Hi All
I hope the following makes sense enough that you can help:
I have a table with the following fields:
*ID
PartCode
DateSet
Tooling Cost
Packaging Cost
Other Cost
Transport
Repaint Cost
LocalManCost
Margin
Rebate
The list looks like
ID PartCode DateSet Tooling Cost Packaging Cost Other Cost Transport Repaint Cost LocalManCost Margin Rebate
15 700-037 1/11/2021 $0.00 $0.00 $0.00 22.00% $0.00 30.00% 5.00%
16 700-050 1/11/2021 $0.00 $0.00 $0.00 22.00% $0.00 48.00% 5.00%
306 700-050 25/10/2021 $0.00 $0.00 $0.00 22.00% $0.00 30.00% 5.00%
17 700-052 25/10/2021 $0.00 $0.00 $0.00 22.00% $0.00 30.00% 5.00%
300 700-052 1/11/2021 $0.00 $0.00 $0.00 22.00% $0.00 72.00% 5.00%
18 700-053 25/10/2021 $0.00 $0.00 $0.00 22.00% $0.00 30.00% 5.00%
Data entry for each Part Code + DateSet and relevant costing.
I need to filter out the latest entries for ALL PartCodes which may not be the same date.
I tried the following:
SELECT tblPricingExtraCost.PartCode, Max(tblPricingExtraCost.DateSet) AS MaxOfDateSet, tblPricingExtraCost.[Tooling Cost], tblPricingExtraCost.[Packaging Cost], tblPricingExtraCost.[Other Cost], tblPricingExtraCost.Transport, tblPricingExtraCost.[Repaint Cost], tblPricingExtraCost.LocalManCost, tblPricingExtraCost.Margin, tblPricingExtraCost.Rebate INTO tblPricingExtraCostLatest
FROM tblPricingExtraCost
GROUP BY tblPricingExtraCost.PartCode, tblPricingExtraCost.[Tooling Cost], tblPricingExtraCost.[Packaging Cost], tblPricingExtraCost.[Other Cost], tblPricingExtraCost.Transport, tblPricingExtraCost.[Repaint Cost], tblPricingExtraCost.LocalManCost, tblPricingExtraCost.Margin, tblPricingExtraCost.Rebate;
But that resulted in a full list of all partcodes with all dates, i.e. double ups.
Then I tried:
SELECT tblPricingExtraCost.PartCode,Max(tblPricingExtraCo st.DateSet) AS MaxOfDateSet
FROM tblPricingExtraCost
GROUP BY tblPricingExtraCost.PartCode
tblPricingExtraCost.[Tooling Cost], tblPricingExtraCost.[Packaging Cost], tblPricingExtraCost.[Other Cost], tblPricingExtraCost.Transport, tblPricingExtraCost.[Repaint Cost], tblPricingExtraCost.LocalManCost, tblPricingExtraCost.Margin, tblPricingExtraCost.Rebate INTO tblPricingExtraCostLatest
FROM tblPricingExtraCost
GROUP BY tblPricingExtraCost.PartCode, tblPricingExtraCost.[Tooling Cost], tblPricingExtraCost.[Packaging Cost], tblPricingExtraCost.[Other Cost], tblPricingExtraCost.Transport, tblPricingExtraCost.[Repaint Cost], tblPricingExtraCost.LocalManCost, tblPricingExtraCost.Margin, tblPricingExtraCost.Rebate;
But now I am getting
“Syntax error (missing operator) in query expression “tblPricingExtraCost.PartCode tblPricingExtraCost.[ToolingCost’.
Once I have filtered the records I have to calculate the actual prices so I thought to create a table (not yet done) with the results to use for the price calculation.
I am looking at it and my eyes are bleeding. I am sure it is something simple I am not seeing.
Katbaroo - because the Yak yaks -