Intense Query Question

[DomZ]

Sup,
I need your help ladies and gentlemen.
I need to do the following:
I have the following Append Query:

INSERT INTO TblTmp2 ( CustomerID, Line, PartNumber, Weight, WONumber, DrumsBoxes, CageNumber, Quantity, Fini, Épaisseur, PONumber, ReqDate, DateIn )
SELECT TblPartsTracking.CustomerID, TblParts.Line, TblPartsTracking.PartNumber, TblPartsTracking.Weight, TblPartsTracking.WONumber, TblPartsTracking.DrumsBoxes, TblPartsTracking.CageNumber, TblPartsTracking.Quantity, TblParts.Fini, TblParts.Épaisseur, TblPartsTracking.PONumber, TblPartsTracking.ReqDate, TblPartsTracking.DateIn
FROM TblParts INNER JOIN TblPartsTracking ON TblParts.PartNumber=TblPartsTracking.PartNumber
WHERE (((TblParts.Line)=Forms!FrmPartsTracking!cboLine) And ((TblPartsTracking.DateIn) Between Forms!FrmPartsTracking!txtFmDate And Forms!FrmPartsTracking!txtToDate) And ((TblPartsTracking.Out)=False))
ORDER BY TblPartsTracking.DateIn;


the INSERT INTO formula i wrote results in inserting into TblTmp2 every single piece, from line X, within 2 dates.

What I want to do is:
The same thing, however, I don't want see every single pieces who have the same WONumber but only 1 line, where all those pieces with the same WONumber would be added...

So lets say :

(FROM)
CustomerID      Boxes      Quantity      Weight      WONumber      PONumber        PartNumber
Hello           1          10            100         030108-1      73726           P09192
Hello           2          15            150         030108-1      73726           F54555
Hello           3          20            200         030108-1      73726           W1222
Hello2          1          10            110         030108-2      45122           LMNOP
Hello3          1          14            140         030108-3      44122           212312

(TO)
CustomerID     Boxes        Quantity     Weight      WONumber     PONumber         PartNumber
Hello           6           45           450         030108-1     73726            DIFF ITEMS
Hello2          1           10           110         030108-2     45122            LMNOP
Hello3          1           14           140         030108-3     44122            212312

Then Of Course you would have the rest, like finish, base metal who will stay the same. (like PONumber/WONumber)

I came up to this:


INSERT INTO TblTmp2 ( CustomerID, Line, PartNumber, Weight, WONumber, DrumsBoxes, CageNumber, Quantity, Fini, Épaisseur, PONumber, ReqDate, DateIn )
SELECT First(TblPartsTracking.CustomerID) AS FirstOfCustomerID, First(TblParts.Line) AS FirstOfLine, First(TblPartsTracking.PartNumber) AS FirstOfPartNumber, Sum(TblPartsTracking.Weight) AS SumOfWeight, TblPartsTracking.WONumber, Sum(TblPartsTracking.DrumsBoxes) AS SumOfDrumsBoxes, First(TblPartsTracking.CageNumber) AS FirstOfCageNumber, Sum(TblPartsTracking.Quantity) AS SumOfQuantity, First(TblParts.Fini) AS FirstOfFini, First(TblParts.Épaisseur) AS FirstOfÉpaisseur, First(TblPartsTracking.PONumber) AS FirstOfPONumber, First(TblPartsTracking.ReqDate) AS FirstOfReqDate, First(TblPartsTracking.DateIn) AS FirstOfDateIn
FROM TblParts INNER JOIN TblPartsTracking ON TblParts.PartNumber = TblPartsTracking.PartNumber
WHERE (((TblParts.Line)=[Forms]![FrmPartsTracking]![cboLine]) AND ((TblPartsTracking.DateIn) Between [Forms]![FrmPartsTracking]![txtFmDate] And [Forms]![FrmPartsTracking]![txtToDate]) AND ((TblPartsTracking.Out)=False))
GROUP BY TblPartsTracking.WONumber
ORDER BY First(TblPartsTracking.DateIn), First(TblPartsTracking.PartNumber);


Now,
I'm a bit confused on how I would do if The WONumber, in table TblPartsTracking, is there more than once, replace the part number for the parts that were grouped, to "DIFF Items".. (So Instead of that: First(TblPartsTracking.PartNumber) AS FirstOfPartNumber for the grouped PartNumber, it would be written "DIFF ITEMS")

Any idea / exemple?

Thanks

 

[DomZ]

Bump

 

[DomZ]

Hey pat
Thanks for replying.
Only thing i don't understand is this statement. could you please explain yourself?

2. A query that selects the selects the records from query1 where the count is equal to 1 and joins them back to the original table to obtain partnumber.

3 more things:
1- What you would select in Query1, what Field from TblPartsTracking? Basically Everything, except that PartNumber would be treated as Count not First right?

2- Does Query1 have the "Insert Into" too?
3- how would you do to joins them back to the original table to obtain partnumber. <--- LOL took me a while but i got the meaning of it... damn i need to start sleeping earlier.. However, how would you do to inner join on records where CountOfPartNumber = 1 and obtain the partnumber instead of the count...

The same logic would be applied to CountOfPartNumber > 1 i guess, however, how to replace the count with ''DIFF ITEMS''

Thanks

 

[DomZ]

UPDATE:
I finally got it
Here's how i did it

SELECT First(TblPartsTracking.CustomerID) AS FirstOfCustomerID, First(TblParts.Line) AS FirstOfLine, Count(TblPartsTracking.PartNumber) AS CountOfPartNumber, Sum(TblPartsTracking.Weight) AS SumOfWeight, TblPartsTracking.WONumber, Sum(TblPartsTracking.DrumsBoxes) AS SumOfDrumsBoxes, First(TblPartsTracking.CageNumber) AS FirstOfCageNumber, Sum(TblPartsTracking.Quantity) AS SumOfQuantity, First(TblParts.Fini) AS FirstOfFini, First(TblParts.Épaisseur) AS FirstOfÉpaisseur, First(TblPartsTracking.PONumber) AS FirstOfPONumber, First(TblPartsTracking.ReqDate) AS FirstOfReqDate, First(TblPartsTracking.DateIn) AS FirstOfDateIn
FROM TblParts INNER JOIN TblPartsTracking ON TblParts.PartNumber=TblPartsTracking.PartNumber
WHERE (((TblParts.Line)=Forms!FrmPartsTracking!cboLine) And ((TblPartsTracking.DateIn) Between Forms!FrmPartsTracking!txtFmDate And Forms!FrmPartsTracking!txtToDate) And ((TblPartsTracking.Out)=False))
GROUP BY TblPartsTracking.WONumber
ORDER BY First(TblPartsTracking.DateIn), First(TblPartsTracking.PartNumber);



INSERT INTO TblTmp2 ( CustomerID, Line, PartNumber, NumberOfParts, Weight, WONumber, DrumsBoxes, CageNumber, Quantity, Fini, Épaisseur, PONumber, ReqDate, DateIn )
SELECT First(Inventaire1.FirstOfCustomerID) AS CustomerID, First(Inventaire1.FirstOfLine) AS Line, First(IIf((Inventaire1.CountOfPartNumber)>1,"DIFF ITEMS",TblPartsTracking.PartNumber)) AS PartNumber, First(Inventaire1.CountOfPartNumber) AS NumberOfParts, First(Inventaire1.SumOfWeight) AS Weight, First(Inventaire1.WONumber) AS WONumber, First(Inventaire1.SumOfDrumsBoxes) AS DrumsBoxes, First(Inventaire1.FirstOfCageNumber) AS CageNumber, First(Inventaire1.SumOfQuantity) AS Quantity, First(Inventaire1.FirstOfFini) AS Fini, First(Inventaire1.FirstOfÉpaisseur) AS Épaisseur, First(Inventaire1.FirstOfPONumber) AS PONumber, First(Inventaire1.FirstOfReqDate) AS ReqDate, First(Inventaire1.FirstOfDateIn) AS DateIn
FROM TblPartsTracking INNER JOIN Inventaire1 ON TblPartsTracking.WONumber=Inventaire1.WONumber
GROUP BY TblPartsTracking.WONumber;