Help on a simple query

[kwaldman]

Hi all,

I've been learning Access very organically, and although I can do some
advanced VB coding, the basics of complex queries mystify me. I'm
collecting data on orders placed and orders received, but the problem
is that I have one table for orders with 1 record per order, but I can
receive multiple shipments of a product from 1 order with different
expiration dates, meaning I have a separate table for receipts with
multiple records per product.

For example, I might order 1000 units of product A, 2000 units of
product B and 3000 units of product C.
I receive 200 units of product A with expiration date 1.
Then I receive 500 units of product A with expiration date 2.
Then I receive 3000 units of product C.

I want to create a query that will contain all of my orders that I
haven't received completely - so I wouldn't see an outstanding order
for order C, would see 2000 units outstanding from order B, and 300
units oustanding from order A.

Can anyone provide any help?

Thanks so much.

 

[Guest]

You need to have a one-to-many relationship between each Order and the the
products recieved.

Table 1 contains a record of each order with a unique identifier or primary
key.

As every order is recieved it is recorded in Table 2 which has a unique
identifier for each record of items recieved AND this Table 2 has a foreign
key identical to the primary key in Table 1.

PO#1 5 widets


Rc'd#1 PO#1 2 widgets
exp2/10/10
Rc'd#2 PO#1 3 widgets
exp5/25/20

 

[Michael Gramelspacher]

Hi all,

I've been learning Access very organically, and although I can do some
advanced VB coding, the basics of complex queries mystify me. I'm
collecting data on orders placed and orders received, but the problem
is that I have one table for orders with 1 record per order, but I can
receive multiple shipments of a product from 1 order with different
expiration dates, meaning I have a separate table for receipts with
multiple records per product.

For example, I might order 1000 units of product A, 2000 units of
product B and 3000 units of product C.
I receive 200 units of product A with expiration date 1.
Then I receive 500 units of product A with expiration date 2.
Then I receive 3000 units of product C.

I want to create a query that will contain all of my orders that I
haven't received completely - so I wouldn't see an outstanding order
for order C, would see 2000 units outstanding from order B, and 300
units oustanding from order A.

Can anyone provide any help?

Thanks so much.

Perhapps something like this:

SELECT Orders.Order_id,
Orders.[item_id],
Orders.item_qty,
SUM(Receipts.received_qty) AS Received_qty,
orders.item_qty - SUM(Receipts.received_qty) AS Difference
FROM Orders
INNER JOIN Receipts
ON (Orders.[item_id] = Receipts.[item_id])
AND (Orders.Order_id = Receipts.Order_id)
GROUP BY Orders.Order_id,Orders.[item_id],Orders.item_qty
HAVING (((orders.item_qty - SUM(Receipts.received_qty)) <> 0));