 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / August 2005Two queries
I have two tables: Allocated and Product Lot Info I want an expression called Unallocated to give me either: - The difference in amount allocated from the Allocated table and the Product Lot Info table - If the product from the Product Lot Info table is not allocated (not in the Allocated table) I want it to display the product quantity from the Product Lot Info table How do I set up the query to do this? >I have two tables: Allocated and Product Lot Info > [quoted text clipped - 7 lines] > >How do I set up the query to do this? Create a query joining [Product Lot Info] to [Allocated]; select the join line and choose option 2 (or 3) - "Show all info in Product Lot Info and matching records in Allocated". Use an expression [Product Lot Info].[Quantity] - NZ([Allocated].[Quantity]) The NZ() function will take the NULL (which will be created for nonexistant records by the outer join) and convert it to a zero. John W. Vinson[MVP] > >I have two tables: Allocated and Product Lot Info > > [quoted text clipped - 20 lines] > > John W. Vinson[MVP] How do I also have the query do as above but if product is allocated take the difference from the Product Lot Info table and the sum of product allocated by lot number? >How do I also have the query do as above but if product is allocated take >the difference from the Product Lot Info table and the sum of product >allocated by lot number? Create and save a Totals query based on the Allocated table, grouping by [Lot Number] and summing the allocation; use this query in your Join rather than the allocations table itself. John W. Vinson[MVP] > >How do I also have the query do as above but if product is allocated take > >the difference from the Product Lot Info table and the sum of product [quoted text clipped - 5 lines] > > John W. Vinson[MVP] Thanks John it worked perfectly. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.