Referencing fields from tables

[Pells]

Is it possible to reference a field from another table that you dont have directly linked into your query?

I am using code to select fields from table 1, but also using the NOT EXISTS code to reference another table to have the results of where data doesn's exist in table 2 and I would like to have a couple of the fields in table 2 shown in my queried results.

If this doesn't make sense, please let me know and I will try to explain better (if I can).

Many thanks for any suggestions.

[weekend00]

It looks like doesn't make sense.
You are getting record from table1 without match in table2, but you want some fields from table2. since there is not match, you can get nothing from table2.

[Pells]

It looks like doesn't make sense.
You are getting record from table1 without match in table2, but you want some fields from table2. since there is not match, you can get nothing from table2.

Many thanks weekend00.

Didnt think it made too much sense!

I have a query that looks for the absence of data in a table using NOT EXISTS SQL code that looks to see if a record exists and if not, shows the resulting fields from another table - this is fine and perfect for what I am trying to achieve but I would like to show a couple of the field names that is in a table that isn't directly shown in the query window but is referenced via the code in the query.

Here is an article that details the NOT EXISTS function that I have used:

http://www.devx.com/dbzone/Article/9570/1954

Hope this makes more sense, if not, I could post my code and try to explain again?

[weekend00]

an example with explainations will be much more clear.
such as sample data in you table 1 and 2, and the result that you are expecting.

[Pells]

an example with explainations will be much more clear.
such as sample data in you table 1 and 2, and the result that you are expecting.

Table 1:
Personnel_Nr i.e. 12345, 11111, 22222
Surname i.e. Smith, Jones, Harris

Table 2:
Personnel_Nr i.e. 12345, 11111, 22222
Week_No i.e. 41, 41, 42

My query looks to find where there are no records against a specific week_no for personnel_nr's. If there are no records in table 2 week_nr, then return the personnel_nr from table 1.

So based on the above, my query would return for Week 41:

Personnel_Nr = 22222
Surname = Harris

The query works fine, but I would like to show the week_nr from table 2 in the results by amending my code that consists of NOT EXIST to provide me with the absence of data, so it would look like this:

Personnel_Nr = 22222
Surname = Harris
Week_Nr = 41

Hope this helps.

[weekend00]

If you mean that you will provide specified week number to get the result, why not just put it in the result that you got:

for example, when you specify week no 41, you get 22222, Harris, then you just add 41.

Following query is just for reference:

select [input_week_no],table1.* from table1 left join table2 on table1.personal_NR=table1.personal_nr where table2.personal_nr is null where table2.week_no=[input_week_no]

when you run this query, it asks for input_week_n.

[Pells]

If you mean that you will provide specified week number to get the result, why not just put it in the result that you got:

for example, when you specify week no 41, you get 22222, Harris, then you just add 41.

Following query is just for reference:

select [input_week_no],table1.* from table1 left join table2 on table1.personal_NR=table1.personal_nr where table2.personal_nr is null where table2.week_no=[input_week_no]

when you run this query, it asks for input_week_n.

Many thanks for your suggestion, but no, this isnt what I am looking for. I want to add the field name week_nr to my query results but this field doesn't exist in table 1 but table 2.

Thanks for your help.

[Pells]

Many thanks for all your suggestions, I think I have managed to solve this one.