query for tot

[heart01]

Hello All, I have run into an issue

I have a Product table which captures all incoming stock
I have a Outgoing table which captures all outgoing stock

I have created a query from the product and outgoing tables and in a colum by itself have totalled the amount of stock per product however, what I am needing to see is

1. a report that collates all specific type of stock for example Passenger and includes any outgoing.

I have so far got this far as per below, what I am finding is info is now being duplicated and showing multiple entries for any one particular item...i would appreciate help on this one

SELECT   Products.[Product Name]
        ,OutgoingTBL.Type
        ,OutgoingTBL.Size
        ,Products.QTY
        ,OutgoingTBL.[Qty Out]
        ,Sum([products]![Qty]-nz([Qty Out],0)) AS [Current Tyre Type in stock]

FROM     Products INNER JOIN OutgoingTBL ON Products.Type = OutgoingTBL.Type

GROUP BY Products.[Product Name]
        ,OutgoingTBL.Type
        ,OutgoingTBL.Size
        ,Products.QTY
        ,OutgoingTBL.[Qty Out]

HAVING   (((OutgoingTBL.Type) Like "*" & [Enter Type Allterrain 4WD, Light Truck, MUDTerrain, Passenger, SUV, Tube] & "*"));

[jimatqsi]

Because you included .qty and .qtyout in your grouping you are seeing one row for every change in either quantity. Remove those two from your Group By.


Jim

[heart01]

Cheers Jimatqsi, I removed the .qty and .qtyout from the grouping and I am receiving error message stating i tried to execute a query that does not include specified QTY as aggregate function...any heads up on this perhaps

[patjones]

Anything that you remove from the GROUP BY clause must be removed from the SELECT clause as well. Otherwise you will obtain an error.

In your situation, it almost seems as though you don't even need to group by anything. The way you have your query written out right now, the grouping will yield sensible results only if there are multiple records containing the same entries for Product Name, Type, Size, QTY, and Qty Out all at the same time...because the idea behind GROUP BY is that it combines like records into a summary record.

However, if even one of those is different, it will not be included in the grouping. For instance, if you perform a grouping on:

Product Name Size Type QTY Qty Out

4WD 35 A 34 23
4WD 35 A 34 23
4WD 35 A 34 18


...then you will end up with two records: one that combines the first two records in the original set, and another that is simply the third record. This is why jimatsqi asked for you to remove QTY and Qty Out from the grouping...each change in those entries will result in a different record in the output.

Pat