Fundamental Question - blanks in queries - show all - hopefully simple solution

[oliviar]

Hello friends!
I'm an Access newbie, after I realised that there were some things I could not bash out of Excel.

I have two sheets I am merging with a query, Sheet A is the master, and Sheet B contains additional things to pad out the information in Sheet A. But Sheet B contains far less information than Sheet A. Yet I need my final query to display all the data of Sheet A - whether or not Sheet B has contributed anything.

I have managed to get my query to work perfectly... but it only contains the values that Sheet B contributed to. Whereas I want to see everything from Sheet A, with blanks from the data missing that Sheet B could not provide.

 

[SydneyGeek]

In the query design you will see a join line between the tables. Right-click that join, and choose the Join Properties.
There are 3 options. Pick the one that shows ALL records from Table 1 and only the existing records from Table 2. It will be option 2 or 3, depending on how the join was drawn between the tables.

Denis

 

[oliviar]

Hmm, I must be completely missing something.
My tables in my query design have no joins. And clicking the spaces between them does nothing.
Though I am using Access 2003 - will that make a difference? (But only until next Friday! Upgrades! Woo! )

edit: Okay I think I found what you are talking about. Except I built my query by writing expressions. Not using the join function. Now when I try to use the join function by matching the key ID to the key ID it says I have ambiguous outer joins.

 

[SydneyGeek]

If you have more than 2 tables / queries in the grid, you will often get the 'ambiguous outer joins' message.
Build the query in 2 steps if that is the case.
1. Have just the 2 tables where you wnat to create the outer join (as I described), and put the relevant fields into the grid.
2. Build a second query, using the first as a source table and adding other table(s) as required. When you want to see the final data, just run the second query.

Denis

 

[oliviar]

Oh wow, thanks so much! It worked.

My final query is going to be pretty darn complicated though. I have to join together 12 tables each with gaps here and there. I have to keep making new queries for each join, building them up like a pyramid?
Can you foresee any terrible problems with this?

 

[SydneyGeek]

Are the fields / columns the same in all the sheets / tables?
If they are not the same, do you have one field common to all, that you are using for the joins?
Before combining 12 tables, I'd want to know more about how they are laid out.

Denis