Alright, so I'm trying to run a query that to display a null value in a field if a duplicate ID exist. There are 2 tables, parent-child relationship. The parent table can have one to many child records. In my scenario, we have fuel tanks that can get many inspections done on them. Tank is the parent table and Tank_Inspections is the child table. There's a capacity data field that I'm getting from the tank table and joining it with the tank inspection record and it shows up twice if multiple inspections exist for that tank. This is fine, however I don't want to double count the capacity and only want to show it once. I've pasted the link to an image of a screenshot of how it should be displayed if multiple records exist for parent table. The highlighted cell should be blank. As you can see, the TankID = 65 has two inspections of different types, since I'm getting the capacity field from the Tank table, it's getting inserted twice. I was to write a query so if two or more inspections exist for tanks, only show the capacity once and "blank" out the other capacity data element. In this case, the highlight cell should be blank. Suggestions?
This sounds like a job for an analytic function. Since Access doesn't natively support these, there is a hack to accomplish the row_number() analytic function that sounds like it would meet your needs:
Achieving ROW_NUMBER / PARTITION BY in MS Access
You create a query that invokes this self-join and use that instead of the table. Once you have the row number on each row, it would look something like this:
Tank ID Inspection ID Row
59 6841 1
60 6842 1
65 7344 1
65 6843 2
And your capacity formula would change from [Inspection].[Capacity] to something like this:
IIf([Self Join].[Row] = 1, [Inspection].[Capacity], Null)