SQL Problem

[rob_andy]

SELECT rCustomer.*, rMachine.machCode
FROM rCustomer INNER JOIN rMachine ON rCustomer.HospitalID = rMachine.HospitalID
WHERE ((Not (rMachine.machCode) Is Null));

I've got 2 tables customer and machine, a customer CAN (doesn't have to) have many machines. So I want to single out the customers with machines using the above query. The problem is it gives a record for each machine so you get duplication of customers i.e.

Cust1 MachineA etc...
Cust1 MachineB etc...
Cust2 MachineA etc...
Cust3 MachineC etc...

The problem is I'm using the query to print labels out so I only need one label per customer. How do I refine the query to only show me one instance of the customer if they have a machine.

Thanks for any help in advance. R

 

[bat17]

You need to add the DISTINCT key word to your query.
You can do this in the SQL like
SELECT DISTINCT rCustomer.*, rMachine.machCode
FROM rCustomer INNER JOIN rMachine ON rCustomer.HospitalID = rMachine.HospitalID
WHERE ((Not (rMachine.machCode) Is Null));

or in the design grid select the property sheet for the query and set "Unique Values" to Yes.
You will probably need to reduce the visible fields down to just the ones that you want to use for the lables though.

Peter

 

[rob_andy]

Thanks for that, it half works, unfortunately because the machines are not unique I still get some recplication. Any further ideas. Thanks R