J
Jason Kaufman via AccessMonster.com
I am having quite a problem regarding a database I am trying to put together
from linked tables in our inventory program, Mas 200.
I am using 4 different basic tables
SO1 Make Table - fields: SalesOrderNumber, SalesOrderDate, ShipDate,
CancelDate,CustomerNumber,CustomerPONumber (from the original linked table -
Sales OrderNumber is the primary key)
SO2 Make Table – fields: SalesOrderNumber, ItemNumber, QtyOrdered, QtyShipped,
QtyBckordr (from the original linked table - SalesOrderNumber & ItemNumber
are marked as primary keys)
PO Make Table – fields: ItemNumber, PurchaseOrder#, QtyonPO, PODueDate
(PurchaseOrder# and ItemNumber are marked as primary keys)
IM Data Make Table – ItemNumber, QtyOnHand (itemNumber is marked as primary
key)
The basic thought of the report is to show all the Sales Orders that are
within a date range (which can vary) and for each Sales Order’s ship date,
check the On Hand quantity to see if there is enough quantity present to
fulfill the quantity for each Item Number required for the first Sales Order.
If two or more Sales Orders require the same item number on the same ship
date, then will be sorted by Sales Order Date, so the older Sales Order Date
will be filled first (if the Sales Order Date is also the same, then sort and
fill by Sales Order Number)
Also, will need to track the incoming Purchase Orders by date at the same
time
Now, I have figured that I need to subtract all Sales Orders quantities and
add in all Purchase Order quantities that are due before the 1st Date of the
Date Range from the current On Hand quantity for each Item Number. This
would become the Revised On Hand quantity
From here, I was looking at the Sales Orders within the date range and I want
to see, for each date in the range, all the Sales Orders in two groups.
The first would show the Sales Orders that could be shipped complete for all
items within the Sales Order.
The second would show the Sales Orders that could NOT be shipped complete and
then show the percentage of each order that could be shipped complete.
I am guessing that the Revised On Hand quantity would need to be subtracting
each Sales Order quantity and adding each Purchase Order quantity on a given
date within the range (that quantity being zero (0) if PO or SO quantity is
Null) and then in a sense maintaining the On Hand quantity so will know at
what date the quantity equals or is below zero.
So in the example below, if the starting OH quantity is 20, then the quantity
would be 14 after shipping the 1st line, and then would be 8 after the 2nd
line is shipped, and by SalesOrder# 40, the revised On Hand quantity would
fall to -3 and the SO# 40 would be unable to ship due to a shortage for this
particular item Number (even if all other item#s are complete). This of
course is just an example of how I see this situation in my mind.
SO# ItemNumber ShipExpireDate QtyOrdered Starting OH RevisedOH
21 SR61602AS 1/25/2006 6 20
14
25 SR61602AS 2/1/2006 6 14
8
30 SR61602AS 2/3/2006 7 8
1
40 SR61602AS 2/8/2006 4 1
-3
I have also been creating a new field in relation to the SO2 Make Table.
I am taking (QtyOrdered – QtyShipped) and getting the proper quantity that
still needs to be sent on the Sales Order. I have noticed that sometimes the
QtyBckOrdr does not reflect the actual quantity needing to be shipped. I
have been calling this BOQty. I have been using this quantity as the
quantity that is being subtracted from the On Hand quantity, both before the
date range and inside the date range.
I don't know anything about SQL although I understand how to flip from design
view to SQL view.
If anyone wishes to try and tackle this problem of mine, feel free to email
me at jkaufman [AT] mccubbin [DOT] com and I can email you the database so
you can see the scope of the data.
Thanks for your consideration and help!
Jason
from linked tables in our inventory program, Mas 200.
I am using 4 different basic tables
SO1 Make Table - fields: SalesOrderNumber, SalesOrderDate, ShipDate,
CancelDate,CustomerNumber,CustomerPONumber (from the original linked table -
Sales OrderNumber is the primary key)
SO2 Make Table – fields: SalesOrderNumber, ItemNumber, QtyOrdered, QtyShipped,
QtyBckordr (from the original linked table - SalesOrderNumber & ItemNumber
are marked as primary keys)
PO Make Table – fields: ItemNumber, PurchaseOrder#, QtyonPO, PODueDate
(PurchaseOrder# and ItemNumber are marked as primary keys)
IM Data Make Table – ItemNumber, QtyOnHand (itemNumber is marked as primary
key)
The basic thought of the report is to show all the Sales Orders that are
within a date range (which can vary) and for each Sales Order’s ship date,
check the On Hand quantity to see if there is enough quantity present to
fulfill the quantity for each Item Number required for the first Sales Order.
If two or more Sales Orders require the same item number on the same ship
date, then will be sorted by Sales Order Date, so the older Sales Order Date
will be filled first (if the Sales Order Date is also the same, then sort and
fill by Sales Order Number)
Also, will need to track the incoming Purchase Orders by date at the same
time
Now, I have figured that I need to subtract all Sales Orders quantities and
add in all Purchase Order quantities that are due before the 1st Date of the
Date Range from the current On Hand quantity for each Item Number. This
would become the Revised On Hand quantity
From here, I was looking at the Sales Orders within the date range and I want
to see, for each date in the range, all the Sales Orders in two groups.
The first would show the Sales Orders that could be shipped complete for all
items within the Sales Order.
The second would show the Sales Orders that could NOT be shipped complete and
then show the percentage of each order that could be shipped complete.
I am guessing that the Revised On Hand quantity would need to be subtracting
each Sales Order quantity and adding each Purchase Order quantity on a given
date within the range (that quantity being zero (0) if PO or SO quantity is
Null) and then in a sense maintaining the On Hand quantity so will know at
what date the quantity equals or is below zero.
So in the example below, if the starting OH quantity is 20, then the quantity
would be 14 after shipping the 1st line, and then would be 8 after the 2nd
line is shipped, and by SalesOrder# 40, the revised On Hand quantity would
fall to -3 and the SO# 40 would be unable to ship due to a shortage for this
particular item Number (even if all other item#s are complete). This of
course is just an example of how I see this situation in my mind.
SO# ItemNumber ShipExpireDate QtyOrdered Starting OH RevisedOH
21 SR61602AS 1/25/2006 6 20
14
25 SR61602AS 2/1/2006 6 14
8
30 SR61602AS 2/3/2006 7 8
1
40 SR61602AS 2/8/2006 4 1
-3
I have also been creating a new field in relation to the SO2 Make Table.
I am taking (QtyOrdered – QtyShipped) and getting the proper quantity that
still needs to be sent on the Sales Order. I have noticed that sometimes the
QtyBckOrdr does not reflect the actual quantity needing to be shipped. I
have been calling this BOQty. I have been using this quantity as the
quantity that is being subtracted from the On Hand quantity, both before the
date range and inside the date range.
I don't know anything about SQL although I understand how to flip from design
view to SQL view.
If anyone wishes to try and tackle this problem of mine, feel free to email
me at jkaufman [AT] mccubbin [DOT] com and I can email you the database so
you can see the scope of the data.
Thanks for your consideration and help!
Jason