Access Query using all records from 2 tables

[scriggs]

I have two tables as lookups. I use a third table to enter information with lookups to each table. I need to create a query which includes non-blank lines.

i.e.

Table 1

Code Description
1000 Sales
2000 Purchases
3000 Costs
etc.

Table 2

Ref Name
SOUTH Souhtern Division
NORTH Northern Division
EAST Eastern Division
etc.

Table 3
Code Ref Amount
1000 SOUTH 1000
2000 SOUTH 200
1000 NORTH 500
etc.

If I create based on Table 3 I get the names of all code/ref combinations with a value, however I want to include blank rows as well. How can I acheive this?

 

[Crowley16]

goto the query builder

right click on the link between the 2 tables, goto join properties...

then choose the option to include ALL records from Tbl3...

--------------------
Procrastinate Now!

 

[scriggs]

Thanks for the quick reply!

Thats what I have at the moment, but it will only include rows that are in table 3. So for example 1000 EAST will not show as there is no data in table 3 for it. I need all the possible combinations shown, including any that dont have data so that I get:

qryResults

Code Ref Amount
1000 EAST 0 <-- these items do not appear in tbl3
1000 NORTH 0 <-- these items do not appear in tbl3
1000 SOUTH 1000
2000 EAST 0 <-- these items do not appear in tbl3
2000 NORTH 0 <-- these items do not appear in tbl3
2000 SOUTH 200
etc.

 

[Crowley16]

hmm...

try joining tbl3 to tbl1, include all from tbl1, then join tbl2 to tbl3, include all from tbl 3

--------------------
Procrastinate Now!

 

[scriggs]

Thats the best method I could currently come up with but is a bit messy. I was hoping there might be a better method out there - it seems such a simple idea! Plus I am trying to leave the end query updatable which the multiple joins does not allow.

 

[lespaul]

are you trying to write a query or insert this information into another table?

Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell

 

[PHV]

And this ?
SELECT A.Code, A.Ref, NZ(B.Amount, 0) AS Amount
FROM (SELECT Code, Ref FROM [Table 1], [Table 2]) AS A
LEFT JOIN [Table 3] AS B ON A.Code = B.Code AND A.Ref = B.Ref

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[scriggs]

lespaul,

i am trying to write a query. i want to bring all the data together and display on a continuous form. And preferably allow the user to update the data in table3 from this query.

 

[PHV]

And preferably allow the user to update the data in table3
One way is to initialyze table3 with a cartesian product of table1 and table2:
INSERT INTO table3(Code,Ref,Amount)
SELECT Code,Ref,0 FROM table1,table2;

I assumed that (Code,Ref) is a unique composite index for table3.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[scriggs]

PHV

Thanks for the idea. Am I right in thinking this will create a new copy of the data, rather than just update the existing data.