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

data duplication in query

P: 22
Hi again all, Im starting to feel like i live here...I have an issue with data duplicating in a query. I have been rolling around now for two days and about to throw the computer out. Here goes my issue following.

I have two tables
Product
Outgoing
(I have not made a relationship) the only fields the same in these two tables are type and date - further any way i do make a relationship no info displays in the query)

I have made a query pulling down from both product table and outgoing table. with the intent to show how many product items are left in stock.
Expand|Select|Wrap|Line Numbers
  1. [Qty]-[Qty out]
based on selecting the type in outgoing and Qty out with the Qty coming from the Product table. Now the query seems to be working and calculating correctly but I find that everything is duplicating - I have checked that it is doing so by the ID number can someone please give me a heads up on how i sort this prob...

the SQL for the outgoing query is as follows
Expand|Select|Wrap|Line Numbers
  1. SELECT OutgoingTBL.ID, OutgoingTBL.Date, OutgoingTBL.Type, OutgoingTBL.Size, OutgoingTBL.[Qty Out], [Qty]-[Qty Out] AS [Current Tyre Type in stock], OutgoingTBL.Freight, OutgoingTBL.[Cost Price], Products.QTY
  2. FROM OutgoingTBL, Products
  3. GROUP BY OutgoingTBL.ID, OutgoingTBL.Date, OutgoingTBL.Type, OutgoingTBL.Size, OutgoingTBL.[Qty Out], OutgoingTBL.Freight, OutgoingTBL.[Cost Price], Products.QTY;
thanking you in advance
Jun 30 '10 #1
Share this Question
Share on Google+
1 Reply


P: 22
@heart01
Well it looks like I have the above issue working now. Perhaps I was too quick to jump into asking...what I did was made a relationship to the product and outgoing tables based on type as this is the field they both have in common. I tried all joins and found one that works. So i dont get duplication now and can select on type which in turn pulls down each outgoing entry for that type and provides a total remaining stock.
Jul 1 '10 #2

Post your reply

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