By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,654 Members | 1,422 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,654 IT Pros & Developers. It's quick & easy.

query for tot

P: 22
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

Expand|Select|Wrap|Line Numbers
  1. SELECT   Products.[Product Name]
  2.         ,OutgoingTBL.Type
  3.         ,OutgoingTBL.Size
  4.         ,Products.QTY
  5.         ,OutgoingTBL.[Qty Out]
  6.         ,Sum([products]![Qty]-nz([Qty Out],0)) AS [Current Tyre Type in stock]
  7.  
  8. FROM     Products INNER JOIN OutgoingTBL ON Products.Type = OutgoingTBL.Type
  9.  
  10. GROUP BY Products.[Product Name]
  11.         ,OutgoingTBL.Type
  12.         ,OutgoingTBL.Size
  13.         ,Products.QTY
  14.         ,OutgoingTBL.[Qty Out]
  15.  
  16. HAVING   (((OutgoingTBL.Type) Like "*" & [Enter Type Allterrain 4WD, Light Truck, MUDTerrain, Passenger, SUV, Tube] & "*"));
Jul 16 '10 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 1,240
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
Jul 16 '10 #2

P: 22
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
Jul 17 '10 #3

patjones
Expert 100+
P: 931
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
Jul 19 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.