R
Rick Vooys
Hello,
I have mutiple tables, each is somwhat similar but different enough I need
to have seperate tables. Basically they are all Costs, each cost is assigned
on a specific date. For each table I created a query to Sum the Costs for
each type of Cost. I then used all those queries in one report. It all worked
until I realized I needed to add the date to the query, as soon as I added
the dates and attempted to add a where condition to each of those queries it
produced crazy records. Here is the query, I can send the file to anybody who
may have an idea?
SELECT tblFieldUnits.UnitID, tblPersonnel.PersonnelFirstName,
tblPersonnel.PersonnelLastName,
qrySumOfServiceTaskCostsByUnitID.SumOfServiceTaskCost,
qrySumOfBodyDamageByUnitID.SumOfBodyDamageCost,
qrySumOfCellPhoneCostsByUnitID.SumOfCellPhoneCost,
qrySumOfTireCostsByUnitID.SumOfTireCost,
qrySumOfFuelCostsByUnitID.SumOfFuelCost,
qrySumOfServiceTaskCostsByUnitID.ServiceDate,
qrySumOfBodyDamageByUnitID.BodyDamageDate,
qrySumOfCellPhoneCostsByUnitID.CellPhoneDate,
qrySumOfFuelCostsByUnitID.FuelDate, qrySumOfTireCostsByUnitID.TireDate
FROM (((((tblFieldUnits INNER JOIN tblPersonnel ON tblFieldUnits.PersonnelID
= tblPersonnel.PersonnelID) INNER JOIN qrySumOfServiceTaskCostsByUnitID ON
tblFieldUnits.UnitID = qrySumOfServiceTaskCostsByUnitID.UnitID) INNER JOIN
qrySumOfBodyDamageByUnitID ON tblFieldUnits.UnitID =
qrySumOfBodyDamageByUnitID.UnitID) INNER JOIN qrySumOfCellPhoneCostsByUnitID
ON tblFieldUnits.UnitID = qrySumOfCellPhoneCostsByUnitID.UnitID) INNER JOIN
qrySumOfFuelCostsByUnitID ON tblFieldUnits.UnitID =
qrySumOfFuelCostsByUnitID.UnitID) INNER JOIN qrySumOfTireCostsByUnitID ON
tblFieldUnits.UnitID = qrySumOfTireCostsByUnitID.UnitID
WHERE (((qrySumOfServiceTaskCostsByUnitID.ServiceDate) Between #4/1/2005#
And #4/10/2005#) AND ((qrySumOfBodyDamageByUnitID.BodyDamageDate) Between
#4/1/2005# And #4/10/2005#) AND
((qrySumOfCellPhoneCostsByUnitID.CellPhoneDate) Between #4/1/2005# And
#4/10/2005#) AND ((qrySumOfFuelCostsByUnitID.FuelDate) Between #4/1/2005# And
#4/10/2005#) AND ((qrySumOfTireCostsByUnitID.TireDate) Between #4/1/2005# And
#4/10/2005#));
I have mutiple tables, each is somwhat similar but different enough I need
to have seperate tables. Basically they are all Costs, each cost is assigned
on a specific date. For each table I created a query to Sum the Costs for
each type of Cost. I then used all those queries in one report. It all worked
until I realized I needed to add the date to the query, as soon as I added
the dates and attempted to add a where condition to each of those queries it
produced crazy records. Here is the query, I can send the file to anybody who
may have an idea?
SELECT tblFieldUnits.UnitID, tblPersonnel.PersonnelFirstName,
tblPersonnel.PersonnelLastName,
qrySumOfServiceTaskCostsByUnitID.SumOfServiceTaskCost,
qrySumOfBodyDamageByUnitID.SumOfBodyDamageCost,
qrySumOfCellPhoneCostsByUnitID.SumOfCellPhoneCost,
qrySumOfTireCostsByUnitID.SumOfTireCost,
qrySumOfFuelCostsByUnitID.SumOfFuelCost,
qrySumOfServiceTaskCostsByUnitID.ServiceDate,
qrySumOfBodyDamageByUnitID.BodyDamageDate,
qrySumOfCellPhoneCostsByUnitID.CellPhoneDate,
qrySumOfFuelCostsByUnitID.FuelDate, qrySumOfTireCostsByUnitID.TireDate
FROM (((((tblFieldUnits INNER JOIN tblPersonnel ON tblFieldUnits.PersonnelID
= tblPersonnel.PersonnelID) INNER JOIN qrySumOfServiceTaskCostsByUnitID ON
tblFieldUnits.UnitID = qrySumOfServiceTaskCostsByUnitID.UnitID) INNER JOIN
qrySumOfBodyDamageByUnitID ON tblFieldUnits.UnitID =
qrySumOfBodyDamageByUnitID.UnitID) INNER JOIN qrySumOfCellPhoneCostsByUnitID
ON tblFieldUnits.UnitID = qrySumOfCellPhoneCostsByUnitID.UnitID) INNER JOIN
qrySumOfFuelCostsByUnitID ON tblFieldUnits.UnitID =
qrySumOfFuelCostsByUnitID.UnitID) INNER JOIN qrySumOfTireCostsByUnitID ON
tblFieldUnits.UnitID = qrySumOfTireCostsByUnitID.UnitID
WHERE (((qrySumOfServiceTaskCostsByUnitID.ServiceDate) Between #4/1/2005#
And #4/10/2005#) AND ((qrySumOfBodyDamageByUnitID.BodyDamageDate) Between
#4/1/2005# And #4/10/2005#) AND
((qrySumOfCellPhoneCostsByUnitID.CellPhoneDate) Between #4/1/2005# And
#4/10/2005#) AND ((qrySumOfFuelCostsByUnitID.FuelDate) Between #4/1/2005# And
#4/10/2005#) AND ((qrySumOfTireCostsByUnitID.TireDate) Between #4/1/2005# And
#4/10/2005#));