Creating a Client List from a Project List

[polishfc]

I currently have a dataset of all "Important" clients. I would like to append this list with data for the remaining client to essentially make a full list of all clients and I was wondering how best to tackle this.



A bit of background, each client has a unique client code. The "Important Client" dataset has a Client Code and Client Name column. The data for the remaining clients would come from a YTD sales report. The sales report would include data for all sales, so a single client could show up multiple times if multiple sales were made ( i.e. client 000001 had $100 for project x and $900 for project y).

I would like to set up a query(ies) that would extract/look at the Client Code column in the YTD Sales Report, remove anyClient Code dupes, and cross reference that list to the list of client codes in the "Important Client" dataset. Lastly I would like to append the "Important Client" dataset with any new client codes from the YTD sales report.

I attached a rudimentary example of the "Important Client" dataset and the YTD Sales Report.

Access Example.zip



Thank you for the help in advance.

[davegri]

polishfc-davegri-v01.zip

Attached DB imports 2 tables from Excel into Access.
qYTDClients is a select query with GROUP BY to select unique Client Codes from tblYTDSales
qAppend is an append query to append records in qYTDClients into tblImportantClients

Running qAppend will add 28 records from qYTDClients to tblImportantClients.
(This has already been run, so running it again will add 28 more records)

[polishfc]

polishfc-davegri-v01.zip

Attached DB imports 2 tables from Excel into Access.
qYTDClients is a select query with GROUP BY to select unique Client Codes from tblYTDSales
qAppend is an append query to append records in qYTDClients into tblImportantClients

Running qAppend will add 28 records from qYTDClients to tblImportantClients.
(This has already been run, so running it again will add 28 more records)

Thanks for putting that together, is there a way to modify these queries to only pull in new unique client codes from the YTD sales dataset?

[davegri]

is there a way to modify these queries to only pull in new unique client codes from the YTD sales dataset?

Try this new query. I named it qAppendAdd. It won't duplicate any existing client codes in tblImportantClients.

INSERT INTO tblImportantClients ( [Client Code] )
SELECT qYTDClients.[Client Code]
FROM qYTDClients LEFT JOIN tblImportantClients ON qYTDClients.[Client Code] = tblImportantClients.[Client Code]
WHERE (((tblImportantClients.[Client Code]) Is Null));