Link to home
Start Free TrialLog in
Avatar of Andrew
AndrewFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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?
Avatar of Andrew
Andrew
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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
Avatar of Andrew

ASKER

Unfortunately, I am able to post a stripped down version of my DB
Avatar of mbizup
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)
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
Avatar of Andrew

ASKER

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
So ... join on cTrainingRegion = Trgregion ?

SELECT * FROM tbl2 LEFT JOIN tbl1 ON tbl2.cTrainingRegion = tbl1.Trgregion
Avatar of Andrew

ASKER

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
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
Avatar of Andrew

ASKER

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
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
ASKER CERTIFIED SOLUTION
Avatar of Andrew
Andrew
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Andrew

ASKER

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)