How can I show we recieved part of a group i.e. 1 of 8

I have a table "Inventory Transactions" that holds the items that have a
purchase order.
On my form for the receiving dept, I have a sub table that shows the
Purchase Orders for supplier and product and that is being received.
If the order is for 8 pallets, how I do I remove one pallet and show that we
are still waiting for 7 more?
michael

Sorry another part that is a little strange, is sometimes we receive more
than we order, so I have to be able to not only remove a value but maybe add
one.


"Michael" ha scritto nel messaggio
...
I have a table "Inventory Transactions" that holds the items that have a
purchase order.
On my form for the receiving dept, I have a sub table that shows the
Purchase Orders for supplier and product and that is being received.
If the order is for 8 pallets, how I do I remove one pallet and show that
we are still waiting for 7 more?
michael

You probably already have tables for PurchOrder (the purchase order header
record), and PurchOrderDetail (the line items on a purchase order.)

Now you need 2 more tables to record the actual deliveries to record the
actual acquisition into stock. The Acquisition table will contain fields
such as:
AcquisitionID AutoNumber primary key
SupplierID Number who this delivery was from.
AcquisitonDate Date/Time when the delivery was received
You will also have the detail table that records the items actually
delivered:
AcquisitionDetailID AutoNumber primary key
AcquisitionID Number which delivery this line belongs to.
Quantity number how many were received.
ProductID number which product was received.
PurchOrderDetailID Number which purchase order line item this delivery
is in response to.

When you receive a delivery, you create a record in the Acquisition table.
Now the database knows which supplier sent the stuff, so if you want to
write some code you can have it lookup which purchase orders from that
supplier have outstanding items, and automatically assign those items to
this delivery. You can then go through and delete the lines for the products
that did not make it yet, or decrease (or increase) the Quantity of the
lines where only part of the quantity was delivered.

To automate that process, you would need to know which purchase order detail
lines were (fully or partially) delivered in any acquisition detail record.
That's the reason for the last field listed above. A PurchOrderDetail record
can have multiple entries in the AcquisitionDetail table (if it is delivered
in dribs and drabs.) When the quantity actually delivered equals or exceeds
the quantity originally ordered, that item has been fully delivered.

In addition to that, you probably want to add a yes/no field to your
PurchOrder table to indicate if the order is complete. This allows you to
mark an order as complete even if not all items have been delivered (e.g.
where an ordered item is no longer being manufactured.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Michael" wrote in message
...
Sorry another part that is a little strange, is sometimes we receive more
than we order, so I have to be able to not only remove a value but maybe
add one.


"Michael" ha scritto nel messaggio
...
I have a table "Inventory Transactions" that holds the items that have a
purchase order.
On my form for the receiving dept, I have a sub table that shows the
Purchase Orders for supplier and product and that is being received.
If the order is for 8 pallets, how I do I remove one pallet and show that
we are still waiting for 7 more?
michael

Thank you Allen,
I will work on this now and see if I can get it going. I just got John
Viescas's Building Access Apps as you suggested, looks very interesting,
hopefully with it I will not be asking so many questions here.
thank you
michael

"Allen Browne" ha scritto nel messaggio
...
You probably already have tables for PurchOrder (the purchase order header
record), and PurchOrderDetail (the line items on a purchase order.)

Now you need 2 more tables to record the actual deliveries to record the
actual acquisition into stock. The Acquisition table will contain fields
such as:
AcquisitionID AutoNumber primary key
SupplierID Number who this delivery was from.
AcquisitonDate Date/Time when the delivery was received
You will also have the detail table that records the items actually
delivered:
AcquisitionDetailID AutoNumber primary key
AcquisitionID Number which delivery this line belongs to.
Quantity number how many were received.
ProductID number which product was received.
PurchOrderDetailID Number which purchase order line item this
delivery is in response to.

When you receive a delivery, you create a record in the Acquisition table.
Now the database knows which supplier sent the stuff, so if you want to
write some code you can have it lookup which purchase orders from that
supplier have outstanding items, and automatically assign those items to
this delivery. You can then go through and delete the lines for the
products that did not make it yet, or decrease (or increase) the Quantity
of the lines where only part of the quantity was delivered.

To automate that process, you would need to know which purchase order
detail lines were (fully or partially) delivered in any acquisition detail
record. That's the reason for the last field listed above. A
PurchOrderDetail record can have multiple entries in the AcquisitionDetail
table (if it is delivered in dribs and drabs.) When the quantity actually
delivered equals or exceeds the quantity originally ordered, that item has
been fully delivered.

In addition to that, you probably want to add a yes/no field to your
PurchOrder table to indicate if the order is complete. This allows you to
mark an order as complete even if not all items have been delivered (e.g.
where an ordered item is no longer being manufactured.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Michael" wrote in message
...
Sorry another part that is a little strange, is sometimes we receive more
than we order, so I have to be able to not only remove a value but maybe
add one.


"Michael" ha scritto nel messaggio
...
I have a table "Inventory Transactions" that holds the items that have a
purchase order.
On my form for the receiving dept, I have a sub table that shows the
Purchase Orders for supplier and product and that is being received.
If the order is for 8 pallets, how I do I remove one pallet and show
that we are still waiting for 7 more?
michael