Hi,

I'm trying to write a sql query to create a report that will act kind of like a Mail Merge in Microsoft Word. I want to send letters to our customers to remind them their machines need to be ballbar'd or calibrated.

My database is set up as follows:

CustomerInformation(CustomerID[pk], CompanyName, CompanyPhone, etc etc etc)

CustomerMachine(MachineID[pk], MachineManufacturer, MachineModel, MachineSerialNumber, MachineSize, CustomerID[fk])

LaserCalibrations(LaserID[pk], LaserDate, MachineID[fk])

Ballbars(BallbarID[pk], BallbarDate, MachineID[fk])

My query looks like this:

SELECT CustomerInformation.CustomerID, CustomerMachine.CustomerID, CustomerMachine.MachineID, CustomerMachine.MachineManufacturer, CustomerMachine.MachineModel, CustomerMachine.MachineSerialNumber, CustomerMachine.MachineSize, LaserCalibrations.LaserID, LaserCalibrations.LaserDate, LaserCalibrations.MachineID

FROM CustomerInformation, CustomerMachine, LaserCalibrations

WHERE (((CustomerInformation.CustomerID)=CustomerMachine .CustomerID) And ((CustomerMachine.MachineID)=LaserCalibrations.Mac hineID))
ORDER BY CustomerInformation.CompanyName, LaserCalibrations.LaserDate;


The problem is that it will not print the laser calibration dates in order. I wanted to include a history of past service on the letter. I tried to write the query including ballbars, but it got too messy, I think I was missing a "Distinct" descripter. I also want to change the price according to the size of the machine... and I'm not certain how to go about doing that.


Please help

Thanks!

you have here two unrelated one-to-many relationships that cannot easily be combined in one query

if a single machine has 3 LaserCalibrations and 4 Ballbars, the query will return 12 rows for that machine

you need two separate queries, one for LaserCalibrations and a separate one for Ballbars