How to Join an Aggregated Query to a Distinct Query (1 Viewer)

[raziel3]

I am using this Source query

I know how to get this unique list query

How do I join the last SUPID to my unique query? Like this

Edit: Preferably as a single query as I want it to feed a combobox.

 

[ebs17]

How do I join the last SUPID to my unique query?

In query processing, the table/query of data origin is basically considered as an unordered set, SQL performs bulk data processing (all at once). Therefore, you need an additional field in your table/query that explicitly defines the correct order, i.e. a chronological car number or a timestamp.

If you have something like this, you can determine the maximum car number or the maximum timestamp and the associated SUPID.

 

[CJ_London]

Do you mean last? or largest? If the former then you need the additional field to define the order as suggested by ebs17. If the latter then include the max of supID in your aggregation query

 

[plog]

No need to JOIN those queries together. Use the below SQL for Query1:

SELECT VENDISP, MAX(SUPID) AS LASTOFSUPID
FROM [Source Query]
GROUP BY VENDISP

 

[raziel3]

Therefore, you need an additional field in your table/query that explicitly defines the correct order, i.e. a chronological car number or a timestamp.

I have the auto number field in the original table [PURID]

@CJ_London Last. I want the Last SUPID for the VEND, so technically if sorted by PURID, Last would also be Largest. Right??

 

[CJ_London]

@CJ_London Last. I want the Last SUPID for the VEND, so technically if sorted by PURID, Last would also be Largest. Right??

If you sorted by SUPID and nothing else yes, no idea where PURID comes from so I would say wrong.

And if not sorted then probably not. Better to use max and not give the sql engine more work to do sorting

plog has given you the solution in post #4

 

[ebs17]

Faster, non-upgradeable:

SELECT
   T.*
FROM
   (
      SELECT
         VENDISP,
         MAX(PURID) AS MaxPURID
      FROM
         TableX
      GROUP BY
         VENDISP
   ) AS Q
      INNER JOIN TableX AS T
      ON Q.VENDISP = T.VENDISP
         AND
      Q.MaxPURID = T.PURID

Updatable:

SELECT
   T.*
FROM
   TableX AS T
WHERE
   T.PURID IN
      (
         SELECT TOP 1
            X.PURID
         FROM
            TableX AS X
         WHERE
            X.VENDISP = T.VENDISP
         ORDER BY
            X.PURID DESC
      )

 

[raziel3]

Good Morning Everyone.
@ebs17 The query produced duplicates

Max may not work. Let me explain the problem in more detail.

I am trying to build a Vendor Listing. My Vendors are separated in to really 2 basic categories:-
"Regular" - Vendors we consistently take goods from and have an account with
"One-Off" - Ad-hoc vendors.

The Main Vendor table (fields SUPID, SUPPLIER) assigns SUPIDs to the Regular Vendors.
SUPID 156- DIRECT PAYMENTS, 141 - GENERAL EXPENSES, 157 - ASSETS are reserved to track the One-Off Vendors.

My Purchases table looks like this

VENDISP is a logic IIF(PAYEE="",VENDORS.SUPPLIER,PAYEE).

Now the problem.
Sometimes One-Off Vendors become Regular Vendors. So on changing their category it may be possible for TERMINX LIMITED to get assigned SUPID 20 so the Max will return 141.

So to summarize,
The Vendor listing I want to create will be based on DISTINCT qryPURCHASES field VENDISP
and the most recent SUPID.

Any help would be appreciated.

Brainstorming:
-Do I need to include a Vendor Since Date field in my Vendors Table?
-Should I move these 156- DIRECT PAYMENTS, 141 - GENERAL EXPENSES, 157 - ASSETS to a separate table and assign different SUPID reserve 1-999 on that table and on my main Vendors Table Start the IDs from 1000. This would mean changing the IDs of about 3000 records in my purchases table.

 

[ebs17]

Max may not work.

MAX works reliably, and both of my example statements work reliably.
If your description is incomplete and your implementation is slightly different, different results may of course occur.

Brainstorming

If you tell some stories about some numbers here, that might be nice, but I have no idea. Contrary to what many people believe, you can't look over their shoulder at the screen. If you cannot unconditionally believe the statements made, you can stop all considerations because they are worthless.

Should I move these ... to a separate table

I create queries after the database schema is finalized, preferably based on the tables and uncalculated fields.

If you have to redevelop your database schema, that's a different job.

 

[raziel3]

I create queries after the database schema is finalized, preferably based on the tables and uncalculated fields.

If you have to redevelop your database schema, that's a different job.

I was just wondering how to go about extracting the Vendor list with their IDs from the history of purchases. I'm sure there are others who have had this problem before so I was fishing for how they dealt with it.

 

[ebs17]

There should be a separate table for vendors anyway. Then there is nothing to extract from lists.
This is what you would see if the relationship picture was shown.
It is a basic principle that attribute information can only be found once in a database. This information is referenced from other locations using a key.