Query is returning several records

  • Thread starter Thread starter ADencklau
  • Start date Start date
A

ADencklau

I have a query that is pulling in information from a few different tables.

When I run the query, it is showing 3 different results 2 of which have the
exact same information and the third one is duplicating the first two
records. I have one field that is summed and that is the field that is
showing duplicated on the third record.

What am I doing wrong?
 
I have a query that is pulling in information from a few different tables.

When I run the query, it is showing 3 different results 2 of which have the
exact same information and the third one is duplicating the first two
records. I have one field that is summed and that is the field that is
showing duplicated on the third record.

What am I doing wrong?
Click to expand...

Since you didn't post the query (in SQL view) all anyone here can say is "I
don't know". Please do so and perhaps someone will be able to help.
 
I was just hoping to get some suggestions as it is a large query, but here ya
go....

SELECT DISTINCTROW [Mexico-Drivers].SalesTax, [Mexico-Drivers].Royalties,
[Mexico-Drivers].Freight, [Mexico-Drivers].Bottles, [Mexico-Drivers].CommExp,
[Mexico-Drivers].COGs, [Mexico-Drivers].AvgPrice, [Mexico-Drivers].FiscalYr,
[Mexico-Drivers].BegDist, [Mexico-Drivers].LessDist2,
[Mexico-Drivers].NewDist2, [Mexico-Expenses].Advertising2,
[Mexico-Expenses].BankFees2, [Mexico-Expenses].Insurances2,
[Mexico-Expenses].AutoLease2, [Mexico-Expenses].Phones2,
[Mexico-Expenses].Utilities2, [Mexico-Expenses].Depreciation2,
[Mexico-Expenses].IntSvcs2, [Mexico-Expenses].ContLabor2,
[Mexico-Expenses].[Lics&Fees2], [Mexico-Expenses].LicensingFees2,
[Mexico-Expenses].Postage2, [Mexico-Expenses].Rent2,
[Mexico-Expenses].[Repairs&Maint2], [Mexico-Expenses].[R&D2],
[Mexico-Expenses].SoftwareSvcs2, [Mexico-Expenses].Dues2,
[Mexico-Expenses].Supplies2, [Mexico-Expenses].OtherInc2,
[Mexico-Expenses].Taxes2, [Mexico-Expenses].OtherExp2,
[Mexico-Meetings&Events].Revenue2, [Mexico-Meetings&Events].ConfRooms2,
[Mexico-Meetings&Events].HotelRooms2, [Mexico-Meetings&Events].MealsExp2,
[Mexico-Meetings&Events].ProductionCosts2,
[Mexico-Meetings&Events].DistGives2, [Mexico-Meetings&Events].OvertimeExp2,
[Mexico-Meetings&Events].DistEnt2, [Mexico-Meetings&Events].EventSupplies2,
[Mexico-T&E].[#International2], [Mexico-T&E].[#SuperSat2],
[Mexico-T&E].[#Local2], [Mexico-T&E].DISTAirfare2, [Mexico-T&E].DISTHotel2,
[Mexico-T&E].DISTRentalCar2, [Mexico-T&E].DISTMeals2,
[Mexico-T&E].DISTMiscExps2, [Mexico-T&E].[#Countries2],
[Mexico-T&E].NCAirfare2, [Mexico-T&E].NCHotel2, [Mexico-T&E].NCRentalCar2,
[Mexico-T&E].NCMeals2, [Mexico-T&E].NCMiscExps2, [Mexico-T&E].Airfare2,
[Mexico-T&E].Hotel2, [Mexico-T&E].RentalCar2, [Mexico-T&E].Meals2,
[Mexico-T&E].MiscExps2, [Mexico-ProfFees Query Monthly].[Sum Of Fee2]
FROM [Mexico-ProfFees Query Monthly], (([Mexico-Drivers] INNER JOIN
[Mexico-Expenses] ON [Mexico-Drivers].FiscalYr=[Mexico-Expenses].FiscalYr)
INNER JOIN [Mexico-Meetings&Events] ON
[Mexico-Expenses].FiscalYr=[Mexico-Meetings&Events].FiscalYr) INNER JOIN
([Mexico-T&E] INNER JOIN [Mexico-ProfFees] ON
[Mexico-T&E].FiscalYr=[Mexico-ProfFees].FiscalYr) ON
[Mexico-Meetings&Events].FiscalYr=[Mexico-ProfFees].FiscalYr
GROUP BY [Mexico-Drivers].SalesTax, [Mexico-Drivers].Royalties,
[Mexico-Drivers].Freight, [Mexico-Drivers].Bottles, [Mexico-Drivers].CommExp,
[Mexico-Drivers].COGs, [Mexico-Drivers].AvgPrice, [Mexico-Drivers].FiscalYr,
[Mexico-Drivers].BegDist, [Mexico-Drivers].LessDist2,
[Mexico-Drivers].NewDist2, [Mexico-Expenses].Advertising2,
[Mexico-Expenses].BankFees2, [Mexico-Expenses].Insurances2,
[Mexico-Expenses].AutoLease2, [Mexico-Expenses].Phones2,
[Mexico-Expenses].Utilities2, [Mexico-Expenses].Depreciation2,
[Mexico-Expenses].IntSvcs2, [Mexico-Expenses].ContLabor2,
[Mexico-Expenses].[Lics&Fees2], [Mexico-Expenses].LicensingFees2,
[Mexico-Expenses].Postage2, [Mexico-Expenses].Rent2,
[Mexico-Expenses].[Repairs&Maint2], [Mexico-Expenses].[R&D2],
[Mexico-Expenses].SoftwareSvcs2, [Mexico-Expenses].Dues2,
[Mexico-Expenses].Supplies2, [Mexico-Expenses].OtherInc2,
[Mexico-Expenses].Taxes2, [Mexico-Expenses].OtherExp2,
[Mexico-Meetings&Events].Revenue2, [Mexico-Meetings&Events].ConfRooms2,
[Mexico-Meetings&Events].HotelRooms2, [Mexico-Meetings&Events].MealsExp2,
[Mexico-Meetings&Events].ProductionCosts2,
[Mexico-Meetings&Events].DistGives2, [Mexico-Meetings&Events].OvertimeExp2,
[Mexico-Meetings&Events].DistEnt2, [Mexico-Meetings&Events].EventSupplies2,
[Mexico-T&E].[#International2], [Mexico-T&E].[#SuperSat2],
[Mexico-T&E].[#Local2], [Mexico-T&E].DISTAirfare2, [Mexico-T&E].DISTHotel2,
[Mexico-T&E].DISTRentalCar2, [Mexico-T&E].DISTMeals2,
[Mexico-T&E].DISTMiscExps2, [Mexico-T&E].[#Countries2],
[Mexico-T&E].NCAirfare2, [Mexico-T&E].NCHotel2, [Mexico-T&E].NCRentalCar2,
[Mexico-T&E].NCMeals2, [Mexico-T&E].NCMiscExps2, [Mexico-T&E].Airfare2,
[Mexico-T&E].Hotel2, [Mexico-T&E].RentalCar2, [Mexico-T&E].Meals2,
[Mexico-T&E].MiscExps2, [Mexico-ProfFees Query Monthly].[Sum Of Fee2]
HAVING ((([Mexico-Drivers].FiscalYr)=2009));
 
FROM [Mexico-ProfFees Query Monthly], (([Mexico-Drivers] INNER JOIN
[Mexico-Expenses] ON [Mexico-Drivers].FiscalYr=[Mexico-Expenses].FiscalYr)
INNER JOIN [Mexico-Meetings&Events] ON
[Mexico-Expenses].FiscalYr=[Mexico-Meetings&Events].FiscalYr) INNER JOIN
([Mexico-T&E] INNER JOIN [Mexico-ProfFees] ON
[Mexico-T&E].FiscalYr=[Mexico-ProfFees].FiscalYr) ON
[Mexico-Meetings&Events].FiscalYr=[Mexico-ProfFees].FiscalYr
Click to expand...

Since you don't have any JOIN from [Mexico-ProfFees Query Monthly] to any of
the other tables you'll get as many duplicates of all the other fields as
there are records in that query. The INNER JOINS will, as always, give
multiple copies of the "one" side table's fields if there are multiple records
in the "many". You may want to build this query up step by step, adding
tables as you go, and checking the datasheet *before* doing the Totals, so you
can see what data is actually being returned.
 
Back
Top