Create a table of unique records from almost dupliacte data in Access 2013

Tags: Software
0 Votes

Create a table of unique records from almost dupliacte data in Access 2013

I have inherited a database that is partially relational and partially flat-file. The database manages sales by third parties via an intermediary. The database belongs to the intermediary. There are four tables involved in this query: Suppliers - the clients of the intermediary who have several items for sale. Customers - people who have bought items from the suppliers - THIS is the flat-file; it contains several records for each customer (yeah, I know!) - one for each purchase (customers can buy from one or more clients in the same Order). Orders - containing metadata including a reference to the suppliers and a reference to the customer. A Salesledger table contains the details of each order; one entry per item bought (except where the Customer buys more than one of a particular item).

This query:

SELECT clients_account.username, SAMP_sales.order_id, SAMP_customers.firstname, SAMP_customers.lastname,, SAMP_customers.addr1, SAMP_customers.addr2, SAMP_customers.town_city, SAMP_customers.county_state, SAMP_customers.post_zip,, SAMP_customers.member
FROM ((SAMP_sales INNER JOIN SAMP_customers ON SAMP_sales.order_id = SAMP_customers.order_id) INNER JOIN SAMP_salesledger ON SAMP_sales.order_id = SAMP_salesledger.order_id) INNER JOIN users_account ON SAMP_salesledger.vendor_id =
WHERE (((client_account.username)=[Enter Client:]));

returns a record set containing all of the sales for a Client. There are of course multiple entries for each customer who has bought more than one item from a particular client.

I need to create a list of customers, without duplicates, for a Client.

I'm banging my head against a wall trying to get Access 2013 to do that. It may be (most probably) that I have missed something simple, but I can't see it. Can anyone help?

Member Answers