Simple Count Records 1

[pbundrant]

I have searched and searched and nothing currently posted is helping me with my issue. All I need is a count on the number of records from one query for an update query.

Here is the statement as it stands:

INSERT INTO [SERIAL NUMBERS] ( GE_SERIAL_NUMBER, ETF_SERIAL_NUMBER, ETF_HEAT_CODE, [MILL_ HEAT_NO], SHOP_ORDER, NOTES, PACKING_SLIP_NUM, [DATE], SALES_ORDER, QTY_TO_SHIP )
SELECT qryDummy.GE_SER_GEAE_SER AS Expr13, qryDummy.GE_SER_ETF_SER AS Expr14, qryDummy.GE_SER_ETF_HEAT AS Expr15, qryDummy.GE_SER_MILL_HEAT AS Expr16, qryDummy.GE_SER_ETF_ORDER AS Expr17, qryDummy.GE_SER_COMMENTS AS Expr18, [Forms]![NEXT PACKING SLIP NUMBER]![NEXT] AS PK_SLP_NUM, [Forms]![ETF_GE_SN_SUBFORM]![Text31] AS [Date], [Forms]![ETF_GE_SN_SUBFORM]![Text33] AS [Sales Order], Count(qryDummy.GE_SER_GEAE_SER) AS [Qty To Ship]
FROM qryDummy
GROUP BY qryDummy.GE_SER_GEAE_SER, qryDummy.GE_SER_ETF_SER, qryDummy.GE_SER_ETF_HEAT, qryDummy.GE_SER_MILL_HEAT, qryDummy.GE_SER_ETF_ORDER, qryDummy.GE_SER_COMMENTS;

I keep getting "1" as the total...instead of whatever the count may be.

Thanks for any help,

Pooh

 

[PHV]

Something like this ?
INSERT INTO [SERIAL NUMBERS] ( GE_SERIAL_NUMBER, ETF_SERIAL_NUMBER, ETF_HEAT_CODE, [MILL_ HEAT_NO], SHOP_ORDER, NOTES, PACKING_SLIP_NUM, [DATE], SALES_ORDER, QTY_TO_SHIP )
SELECT A.GE_SER_GEAE_SER, A.GE_SER_ETF_SER, A.GE_SER_ETF_HEAT, A.GE_SER_MILL_HEAT, A.GE_SER_ETF_ORDER, A.GE_SER_COMMENTS, [Forms]![NEXT PACKING SLIP NUMBER]![NEXT] AS PK_SLP_NUM, [Forms]![ETF_GE_SN_SUBFORM]![Text31] AS [Date], [Forms]![ETF_GE_SN_SUBFORM]![Text33] AS [Sales Order], B.[Qty To Ship]
FROM qryDummy AS A INNER JOIN (
SELECT GE_SER_GEAE_SER, Count(*) AS [Qty To Ship] FROM qryDummy GROUP BY GE_SER_GEAE_SERAS
) AS B ON A.GE_SER_GEAE_SER = B.GE_SER_GEAE_SER;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pbundrant]

Thanks PH,

Still getting "1" for any amount.
...hmmmm

 

[PHV]

Does qryDummy returns several rows for a given GE_SER_GEAE_SER value ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pbundrant]

No, the query returns a quantity (user entered) of distinct serial numbers. No two numbers are the same. I need the exact quantity (of serial numbers) avaiable for the pack slip. A user may enter any number needed to ship, but only the quantity actually avaiable will be listed when the query is run.

 

[PHV]

So, I quite don't understand why you played with the Count aggregate function ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pbundrant]

To count the number of records returned.

Is there a better way to count records?

 

[PHV]

To count the number of records returned
returned by what ? the whole query ?
INSERT INTO [SERIAL NUMBERS] ( GE_SERIAL_NUMBER, ETF_SERIAL_NUMBER, ETF_HEAT_CODE, [MILL_ HEAT_NO], SHOP_ORDER, NOTES, PACKING_SLIP_NUM, [DATE], SALES_ORDER, QTY_TO_SHIP )
SELECT GE_SER_GEAE_SER, GE_SER_ETF_SER, GE_SER_ETF_HEAT, GE_SER_MILL_HEAT, GE_SER_ETF_ORDER, GE_SER_COMMENTS, [Forms]![NEXT PACKING SLIP NUMBER]![NEXT] AS PK_SLP_NUM, [Forms]![ETF_GE_SN_SUBFORM]![Text31] AS [Date], [Forms]![ETF_GE_SN_SUBFORM]![Text33] AS [Sales Order], (SELECT Count(*) FROM qryDummy) AS [Qty To Ship]
FROM qryDummy;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pbundrant]

Wow....THANKS! It worked

...and yes to count the number of records returned by the first query. This query only appends/adds those records to a table which is used to print all pack slip info. The exact "quantity to ship" is a key piece of info when all documentation is printed/saved. I am sure there probably is an easier way of going about it, but oh well.

Again, Thanks So Much!

Have a Great Week,
Pooh