Andrew
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?
ASKER
Unfortunately, I am able to post a stripped down version of my DB
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)
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
SELECT * FROM tbl2 LEFT JOIN tbl1 ON tbl2.somecolumn = tbl1.somecolumn
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
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
SELECT * FROM tbl2 LEFT JOIN tbl1 ON tbl2.cTrainingRegion = tbl1.Trgregion
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
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
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
ASKER
Here is my SQL, most of the fields you don't need to worry about
I am still missing several values of TrgRegion.GMC_TR
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;
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
Have a look at this attached example dummy db.
Q-27853975.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
ASKER