Two queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

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?
Click to expand...

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]
 
John Vinson said:
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?
Click to expand...

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]
Click to expand...


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?
Click to expand...

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]
 
John Vinson said:
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?
Click to expand...

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]
Click to expand...

Thanks John it worked perfectly.
 
Back
Top