MS Access: First () function in query not retrieving correct values

Question

I have 4-6 tables, but the main ones are LRTTable and TempTable. They are joined using INNER JOIN and result set is LEFT JOIN with other 6 tables.

LRTable and Temp Table contains Store ID ,PCC Id ,COF NO, Line of Business Name, Status, Address and few other columns. LRTable can have duplicate records for same Store ID with different COF No(0,7987987) and Line of Business Name(Food and Retail).

Temp Table has Store ID, PCC. Combination of these two are primary key in Access.

Query is correctly able to pull all the data from LRTable based on TempTable. For example, query pulls all the records (including duplicates)from LRtable when there is match in Temp Table and are sorted based on status code and line of business. By doing so I am ensuring the active and food records are available on the top.

What I need to achieve is, instead of having multiple records for each store in the query result set, I want only top record from set of records of each store from the result set. I am able to this with the first function, however its picking values fron LRTable rather than sub query result

Is there a way I can just pick from sub query ?

LRTable:

  Store ID   PCC ID    Status    Line of Business Name COF No 
 ---------- -------- ---------- ----------------------- 
         1      123   Active     Food                   999
         1      123   InActive   Food                   89899
         1      123   Active     Retail                 0
         2      222   Active     Retail                 0
         2      222   InActive   food                   76767

TempTable

Store ID : 1 and 2 
PCC : 123 and 222

Result: 


 - 1,0
 - 2,0

Expected output:

 - 1,999
 - 2,76767

Below is the query with first

SELECT StoreID,FIRST (COF No)  from
 ( 
  select * from 
 (
   SELECT distinct TempTable.[Avendra ID],
    TempTable.[Marsha Code],
    LRTable.[Concept Name],
    LRTable.[Outlet (Store) Name],
    LRTable.[Outlet Street Address Text ID],
    LRTable.[Outlet City ID],
    LRTable.[Outlet State ID],
    LRTable.[Outlet Zip Code ID],
    LRTable.[Status Code ID],
    LRTable.[Outlet SAP ID ID],
    LRTable.[Outlet PCC ID ID],
    LRTable.[COF Number ID],
    CLPSource.[Brand Mandated],
FROM (
    (
        (
            (
                (
                    (
                        LRTable INNER JOIN TempTable 
                            ON LRTable.[Outlet PCC ID ID] = TempTable.[PCC ID]
                            AND LRTable.[Store Number ID] = TempTable.[Store Number ID]
                        ) LEFT JOIN CLPSource 
                            ON TempTable.[Avendra ID] = CLPSource.[Avendra Customer ID]
                    ) LEFT JOIN OasisReportSource 
                            ON TempTable.[Marsha Code] = OasisReportSource.[Marsha])

                ORDER BY LRTable.[Status Code ID],
                    LRTable.[Line of Business Name]))

Group by LRTable.[StoreID]

I don't think your expected results make sense based on your dataset. Your example data has no duplicates in it. You may need to clarify what you're trying to do here. — Jiggles32
@Jiggles32, Well Its not actually duplicates, but a store may or may not have 2- 3 records in LRTable with different PCC and COF Numbers, which I have not mentioned in the example data set. I will edit it now. At the end, I would want to see only one record per store instead of 2-3 records — Shwe
What is the criteria you use to choose which record from each store, or does that not matter? — Jiggles32
@Jiggles32 I am joining the tables based on Store ID and PCC ID because that uniquely pulls store record from LR Table. The result set is as expected just that I want one record per store instead of 4 or 5. — Shwe
@Jiggles32 And this resultset is then displayed on the Access Form — Shwe

John F John F · Accepted Answer · 2018-06-21T14:07:41

Using the data, actual output and desired output you provide in your LRTemp table and Temp Table panels, I reckon can be done using the FIRST aggregate function i.e.

SELECT StoreID, FIRST(PCC ID), FIRST(Status), FIRST(Line of Business Name), FIRST (COF No)
FROM (joined LRTable and TempTable)
GROUP BY StoreId

You should then get the data of first-occurring record for each StoreId.

MS Access: First () function in query not retrieving correct values

1 Answers