Group and sum outside of query string

[meldrape]

Hello there, my company is converting to a new inventory system. I have the following query that eventually produces an export file. I hesitate to post it because it's huge but I have to.

tblExport.Execute "SELECT qyNewExport.cQty, qyNewExport.CUNIT, qyNewExport.CCATGORDR, qyNewExport.CCATG, qyNewExport.CPHASE, qyNewExport.CPART, qyNewExport.cPartDescr, qyNewExport.CPLANID, qyNewExport.aPartName, qyNewExport.aUnit, qyNewExport.aConversion, PLAN.* INTO " & tmpFile & " FROM PLAN INNER JOIN qyNewExport ON PLAN.CPLANID = qyNewExport.CPLANID WHERE (((qyNewExport.CPLANID) = '" & strPlanID & "')) ORDER BY qyNewExport.CCATGORDR, qyNewExport.CPART;"

"tmpfile" is just a random name for the temporary table. Anyway, there are two possible names for the same inventory part: CPART (old name) or aPartName (if substitute name is required). All of the CPART names are unique, but if an "aPartName" exists, it's a new substitute name for CPART and sometimes those are the same. Here inlies the rub. If let's say 2 aPartNames are the same but the CPART name is different, i.e.

Qty=15 CPART is 010206RC aPartName is 0102RC
Qty=10 CPART is 010208RC aPartName is 0102RC
so the answer I want is Qty=25 for 0102RC

then I have to add up the quantities. Not sure how to do that programmatically? Do I loop through and assign a new variable name for the part like newpart=newpart & qyNewExport.aPartName then do an "if" statement that if newpart=qyNewExport.aPartName combine them? I know it's convoluted. I can't think of how to explain it any better. Any thoughts would be greatly appreciated. Many thanks in advance.

 

[PCLewis]

Not sure why you don't use a basic GROUP BY select here. YOu have to replace the PLAN.* with a list of each field and add all fields except the cQty to the GROUP BY clause but it's pretty straight forward...

SELECT SUM(qyNewExport.cQty) as cQty, 
qyNewExport.CUNIT, qyNewExport.CCATGORDR,
qyNewExport.CCATG, qyNewExport.CPHASE, 
qyNewExport.CPART, qyNewExport.cPartDescr,
qyNewExport.CPLANID, qyNewExport.aPartName,
qyNewExport.aUnit, qyNewExport.aConversion,
PLAN.field1, PLAN.field2, ... 
INTO tmpfile 
FROM PLAN INNER JOIN qyNewExport ON PLAN.CPLANID = qyNewExport.CPLANID 
WHERE (((qyNewExport.CPLANID) = '')) 
GROUP BY qyNewExport.CUNIT, qyNewExport.CCATGORDR,
qyNewExport.CCATG, qyNewExport.CPHASE, 
qyNewExport.CPART, qyNewExport.cPartDescr,
qyNewExport.CPLANID, qyNewExport.aPartName,
qyNewExport.aUnit, qyNewExport.aConversion,
PLAN.field1, PLAN.field2, ... 
ORDER BY qyNewExport.CCATGORDR, qyNewExport.CPART;

The other way is just to do a query on your tmpFile to do the sum on cQTY in there.

 

[meldrape]

Hi PCLewis, yes, that's what I ended up doing. A query on my tmpfile. It's working fine now. Many thanks for your reply!