Access query - show all records

I have a table (table 1) of records and I can show all of the records that are populated against one of 21 training regions but I also want to show all training regions even where they do not have records in table 1.  Can someone help please?
LVL 7
andymacfAsked:
Who is Participating?
 
andymacfAuthor Commented:
I have been able to get this working by going back to the start and re-thinking the route through my queries (of which there are many)
0
 
andymacfAuthor Commented:
So far I have tried editing the join link between tbl1 and tbl2 (this is a table containing all the regions) but this is where i get stuck
0
 
andymacfAuthor Commented:
Unfortunately, I am able to post a stripped down version of my DB
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
mbizupCommented:
Use a LEFT JOIN:


SELECT *
FROM  Table1 LEFT JOIN Table2
ON Table1.LinkingField = Table2.LinkingField


(Adjust the table and field names as needed...  LEFT JOIN includes all records from the table on the left hand side of the JOIN clause)
0
 
lwadwellCommented:
Can you show the actual SQL?  Sounds like you need a LEFT JOIN like ...

SELECT * FROM tbl2 LEFT JOIN tbl1 ON tbl2.somecolumn = tbl1.somecolumn
0
 
andymacfAuthor Commented:
This is what i have tried without success.

To explain a bit more, my tbl1 contains many fields, one of which is training regions.  Values are populated in this table from exam events.

Tbl2 contains a unique ID, cTrainingRegion (this is the link to the tbl1 equivalent field), Trgregion (this is a different name to cTrainingRegion) and is how i want to display it in the eventual query output.

I want to be able to show all 22 fields from Tbl2 no matter how many are populated in Tbl1
0
 
lwadwellCommented:
So ... join on cTrainingRegion = Trgregion ?

SELECT * FROM tbl2 LEFT JOIN tbl1 ON tbl2.cTrainingRegion = tbl1.Trgregion
0
 
andymacfAuthor Commented:
Still no luck,  i want to be able to pick a particular exam from tbl1 and where there are no records against training region then it still displays a row for the missing regions but with null values in the data cells.

i hope this makes sense
0
 
lwadwellCommented:
If you only want a particular exam ... let's call the column 'exam', then add it to the query in the join criteria; e.g.

SELECT *
FROM tbl2
LEFT JOIN tbl1 ON tbl2.cTrainingRegion = tbl1.Trgregion AND tbl1.exam = 'somevalue'


Note: It could be in a WHERE clause but you need to cater for the missing records ... e.g.

SELECT *
FROM tbl2
LEFT JOIN tbl1 ON tbl2.cTrainingRegion = tbl1.Trgregion
WHERE tbl1.exam = 'somevalue' OR tbl1.exam IS NULL
0
 
andymacfAuthor Commented:
Here is my SQL, most of the fields you don't need to worry about
 
SELECT TrgRegion.TRID, TrgRegion.GMC_TR, [GMC ASR - Combined 2-1].Specialty, [GMC ASR - Combined 2-1].Exam, [GMC ASR - Combined 2-1].[Exam Date], [GMC ASR - Combined 2-1].Deanery, [GMC ASR - Combined 2-1].Male, [GMC ASR - Combined 2-1].Female, [GMC ASR - Combined 2-1].Asian, [GMC ASR - Combined 2-1].Black, [GMC ASR - Combined 2-1].[Chinese/SE Asian], [GMC ASR - Combined 2-1].White, [GMC ASR - Combined 2-1].Other, [GMC ASR - Combined 2-1].[Ethnicity Unknown], [GMC ASR - Combined 2-1].UK, [GMC ASR - Combined 2-1].EEA, [GMC ASR - Combined 2-1].[Rest of World], [GMC ASR - Combined 2-1].[Graduate Unknown]
FROM TrgRegion LEFT JOIN [GMC ASR - Combined 2-1] ON TrgRegion.cTrainingRegion = [GMC ASR - Combined 2-1].Deanery
WHERE ((([GMC ASR - Combined 2-1].Exam)=521 Or ([GMC ASR - Combined 2-1].Exam) Is Null))
ORDER BY TrgRegion.TRID;

Open in new window


I am still missing several values of TrgRegion.GMC_TR
0
 
lwadwellCommented:
I couldn't get it to work using a WHERE clause either (there is something about MS Access and it's difference in its SQL implementation from SQL Server, Oracle, MySQL, ... etc that just annoys me).
Have a look at this attached example dummy db.
Q-27853975.accdb
0
 
andymacfAuthor Commented:
I have been able to get this working by going back to the start and re-thinking the route through my queries (of which there are many)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.