Query returning more results than wanted

[thedanch]

Hi,



So I have a query, drawn from four sub-queries, and it's not working entirely how I want it to, and I'm not sure how to fix it.

If each of the four sub-queries is only a one-line record, it works fine, but when I add a second line to any of the tables that the queries drawn from, it adds to the query almost exponentially.

(ie. the data is coming from a form with four-subforms. We'll call them A, B, C, and D. The query performs a sum. If it's one line, it will go A1 + B1 + C1 + D1, and everything works. If I add another line I get: (A1 + B1 + C1 + D1) + (A2 + B1 + C1 + D1). If I add another line to another subform, it will do the same thing, but with every permutation again.)

I have unique values on, but that doesn't change anything.

Any help would be greatly appreciated.

Thank you.

[Robeen]

How have you built the query that is based on the four other queries?
Could you post a copy of the SQL for all the queries?

[thedanch]

Context:
Working on a WIP Calculation that draws from a timesheet with five subforms:
1) Labor Hours - 'Hours'
2) Equipment Hours - 'Equipment'
3) Materials - 'Materials'
4) Mileage - -KMs'
5) Jobs - 'Job_WIP' - where all the prior WIP is split out based on a percentage to each job for the day.

I'm calculating the $ amounts from the first four in queries, and then trying to combine all those in a 5th query where I assign the percentage.

Query A:
SELECT Hours.Timesheet_id, [First_name] & " " & [Last_Name] AS Name, Hours.Role, Hours.Hours_worked, Roles.Charge_out, [Hours_worked]*[Charge_out] AS HoursWip
FROM Employees INNER JOIN (Roles INNER JOIN Hours ON Roles.Role_id = Hours.Role) ON Employees.Employee_id = Hours.Employee_id;

Query B:
SELECT Equipment_Used.[Timesheet_id], Equipment.Description, TimeBasis.Description, Equipment_Used.Time_Units, Equipment.Chargeout_Rate, [Time_units]*[Chargeout_rate] AS EquipmentWIP
FROM Equipment INNER JOIN (TimeBasis INNER JOIN Equipment_Used ON TimeBasis.TimeBasis_id = Equipment_Used.TimeBasis_id) ON (TimeBasis.TimeBasis_id = Equipment.Time_Basis) AND (Equipment.Equipment_id = Equipment_Used.Equipment_id);



Query C:
SELECT Materials.Timesheet_id, Materials.ExpenseType_id, Materials.[Description / Notes], Materials.Cost, Materials.Markup, Materials.[MaterialsWIP]
FROM Materials;

Query D:
SELECT Vehicle_Used.Timesheet_id, Vehicle_Used.KM_Travelled, Vehicle.KM_Chargeout, [Km_Travelled]*[KM_Chargeout] AS KMWIP
FROM Vehicle INNER JOIN Vehicle_Used ON Vehicle.[Vehicle_id] = Vehicle_Used.[Vehicle_id];

which all work fine on an individual basis. The following query is where things go sideways and I run into the problem above:

SELECT DISTINCT Timesheet.Timesheet_id, Job_Timetable.Job_id, Job_Timetable.Percent_charge, [EquipmentWIP]*[Percent_Charge]/100 AS EquipP, [HoursWip]*[Percent_Charge]/100 AS HoursP, [KMWIP]*[Percent_Charge]/100 AS KMP, [MaterialsWIP]*[Percent_Charge]/100 AS MaterialsP, [EquipP]+[HoursP]+[KMP]+[MaterialsP] AS Job_WIP, Client.Client_Name
FROM (((((Timesheet INNER JOIN [KM WIP] ON Timesheet.[Timesheet_id] = [KM WIP].[Timesheet_id]) INNER JOIN Job_Timetable ON Timesheet.[Timesheet_id] = Job_Timetable.[Timesheet_id]) INNER JOIN HoursWIP ON Timesheet.[Timesheet_id] = HoursWIP.[Timesheet_id]) INNER JOIN MaterialsWIP ON Timesheet.[Timesheet_id] = MaterialsWIP.[Timesheet_id]) INNER JOIN EquipmentWIP ON Timesheet.[Timesheet_id] = EquipmentWIP.[Timesheet_id]) INNER JOIN Client ON Job_Timetable.Client_id = Client.Client_id
ORDER BY Timesheet.Timesheet_id;

[thedanch]

I should add that I also have the same sub-totals in Reports for each of the inputs, but it doesn't look like I can combine those sub-totals and apply the percentage to them.

[thedanch]

Sorry to bump this, but I'm really stumped here if anyone has any ideas.