[Calfuzz]

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!

[r937]

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