Grouping data when inserting

[DoubleD]

Hi,

I need some assistance or ideas on ho to group data when inserting to a MS SQL table.

I currently have a continuous form that an "overview" of my inventory including vendor details, stock levels and sales history.

I now need to create a routine that allows the creation of purchase orders directly from the continuous form. Example, when a user selects item 1234 to be ordered from vendor 1, but at the same time selects item 1222 to be order from vendor 2, I need to create a purchase order for each vendor and all items selected to be purchased.

My question is, how do I group item and vendor and then insert/create a separate purchase order for each vendor? Each purchase order might have multiple items on it.

[Seth Schrock]

A great deal depends on how your tables are structured. I know how I would do it, but that is because I know how I would have my tables structured. Please let us know what your table(s) looks like into which the user in entering the item 1234 from vendor 2 and item 1222 from vendor 2 as well as the table (should really be tables) for your purchase order.

[DoubleD]

Hi Seth,

As this is a new application that we're designing for the customer, I haven't even started structuring the tables for the purchase orders yet (I agree that it should be more than one table).

The idea is to dump the data into a temporary table/s and from there write it into 2 MS SQL tables. I'm just not sure how I would go about creating a purchase order for one vendor with associated items and at the same time a separate purchase order for a different vendor and associated items.

[Seth Schrock]

How I would do it would be to have a Purchase Orders table (tblPurchaseOrders) and a Purchase Order Details (tblPurchaseOrderDetails). If you think of looking at an invoice, you have company information, shipping address, billing address, delivery method, etc. at the top. This would be the tblPurchaseOrders information. Then in the middle, you have all items purchased. This is the tblPurchaseOrderDetails.

However, I wouldn't do a temp table as this means you would have to copy records and delete records and this just creates a mess. What you can do is to have a field in tblPurchaseOrders that marks it as temporary. This can be done with either a status field that allows you to have "New", "Approved", and "Ordered" statuses, or just a simple Yes/No field for whether it has been verified.

[DoubleD]

This sounds perfectly normal. The tables in MS SQL where I will insert my data are called tblPOHDR(holding the PO header info, date created, document type, document number, vendor info) and tblPODtl (stock item, quantity ordered, cost, etc).

I guess i should go back 1 step and tell you how i get my data and what my idea is to create the purchase order from Access.

I have created a continuous form in Access that is based on a query. The query is selecting data from 2 linked SQL tables with a left join between the 2 tables. The reason for the join is to get supplier contact details through to my form. Currently on the form I am displaying stock items with their associated sales history and projected ordering quantities.

My idea is to have a "TO ORDER" field (a text box) on the form where the buyers will fill in how many of an item they want to order from a vendor (some items have multiple vendors allocated to them).

First off it looks like my continuous form design is not really going to work as the "TO ORDER" field replicates any value that I enter into it to all items loaded on the form, so I must rethink the design first of all.

[DoubleD]

Time to revisit this question as I finally got m continuous form to work the way I need it.

By the way, I am using Access 2007 and am still very inexperienced in Access

I have created a POHdr table and a PODtl table. The POHdr table will have the Document Number, Vendor number and Name, Date Created, Delivery Method and Document Totals. The PODtl table will contain each item on order line by line, which includes Item Number, Item Description, Quantity Ordered, Unit Cost, Total line Item Cost(quantity * unit cost)

I just need some help figuring out how to create a purchase order per vendor and have all the items (it could be 1 item only or 50 items) ordered from that vendor on the one purchase order.

[Seth Schrock]

Create a subform for your PODtl table that is linked to a parent form bound to the POHdr table. For an example of this, look at the Northwind sample database provided by Microsoft. Once you select the user to log in as, click New Purchase Order (or press Alt + P).

[DoubleD]

Thanks Seth. I will go look at the Northwind Sample, but it sounds easy enough