Generating purchase orders for each supplier adding relevant products!

[kahless]

I am working on a database system for a small furniture retailer. I have uploaded a picture of the relationships because I don't know how I'd describe it otherwise!

http://www.geocities.com/petehunt2000/relationships.gif

I've hidden tables which aren't relevant. The StockTracker table individually identifies every product ordered so each delivered item can be matched to a specific customer and records of it's movements can be monitored etc. There is a one to many join with the purchase orders table which of joins to suppliers.

At the end of the each day I need to generate purchase orders to be sent out to each supplier. I can easily produce reports to do this using the Sorting and Grouping feature, but I also want to have a record of each purchase order. My problem is that I don't have any idea how to go about generating a purchase order for each supplier and then and then updating the PurchaseID field in tblStockTracker so that the 2 relate, all this bearing in mind that the product needs to go onto the right purchase order for the relevant supplier. All of this needs to be done automatically! Can anyone give me any pointers?

I hope this all makes sense!

Thank you

 

[SiJP]

Hi Kahless, three points..

1) The link doesn't work - too many hits perhaps?
2) The above is quite a few questions.. by the looks, you seem to be pretty new to access development? We'll help you as much as possible, but you need to have some grasp on certain aspects before hand.
3) I would strongly suggest opening and playing around with the Northwind database.. this is designed to do exactly what you nee - once you have the concept there, customisation is where the fun begins, and you will probably find many more questions.

Saying that, you will need to tell us how you are generating your orders.. a button on a form? Typing in the tables directly?

Generally, you would need to create a form that does the inserting of information into your tables for you, then prints (opens) a report from there.. have you this in place already?

Speak soon no doubt!

------------------------
Hit any User to continue

 

[kahless]

Thanks for the reply.

I found a work around in the end which resulted in a massive data structure redesign. It doesn't work exactly as I intended but it does the job!

Ta