Links through multiple tables creates duplicates

  • Thread starter Thread starter Robbro
  • Start date Start date
R

Robbro

I'll do my best to explain this database but its getting complicated. I
have 11 tables that I'm using to calculate cost of yarn. I have a crosstab
query that works perfectly to calculate my overhead expenses sort of as
follows:
Lot water power labor maintenance etc...


I need to add one more calculation on the end for a cotton rebate (part of
the rebate must be applied to overhead). So far I try to link Lot from my
query to lot in lotblend table (I just have lot #'s and blendcodes in this
table), then from blendcode in lotblend to blendcode in my blendcode table
which is laid out as follows:

blendcode material % waste type
1A ab 40 5 cot
1A pb 50 1 poly
1A bb 10 5 cot
etc.....

The rebate calculation is for cotton only its rebate rate*%/(1-waste) and I
can get this into a query and calculate the correct number, my only problem
is that when I do this the query goes to showing 3 instances of each lot
number and calculating the rebate amount for each material when I just want
one number for the lot. I have tried using the sum function but then i get
"You tried to execute a query that does not include the specified expression
"Lot" as part of an aggregate function"
 
Hope this is not a double post, think I hit close too soon first time.

SELECT theorpod.Lot, theorpod.Efficiency, theorpod.[Lbs/Rotor-Hr],
plantlbsperyearandframes.lbsperyear, plantlbsperyearandframes.numframes,
plantlbsperyearandframes.numspindles, plantlbsperyearandframes.budgweeks,
ohamts.ohitem, ohamts.ohamt, ohamts.varfixed,
[Efficiency]*[Lbs/Rotor-Hr]*[numspindles]*168 AS lbsperweek,
ohamts.applyratio, [ohamt]/[lbsperyear] AS dolperlb, [lbsperyear]/[budgweeks]
AS budlbsperweek, [lbsperweek]*[numframes]/[budlbsperweek] AS ratio,
[farmbillrebate]![varohreb]*[blendcode]![percent]/(1-[blendcode]![waste]) AS
varohreb, IIf([applyratio]="Y",[dolperlb]/[ratio],[dolperlb]) AS adjdolperlb,
[farmbillrebate]![cotrawreb]*[blendcode]![percent]/(1-[blendcode]![waste]) AS
cotrebate
FROM farmbillrebate RIGHT JOIN (matprices RIGHT JOIN (blendcode RIGHT JOIN
((plantlbsperyearandframes RIGHT JOIN (ohamts RIGHT JOIN theorpod ON
ohamts.plant=theorpod.Plant) ON
plantlbsperyearandframes.plant=theorpod.Plant) LEFT JOIN lotblendcode ON
theorpod.Lot=lotblendcode.Lot) ON blendcode.blendcode=lotblendcode.blendcode)
ON (matprices.plant=blendcode.plant) AND
(matprices.material=blendcode.material)) ON
farmbillrebate.cotpoly=matprices.cotpoly;
 
SELECT theorpod.Lot, theorpod.Efficiency, theorpod.[Lbs/Rotor-Hr],
plantlbsperyearandframes.lbsperyear, plantlbsperyearandframes.numframes,
plantlbsperyearandframes.numspindles, plantlbsperyearandframes.budgweeks,
ohamts.ohitem, ohamts.ohamt, ohamts.varfixed,
[Efficiency]*[Lbs/Rotor-Hr]*[numspindles]*168 AS lbsperweek,
ohamts.applyratio, [ohamt]/[lbsperyear] AS dolperlb, [lbsperyear]/[budgweeks]
AS budlbsperweek, [lbsperweek]*[numframes]/[budlbsperweek] AS ratio,
[farmbillrebate]![varohreb]*[blendcode]![percent]/(1-[blendcode]![waste]) AS
varohreb, IIf([applyratio]="Y",[dolperlb]/[ratio],[dolperlb]) AS adjdolperlb,
[farmbillrebate]![cotrawreb]*[blendcode]![percent]/(1-[blendcode]![waste]) AS
cotrebate
FROM farmbillrebate RIGHT JOIN (matprices RIGHT JOIN (blendcode RIGHT JOIN
((plantlbsperyearandframes RIGHT JOIN (ohamts RIGHT JOIN theorpod ON
ohamts.plant=theorpod.Plant) ON
plantlbsperyearandframes.plant=theorpod.Plant) LEFT JOIN lotblendcode ON
theorpod.Lot=lotblendcode.Lot) ON blendcode.blendcode=lotblendcode.blendcode)
ON (matprices.plant=blendcode.plant) AND
(matprices.material=blendcode.material)) ON
farmbillrebate.cotpoly=matprices.cotpoly;
 
"You tried to execute a query that does not include the specified
expression "Lot" as part of an aggregate function"

What this means is that you have a 'Totals' query but did not GROUP BY the
"Lot" but yours is not a totals query. You must have a query feeding this
one that has the problem.

In a totals query all fields in the output must be either GROUP BY or some
function such as - First, Last, Min, Max, Sum, Count, Avg, etc.
 
Back
Top