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.