Access 2016 Relationships

Question

I have a Access 2016 database which I am trying to design. I have a table with a roster of people tblRoster, a 1-1 relationship to another table with Basic Individual Data about the the person and 1-1 relationship from this basic data table to another table as lookup. I have a working query but when I add a column from the lookup table no data is shown. Please help figure out why.

Please find below the the SQL Statement for the query:

SELECT tblRoster.EDIPI, [tblRoster].[LastName] & ", " & [tblRoster].[FirstName] & " " & Left$([tblRoster].[MiddleName],1) AS [Full Name], tblBasicIndividualRecords.PriMilOccSpec AS MOS, DLookUp("LongName","tblMilitaryOccupationalSpecialties","tblMilitaryOccupationalSpecialties!ID = " & [tblBasicIndividualRecords].[PriMilOccSpec]) AS [MOS Decription], DLookUp("Abbreviation","tblRankStructure","tblRankStructure!ID = " & [tblRoster].[Rank]) AS Rank, Format([EndOfActiveService],"yymmdd") AS EAS, tblBasicIndividualRecords.Billet, tblBasicIndividualRecords.OnHand, tblBasicIndividualRecords.Remarks, tblBasicIndividualRecords.Deployable, tblRankStructure.SortValue
FROM (tblRankStructure INNER JOIN tblRoster ON tblRankStructure.ID = tblRoster.Rank) INNER JOIN tblBasicIndividualRecords ON tblRoster.EDIPI = tblBasicIndividualRecords.EDIPI
ORDER BY tblRankStructure.SortValue DESC;

When I add the lookup table to the query no data populates:

SELECT tblRoster.EDIPI, [tblRoster].[LastName] & ", " & [tblRoster].[FirstName] & " " & Left$([tblRoster].[MiddleName],1) AS [Full Name], tblBasicIndividualRecords.PriMilOccSpec AS MOS, DLookUp("LongName","tblMilitaryOccupationalSpecialties","tblMilitaryOccupationalSpecialties!ID = " & [tblBasicIndividualRecords].[PriMilOccSpec]) AS [MOS Decription], DLookUp("Abbreviation","tblRankStructure","tblRankStructure!ID = " & [tblRoster].[Rank]) AS Rank, Format([EndOfActiveService],"yymmdd") AS EAS, tblBasicIndividualRecords.Billet, tblBasicIndividualRecords.OnHand, tblBasicIndividualRecords.Remarks, tblBasicIndividualRecords.Deployable, tblRankStructure.SortValue, tblMilitaryOccupationalSpecialties.SortOrder
FROM tblMilitaryOccupationalSpecialties INNER JOIN ((tblRankStructure INNER JOIN tblRoster ON tblRankStructure.ID = tblRoster.Rank) INNER JOIN tblBasicIndividualRecords ON tblRoster.EDIPI = tblBasicIndividualRecords.EDIPI) ON (tblMilitaryOccupationalSpecialties.ID = tblBasicIndividualRecords.AddMilOccSpec.Value) AND (tblMilitaryOccupationalSpecialties.ID = tblBasicIndividualRecords.PriMilOccSpec)
ORDER BY tblRankStructure.SortValue DESC;

Post the query you are using. And do you really mean "when I add a COLUMN..." or do you mean "when I add a ROW..." — Wayne G. Dunn, Jun 20 '17 at 16:56
I added the SQL Statement for the query prior to adding the lookup table to the query. I also added the SQL statement after adding the lookup table to the query. I did not mean a row; I meant adding a column to the query in the Query Design. — TroyPilewski, Jun 20 '17 at 17:16
You are adding a NEW table to your SQL and joining the 'ID' field in that table with field 'tblBasicIndividualRecords.PriMilOccSpec' and with field 'tblBasicIndividualRecords.AddMilOccSpec.Value'. So, your problem appears to be one (or both) of those. I suggest you test by creating a simple query with ONLY two tables and one of the two new joins and test. If it works, try the other join. If it fails, correct, retest, then repeat for the second new join. — Wayne G. Dunn, Jun 20 '17 at 17:28
Thank you! I didn't think to start as a simple query. If you could write this in the form of an answer I will accept your answer. — TroyPilewski, Jun 20 '17 at 19:06

score 0 · Accepted Answer · answered Jun 20 '17 at 20:21

Whenever you create or change a query and it does not work as expected, there are several steps you can take to analyze the issue and find a solution. The two most frequently encountered issues are either 'selection criteria' or 'table joins'. The more complex the query, the more difficult it will be to spot the issue.

Join Issues: IF you encounter an issue when you add a new table or join on an additional field, simplify the query by keeping only the new table and the table the join is on, then test. If necessary, insure your tables actually contain matching data! If you had also added selection criteria, then remove it.

Selection Criteria: If your change/add results in no or incorrect results, simplify the query by getting rid of all other selections, then test to find cause. Remove other tables if necessary.

Complex Queries: Sometimes you may have 20 or more joined tables with 20 or more selection criteria. Since it would be too time consuming to test each possible scenario, I like to delete half of the selection criteria, then test. If it still doesn't work, delete half of the remaining criteria and repeat the test until it works. If you delete half and it now works, you know the issue is with the item(s) you deleted. The same testing method applies to the joined tables. First delete any tables that have no dependent tables, and test that. Keep deleting tables / criteria until it works.

Some other debugging tips:

Managing and debugging SQL queries in MS Access

http://www.dataversity.net/debugging-complex-sql-queries/

Access 2016 Relationships

1 Answers1