Best Practice - Design Question on Aggregate Query

[rubbernilly]

Hello again, all... I've been away from this forum for some time, but I am back with a question of my own. I don't know if I am out of practice, but I am just not seeing the best way to set this query up... function, sub-query, or inline computation. Here's the situation:

[blue][b]Table:[/b] WarrantyInformation[/blue]
[b]Relevant Fields:[/b]
ClaimID (autonumber)
PartNumber
ModelCode
ProductionDate

[blue][b]Table:[/b] Vehicles[/blue]
[i](Slave to WarrantyInformation table on VehicleCode=ModelCode)[/i]
[b]Relevant Fields:[/b] VehicleCode

[blue][b]Table:[/b] VehicleVolumes[/blue]
[i](Slave to Vehicles table on VehicleCode field)[/i]
[b]Relevant Fields:[/b]
VehicleCode
ProdYear
ProdMonth
Volume

What I want to create is an incidents per 1000 vehicles (IPTV) query from the WarrantyInformation table, grouped on the PartNumber. Each PartNumber will have an entry that lists the total Claims and then the IPTV rate.

Currently, I sum the VehicleVolumes per VehicleCode (in a separate query called "VehicleVolumesSum") and then pull that value in by reference to the ModelCode from the WarrantyInformation table.

Here is the query that I have. It works, but has no date limitations:

SELECT WarrantyInformation.ProductID, Month([ProductionDate]) & "/" & Year([ProductionDate]) AS ProdMonth, WarrantyInformation.ModelCode, VehicleVolumesSum.Volume, Count(WarrantyInformation.ClaimID) AS Claims, [b]([Claims])/([Volume]/1000) AS IPTV[/b]
FROM WarrantyInformation INNER JOIN VehicleVolumesSum ON WarrantyInformation.ModelCode = VehicleVolumesSum.VehicleCode
GROUP BY WarrantyInformation.ProductID, Month([ProductionDate]) & "/" & Year([ProductionDate]), WarrantyInformation.ModelCode, VehicleVolumesSum.Volume;

So now I realize that if I want to limit the dates of the Claims made in the WarrantyInformation table, I will still be pulling in *all* of the Volume information for the VehicleCode, since that is not also limited.

To overcome this limitation, should I...
--use a VB function that expects arguments that would be fed by the same query parameters that would limit the dates
--use a subquery to get the right volume value I'm looking for
--use the same parameters on the VehicleVolumesSum query to equally limit the number of production months it totals for the Volume value
--generate this value by calculation in the query (is this even possible?)

 

[SimonSellick]

The third option sounds easiest, assuming that what you want to see is actually "incidents per thousand vehicles produced in the same month as the vehicle for which the warranty claim was made" (!) You will also have to convert WarrantyInformation.ProductionDate to a production year and month to set the parameters on your VehicleVolumesSum query (or the other way around if the driver is the production data).

When I mentioned "easiest", that was a relative term...just that it can be done in SQL without resorting to VB functions etc.

Interesting problem - post your decision and reasoning if you have time.

 

[rubbernilly]

I did end up going with the third option since it required the least amount of work for me.

I added criteria of:

Between [Start Month] And [End Month]
Between [Start Year] And [End Year]

to both the VehicleVolumeSum Query and the IPTV query. I did have to break out the ProductionDate field into month and year to make the proper comparisons.

...but now that I am reporting this, I am realizing a problem. If I provide a month range of 5-8 and a year range of 2005-2006, I will get May through August of 2005 and May through August of 2006. That's not what I was looking for.

I think I'm going to have to go the other direction and turn the ProdYear and ProdMonth of the VehicleVolumes table into a date in the VehicleVolumeSum query. Then I can use the criteria:

Between [Start Date] And [End Date] in that query and the IPTV query. That will handle the information correctly *and* let me span year-end/year-beginning.

Thanks for the help, Simon. It's always good to have someone to bounce ideas off of!