UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Pos To Customer Orders, Access 2016    
 
   
meyert
post Mar 15 2019, 10:11 AM
Post#1



Posts: 147
Joined: 22-January 08



I don't recall if I have ever asked this forum about this one thing that i want to do - but it has been on my to do list for a long time and I get overwhelmed and give up. I am hoping that perhaps someone here can give me some tips, inspiration or just explain what I am not understanding smile.gif

we have a list of purchase orders - part numbers, quantities and due dates. (about 30% of the POs are for resale only and it is those 30% that I am dealing with)

we also have a list of customer orders - part numbers quantities and ship dates

Shipping has a shipping schedule that we need to confirm to them when the parts are due.

Now if there were only 1 PO for every 1 sales order I wold not have a problem doing that ( I could just link by part number and put the PO due date in as when the part would be here )

My struggle is when there is 1 PO for 100 pcs that covers several customer orders. I need to know when that 100 pcs on the PO runs out and when I need to change to the next PO to use that date

Am I making sense?

Any suggestions or tips?
Go to the top of the page
 
Dan Dungan
post Mar 15 2019, 12:28 PM
Post#2



Posts: 312
Joined: 20-July 10
From: chatsworth, ca


It seems to me that to make any suggestions, it would be useful to know the structure of your tables.

--------------------
Dan
Go to the top of the page
 
meyert
post Mar 15 2019, 03:27 PM
Post#3



Posts: 147
Joined: 22-January 08



well I am not sure what you mean by structure.. but in my mind I am willing to change and set up the structure to whatever would work best to get the results desired.

now there is a lot of data, but it can be changed. The main fields - for the POs: part number, order number, quantity and date due for the customer orders: part number, order number, order quantity, quantity short and allocation date
Go to the top of the page
 
Dan Dungan
post Mar 15 2019, 04:00 PM
Post#4



Posts: 312
Joined: 20-July 10
From: chatsworth, ca


Is the po order number the same as the customer orders order number?

--------------------
Dan
Go to the top of the page
 
meyert
post Mar 15 2019, 05:17 PM
Post#5



Posts: 147
Joined: 22-January 08



no the PO order number is not the same as the customer order number

the part number would be the same on the PO number and the customer number
Go to the top of the page
 
tina t
post Mar 17 2019, 04:47 AM
Post#6



Posts: 5,865
Joined: 11-November 10
From: SoCal, USA


hmm, well, i might make a copy of my db (i'm on a back-it-up kick tonight, or excuse me, this morning!), and then try the following setup:

add a linking table for POs and orders. one PO many cover many orders, AND one order may require many POs.

datawise, add as many records to the linking table as needed to cover an order. then, use a Totals query to pull the Max() PO date for a given order, grouping on the order ID.

if it works, you're gold. if it doesn't, you can ditch this copy of the db and go back to your backup copy, so you can start fresh on trying another solution!

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
meyert
post Mar 17 2019, 02:37 PM
Post#7



Posts: 147
Joined: 22-January 08



thanks for your thoughts.. you and I are on the same track as far the backing up ha ha

I do have the links to the tables for the POs and for the Customer orders

I am not sure that I understand what you are saying here - can you help me understand?

QUOTE
datawise, add as many records to the linking table as needed to cover an order. then, use a Totals query to pull the Max() PO date for a given order, grouping on the order ID.


Maybe I can give you a little more info as well.... example

PO #1 for item 12345 is for 500 pcs due to deliver 3/25
PO #2 for item 12345 is for 500 pcs due to deliver 4/8

Customer orders #1, #2, #3 and #4 are each for 175 pcs. I was hoping I could have a query tell me how many of the customer orders are covered with PO #1 and which of the customer orders will need to wait until the 4/8 PO arrives

Go to the top of the page
 
tina t
post Mar 17 2019, 03:47 PM
Post#8



Posts: 5,865
Joined: 11-November 10
From: SoCal, USA


QUOTE
I was hoping I could have a query tell me how many of the customer orders are covered with PO #1 and which of the customer orders will need to wait until the 4/8 PO arrives

hmm, i was thinking of the linking table as recording what goes where. but maybe too manual and hands-on for the info you hope to see. or maybe the structure will work, but needs good SQL to provide the info you're after - and unfortunately i'm not anything remotely like a SQL whiz.

i PM'd a few of the best minds here, and asked them to take a look at this thread. i think any/all of them can help you figure this out, and teach me a thing or two at the same time. let's wait and see who jumps in on Monday, these guys or anyone else who can help out before i lead you astray!

hth
tina

PS. because it bugs me and also because i don't want to cause anyone confusion, here a correction of the relationships statement from my first post in this thread:

one PO many may cover many orders, AND one order may require many POs.
This post has been edited by tina t: Mar 17 2019, 03:50 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
theDBguy
post Mar 17 2019, 05:37 PM
Post#9


Access Wiki and Forums Moderator
Posts: 74,977
Joined: 19-June 07
From: SunnySandyEggo


Hi.
QUOTE
I was hoping I could have a query tell me how many of the customer orders are covered with PO #1 and which of the customer orders will need to wait until the 4/8 PO arrives
To get the answer to your question, you will first have to decide if you want to use the LIFO or FIFO method. And since each order doesn’t match your PO, you may also need to apply some VBA to take the remaining balance from the next po and apply it to the next order. It would be easier to help you if you could post a sample db with some test data.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
pere_de_chipstic...
post Mar 17 2019, 05:46 PM
Post#10


UtterAccess Editor
Posts: 10,411
Joined: 8-November 07
From: South coast, England


Hi

I'd like to ask for some clarification about your business processes first.

You have a purchase order from a customer and your company creates a Customer Order record.
If you then receive further purchase orders for the same item from the same customer you then add the additional order to the original (customer) order?

If this is this case then I believe you should change your processes as you are in danger of losing an audit-able link between your customer's Purchase Order(s) and your organisation's customer order number(s) and invoices. In a system I have developed for a client each customer purchase order is allocated a customer order number (in my case this is a consignment number) and the consignment given a sub-consignment number of '0'. If the whole consignment cannot be shipped together, the 'late' items are allocated to new sub-consignments. The customers are then invoiced by each sub-consignment and there is a robust audit trail between the original purchase order and the sub-consignment deliveries.

Hope I've understood your situation and that this helps.

--------------------
Warm regards
Bernie
Go to the top of the page
 
meyert
post Mar 23 2019, 09:06 PM
Post#11



Posts: 147
Joined: 22-January 08



QUOTE
To get the answer to your question, you will first have to decide if you want to use the LIFO or FIFO method. And since each order doesn’t match your PO, you may also need to apply some VBA to take the remaining balance from the next po and apply it to the next order. It would be easier to help you if you could post a sample db with some test data.


Thank you for your post... I hate to assume so I want to clarify.. LIFO and FIFO = last in first out and first in first out?

you sound like you are on the path that I am as I was thinking the remaining balance scenario as well. I will have to pull together some simple data in a file and share tomorrow
Go to the top of the page
 
meyert
post Mar 23 2019, 09:11 PM
Post#12



Posts: 147
Joined: 22-January 08



QUOTE
Hi

I'd like to ask for some clarification about your business processes first.

You have a purchase order from a customer and your company creates a Customer Order record.
If you then receive further purchase orders for the same item from the same customer you then add the additional order to the original (customer) order?

If this is this case then I believe you should change your processes as you are in danger of losing an audit-able link between your customer's Purchase Order(s) and your organisation's customer order number(s) and invoices. In a system I have developed for a client each customer purchase order is allocated a customer order number (in my case this is a consignment number) and the consignment given a sub-consignment number of '0'. If the whole consignment cannot be shipped together, the 'late' items are allocated to new sub-consignments. The customers are then invoiced by each sub-consignment and there is a robust audit trail between the original purchase order and the sub-consignment deliveries.

Hope I've understood your situation and that this helps.


Perhaps I was not clear, and I am sorry. The purchase order is from when my company purchases an item from a supplier and the customer order is when my company sells to the customer. (for my purposes the customer purchase order is not included in the data)

this exercise is not dealing with invoicing. Its nothing that formal

I appreciate your thoughts
This post has been edited by meyert: Mar 23 2019, 09:11 PM
Go to the top of the page
 
meyert
post Mar 24 2019, 10:31 AM
Post#13



Posts: 147
Joined: 22-January 08



I have attached some raw data.

The data in purchase order table needs to be compared to the data in the customer order table to confirm if all of the customer orders are covered by the POs. And if the POs are arriving on time

make sense?
Attached File(s)
Attached File  shared_to_utter_access.zip ( 414.95K )Number of downloads: 2
 
Go to the top of the page
 
meyert
post Mar 24 2019, 01:43 PM
Post#14



Posts: 147
Joined: 22-January 08



Hello

Last week I posted about something I was trying to accomplish. I did get some good replies - so thank you. The bottom line is that my providing the data would be helpful... so I have attached a fil

bottom line is that the orders in the PO table need to cover the orders in the customer order table.

If it were one PO to one customer order I would have it done now

Its the POs the cover multiple customer orders that I am struggling with. example.. if the PO is for 500 pcs and there are multiple customer orders for that item how many of the customer orders will be covered by that 500 pc PO? and will the PO arrive in time and will the next PO cover

item P7513A is a good example. there are 78 customer orders - 20 of which are short for shipment. there are 8 POs so each one covers more than one customer order.

Make sense? In my minds eye I would check the next PO qty due in and run against the short customer orders to see which ones are covered. then the "comment" field in the Customer Order table could be updated to the PO request date so show then the parts are due to deliver
Attached File(s)
Attached File  shared_to_utter_access.zip ( 414.95K )Number of downloads: 7
 
Go to the top of the page
 
muddog
post Apr 12 2019, 04:00 PM
Post#15



Posts: 12
Joined: 29-August 17



In my industry, we need to be able to see "ATS" (Available to Sell). ATS looks at on-hand, and then can add/subtract purchases and sales in expected chronological order. If you go negative at any point in your running total, you don't have enough on-hand + purchases to cover the sales demand in the time frame it is needed.

I looked at your sample structure and added some ATS type functionality.

There are other things in your table structure I would address (separate Purchase Orders and Customers Orders into PO Header/PO Detail and Sales Header/Sales Detail tables, etc.) but sticking to ATS....

I'm not sure if you have a function to receive your purchase orders into an inventory/onhand table? I created a simple OnHand table to illustrate how it could be used in an ATS calculation:

OnHand
------------------
ItemNumber (PK)
OnHandQty


I created a tempATS table:
-----------------------------------
Item (index, duplicates OK)
Type
Reference
Date
Qty
RecNumber (autonumber, PK)


I added primary keys to you customer orders and purchases orders tables, which probably helps the performance of the mk_tempATS query

I created a union query:

select onhand.[itemnumber] as [Item], 0 as [TypeSort], 'OnHand' as [Type] ,'' as [Reference], '' as [Date], onhand.onhandqty as [Qty]
from onhand
union

SELECT [PURCHASE ORDERS].[Item Num],1,'Purchase', [PURCHASE ORDERS].[Order Num], [PURCHASE ORDERS].[Prom Del], [PURCHASE ORDERS].Qty
FROM [PURCHASE ORDERS]

UNION SELECT [CUSTOMER ORDERS].[ITEM NUMBER],1,'Sale',[CUSTOMER ORDERS].[ORDER], Nz([REQUEST DATE],[pick date]) AS OrderDate, [ORDER QTY]*-1 AS Expr1
FROM [CUSTOMER ORDERS]
ORDER BY 1, 2, 4;

That gets info from the onhand, purchase order and customer order table. Note, if you had an onhand table, you would probably exclude orders that had a pick date and I wouldn't need the nz function above to have a date for the ATS calculation.


the query apd_tempATS inserts the results of this union query into the tempATS table in correct order (item, type (so on-hand is always first and then PO's/Customer orders in date order). Before apd_tempATS is run, the previous contents of tempATS need to be cleared out....the BUILDATS macros will do that for you..


Finally, qry_ats queries tempATS and shows a running total....

All you need to do is run the macro BuildATS, which will clear the tempATS table, re-run the query to insert records into tempATS (from the union query based on the values in onhand, purchase orders and customer orders at the time you run it).

BuildATS will then open the qry_ats query to show the results.

Here is example output of qry_ats for Item # P4071B1B08450. I added 50 units onhand in the on-hand table for this part # to demonstrate:


Item Type Reference Date Qty Total
P4071B1B08450 OnHand 50 50
P4071B1B08450 Sale 19991199 11/26/2018 -6 44
P4071B1B08450 Sale 19991199 12/3/2018 -10 34
P4071B1B08450 Sale 24563591 3/11/2019 -10 24
P4071B1B08450 Sale 25672397 3/25/2019 -1 23
P4071B1B08450 Purchase 1675457 4/15/2019 1 24
P4071B1B08450 Purchase 1678801 4/22/2019 12 36
P4071B1B08450 Purchase 1680451 4/30/2019 3 39
P4071B1B08450 Purchase 1673399 4/8/2019 11 50
P4071B1B08450 Sale 25330979 4/8/2019 -2 48


Without the initial on-hand, this part would be negative from the get-go:

Item Type Reference Date Qty Total
P4071B1B08450 Sale 19991199 11/26/2018 -6 -6
P4071B1B08450 Sale 19991199 12/3/2018 -10 -16
P4071B1B08450 Sale 24563591 3/11/2019 -10 -26
P4071B1B08450 Sale 25672397 3/25/2019 -1 -27
P4071B1B08450 Purchase 1675457 4/15/2019 1 -26
P4071B1B08450 Purchase 1678801 4/22/2019 12 -14
P4071B1B08450 Purchase 1680451 4/30/2019 3 -11
P4071B1B08450 Purchase 1673399 4/8/2019 11 0
P4071B1B08450 Sale 25330979 4/8/2019 -2 -2

based on the request date (or pick date if null) on the customer order and the Prom Del date on the Purchase Orders.


Hope this helps/gives you some ideas.















Attached File(s)
Attached File  ATSSAMPLE.zip ( 1016.82K )Number of downloads: 3
 
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd April 2019 - 09:32 AM