Full Version: Order entry database for a grocery broker
UtterAccess Forums > Microsoft® Access > Access Forms
alpha
I am developing an order entry database for a grocery broker. I will be asking a number of questions as the project develops. Thanks in advance for any assistance you provide.
I have the following tables currently set up:
1.Order header
2.Order detail
3.Customer address
4.Vendor address
5.Customer profile
6.Vendor items
The transaction history will reside in the tables “Order Header” and “Order Detail”.
The “Vendor Items” table will contain all the products offered by each vendor. This information is provided by the vendors.
The "Customer Profile" table will contain all products that have been checkmarked in the "Vendor Items" table, to allow the broker to select items from a shorter list.
Issues
1. What is the most idiot-proof way to select the customer and vendor contact information? The "Order Header" and "Order Detail" tables must contain a complete record of the transaction, and may not change even if the related tables change.Is there an idiot-proof method you can recommend?
2. The broker selects products from a "Customer Profile" table, which shows all products that have been checkmarked in the "Vendor Items" table. This allows for efficient selection of products. How would you structure this?
3. After the order is completed, the "Order Header" record and "Order Detail" records should be moved to "Order Header History" and "Order Detail History" tables, respectively. What's the best way to do this?
Thanks!
alpha
Here's an observation about the layout for the "Customer Profile" form.
efore I select vendor items for a particular customer: (1) A customer must exist in the "Customer Address" table, (2) A vendor must exist in the "Vendor Address" table,and (3) Vendor items must exist in the "Vendor Items" table. I should then be able to select vendor items to be included in the "Customer Profile" table.
I guess I need a Customer Address form with an embedded "Vendor Address" subform. Inside the "Vendor Address" subform. I'll need a "Customer Profile" subform embedded in the "Vendor Address" subform!
Arrgghhh!
Any bright ideas out there? There must be an easier way!
alpha
OK, I've decided I am not going to create nested forms; it is not inuitive enough.
When in the "Order Header" form, the user will input the Customer ID and the Vendor ID, then click on the "Order Detail" button to open the "Customer Profile" form.
Here's my question: In the Customer Profile form, how can I show only the products that are related to the specific customer and vendor?
Ocreated a button using the wizard, and it generated the code shown below. The wizard allows me to select only one criterion (e.g.; "Vend ID"), as shown below. How do I change this code to filter based on both "Vend ID" and "Cust ID"?

Private Sub btn_Order_Detail_Click()
On Error GoTo Err_btn_Order_Detail_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frm Cust Profile"

stLinkCriteria = "[Vend ID]=" & Me![Vend ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_btn_Order_Detail_Click:
Exit Sub
Err_btn_Order_Detail_Click:
MsgBox Err.Description
Resume Exit_btn_Order_Detail_Click

End Sub

Thanks!
aoh
stLinkCriteria = "[Vend ID]=" & Me![Vend ID] & " And [CustId] = " & Me![CustId]
Jack Cowley
1. Take a look at the Northwind sample db that comes with Access as it has 99% of what you want to do.
. See above.
3. This is unnecessary and should not be considered.
My 2 cents worth says that you will find solutions to the majority of your questions in the Northwind database... Good luck!
hth,
Jack
alpha
Thanks Anne and Jack for the responses. I appreciate your input. I'll study the Northwind database for more guidance.
found a great resource that described the following issues, and helped me to develop a "Customer Header" form.
1. How to display a name (e.g.; Customer Name), but store an ID number (e.g.; Cust ID), and
2. Updating and displaying form controls after you look up data in a field (i.e.; display contact info based on the Cust ID).
See "Practical Advanced Microsoft Access Forms and macros" at http://eis.bris.ac.uk/~ccmjs/accform.htm .
My next step is to create a "Customer Profile" form. This form should list products that a specific customer ususally buys from a specific vendor (like a menu with only favorite selections). The items listed on this form will be selected from the "Vendor Items" form.
I have to develop a way to select these favorite items for display on the form, and then select from the favorite items to create an order.
And I've got to do it within the next 24 hours...
Jack Cowley
Your deadline seems unrealistic unless you are creating this for a school project, as you appear to be new to relational databases and Access in particular. The first thing you need to do before you concern yourself about forms is to be sure that your data is normalized. If your data is not properly normalized then creating forms, queries and reports will be much harder than necessary. I strongly suggest that you make sure that your tables are set up properly and then start worrying about how you want to display the data....
Is for your question I would probably use the method described in this article.
That is my 2 cents worth....
hth,
Jack
alpha
Thanks for your responses.
successfully prepared a draft "Customer Profile" form!
There are three levels of vendor items:
1. The "Vendor Items" table is the master list.
2. The "Customer Product Profile" table is a subset of the "Vendor Items" table.
3. The "Order Detail" table contains records that are a subset of the records in the "Customer Product Profile" table.
The customer will order items shown in the "Customer Product Profile." These items will be recorded in the "Order Detail" table when the sales order is generated.
Today I will determine how to copy selected items from the "Customer Product Profile" to the "Order Detail" table.I'll need to save the order header and the order detail records simultaneously, and generate a sales order number that links the two. I found this guidance: UtterAcess tip showing how to copy fields from one table to another.
Owonder if I need a temporary location for the order header fields until I am ready to save both parts of the sales order (i.e.; the order header and the order detail).
MiaAccess
It seems to me that you are not taking anyones advise. I am not exactly sure why you are writing to the newsgroup, you are going forward the way you picture things and reporting the progress.
Why are you beating your head agains the wall, look at the Northwind.
Anne
JVanKirk
Just looking at how you are referring to your tables and so forth one has to wonder...are you using good naming conventions, if not, are you following other rules that are much more essential like normalization. If you are doing this for a school project then drive on and get whatever grade you get, if you are doing this for a business, consider what kind of references youwill get fromthat business when they start running into errors and headaches due to improper normalization of the database turning into improper data handling....eventually, very possibly a failed database and a need for a new one by the customer who is now not happy at all with you for not designing the db right from the start.
Take a fellow Pennsylvanian proud and build this thing good!!
Jason
alpha
I'm sorry that you got that impression. I did look at the Northwind database, and it helped a bit.
This forum has been a great resource. I have taken a great deal of advice from this forum, including normalizing the database.
The advice provided by Jack regarding synchronized combo boxes is good to know, and I've saved that info. It isn't a good fit for this situation however. I found a way to pass the "Customer ID" and "Vendor ID" from the "Order Header" form to the "Customer Profile" form. When the "Customer Profile" form opens, it displays only the related records.
This project does have special requirements, and it's not easy to fit it into an existing template. I am not an experienced database developer, but would like to learn.
Jack, can you be more specific about the naming conventions?
Thank you everyone for your help!
alpha
I took another look at Northwind. As a result, I changed the names of tables, queries, and forms in my database.
here's no way I can do this without you folks; please be patient with me...
Jack Cowley
I see you have looked at Northwind, but it is a terrible example of proper naming conventions. Here are some general rules that everyone should follow:
. No spaces in any object or field names
2. Add these prefixes: tblMyTable, frmMyForm, qryMyQuery, rptMyReport, subfrmMySubform
3. Do not use Reserved Words for the names of objects. There is a list of words in the archives here
4. Do not use characters such as the #, /, %, etc. in the names of objects or field names.
5. Make the names of objects and fields descriptive, but not too long. MY_AR_MPR_FRM_04 is not what I would consider an easily understood field name to someone trying to assist a developer.
If you follow those basic rules it will be easier for you to know that a query is the Record Source for a form rather than a table. And you won't have problems when your code does not open a form that has 2 hard-to-see spaces in the name instead of a single space.
Ounderstand about your db not neatly fitting into an existing template. Without seeing what you are doing we have to try to make educated guesses and advise you as best we can. I know that you appreciate the help and that is what we are here for. Just keep in mind that what you are doing is very straightforward for you, but we have to try and read between the lines because we do not have the database in front of us.
You are a valued member here at UA so keep the questions coming!!
Jack
alpha
I broke everything when I changed the names throughout; it's taken hours to get back to where I was.
I have been unable to achieve my next goal. I want to select items on a menu, then copy the selected items to the order detail. The logic is very different from that shown in Northwind.
I'll post a file sometime tomorrow.
Jack Cowley
If you are going to be developing Access databases on a regular basis I would suggest that you invest in a program called Find and Replace by Rick Fisher. It is inexpensive and is worth every penny. You can download a trial version here.
mmm. You certainly can do what you want by selecting items and then copying them to a table, but it seems like the long way round. What if you want to add another item? Do you have to go back to the previous form and select a new item?
I will be around Friday afternoon and look forward to seeing what you are up to.
Jack
ScottGem
I'm gonna stick my 2 cents in here after reading the thread. I think I understand where you are going with part of this. You have your Products table which should be structured like so:
roductID (PK, Autonumber)
ProductCode (this is the code supplied by the vendor to identify their product)
ProductDescription
VendorID (FK to Vendor table)
UnitCost (cost of the item)
You may have other fields in this table that describe each product but those are the base.
Next you have your CustomerProfile table, the strucutre of whihc should be:
ProfileID (PK Autonumber)
CustomerID (FK to customer table)
ProductID (FK to product table)
Now, from this structure you can restrict selectable products to those in the Customer profile.
However, as I type this, I'm not sure its the best way. Your stated goal was to make it more efficient to select products by limiting the list. But what happens if a customer needs to order a product not in their profile? That means you have to go back and change the profile before the product can be ordered. So a better way to make the product lists more manageable is to add a Product category to the Product table (I.E. Baked Goods, Produce, Canned Vegetables, Cereal, etc.). Then you would use a cascading combobox setup to first select the category and then filter the product combo for those int hat category.
alpha
I've attached an mdb file showing what I've accomplished so far.
Scott: Unfortunately, the customer requires the Customer Product Profile.)
The relationships have me a bit confused, and I can't figure out the best way to manage the data in each of these.
1. Products
2. Customer Products Profile
3. Order Detail
The Customer Profile is a subset of the Products list.
The Order Detail is a subset of the Customer Profile list.
How can I copy records from the Products table to the Customer Product Profile?
How can I move copy records from the Customer Product Profile to the Order Detail?
ScottGem
I've taken a look at your database and there are a number of normalization issues. Some specifics.
* Order Date doesn't belong in OrderDetail, yoiu already have it in Orders.
* Unless Detail items are not all added at the same time as the Order is created, then you don't need Create Date in OrderDetail
* The Removed Date and Removed from tickler are also questionable, unless line items of an order need to be treated separately
* its not clear what the X fields are in the Orders, OrderDetail amd CustProfile tables are, but they may also be redundant.

There are also a number of other design issues.
* Label Name in the Employee table is, I suspect, A concantenation of the Employees first and last names. If so, then its a calculated value and shouldn't be stored.
* You shouldn't use space in object names.
* I assume the 1-4 fields for addresses are for the whole address. I would use one (maybe 2) fields for the street address and then have City, State and ZIP fields rather then generically names fields.
* In your Product table, do you plan on keeping a history of price changes?, If so the Price change Date is unneccessary. If you are planning on keeoping such a history you should do so in a separate table i.e.; PriceHistory; HistoryID (PK Autonumber), ProductID, Price, EffectiveDate
* The CustomerProfile should be more along the lines I outlined in my previous reply. The last order info can be gotten from queries and does not need to be stored. You don't need VendorID since that''s in the Product table. Not sure what the other fields are for.

Now to answer your last question;
Order a look at your database and there are a number of normalization issues. Some specifics.
* Order Date doesn't belong in OrderDetail, yoiu already have it in Orders.
* Unless Detail items are not all added at the same time as the Order is created, then you don't need Create Date in OrderDetail
* The Removed Date and Removed from tickler are also questionable, unless line items of an order need to be treated separately
* its not clear what the X fields are in the Orders, OrderDetail amd CustProfile tables are, but they may also be redundant.

There are also a number of other design issues.
* Label Name in the Employee table is, I suspect, A concantenation of the Employees first and last names. If so, then its a calculated value and shouldn't be stored.
* You shouldn't use space in object names.
* I assume the 1-4 fields for addresses are for the whole address. I would use one (maybe 2) fields for the street address and then have City, State and ZIP fields rather then generically names fields.
* In your Product table, do you plan on keeping a history of price changes?, If so the Price change Date is unneccessary. If you are planning on keeoping such a history you should do so in a separate table i.e.; PriceHistory; HistoryID (PK Autonumber), ProductID, Price, EffectiveDate
* The CustomerProfile should be more along the lines I outlined in my previous reply. The last order info can be gotten from queries and does not need to be stored. You don't need VendorID since that''s in the Product table. Not sure what the other fields are for.

Now to answer your last question;
ORDER Detail is a child of Orders (not a subset of the Profile). Products is essentially a lookup for OrderDetail
Customer Profile is a JOIN table (not really a subset), which joins Customer and Products to filter the products list for a customer.

You don't "copy" records from one table to another. You reference them using a foreign key. Your Profile form, should be a main/subform. The Main form showing the Customer table, the subform, the Profile. You select a Customer on the main form then add records in the subform selecting the Products from a combo.
The OrderDetail is also part of a main/subform. The main form is the Order table and the subform the detail. On the subform you select Product IDs via a combo from the Customer profile. The combo is filtered from the Customer combo on the main form.
alpha
Scott,
agree with your analysis for the most part; and will make the changes you recommend. Thank you!
A few comments:
- Unnecessary fields: I am re-using an old database as a starting point, and will have to import data from existing tables. I have marked most of the unnecessary fields with an X, but felt the need to keep them for now. I am also re-using certain field names that existed in the old database, even though they wouldn't be my first choice (e.g.; addr 1, addr 2). I admit that it is strange to have the company name be the "addr 1" field!
- Field Names: I got tired of adding spaces to field labels in forms, and got into the habit of letting the field name match the label.
- I don't need a history of price changes. I just need the last price paid and the date.
- I don't understand how to set up the Customer Product Profile in the manner you describe.
Thanks,
Elliot
ScottGem
When it comes to database design I advocate trying to get the design as right as possible from the beginning. I understand the temptation of importing first, but I would prefer correcting the design problems and impoirting data correctly. I also understand the annoynace of changing labels, but I tend to abreviate more in field names so I have to change labels anyway.
If you don't need a history then why do you need a date? I don't see what value storing the effective date of the last price increase has.
What specifically don't you understand about the Profile? As I understand it the Profile is primarily a tool to filter products that is specific to the Customer. In that case, all it needs is the foreign keys for customer and product. The query behind the Combo used to select Product in the OrderDetails would join the Profile to the Product table on productID and filter for CustomerID (set the crtiteria for CustomerID to =Forms!formname!customercombo), the CustomerID column can have the Showbox unchecked. You would also include the Product Description from the Product table so the user can select from the descriptions.
aoh
Sorry to butt in guys, but my 2 cents worth on one little point:
<
If you fill in the "Caption" for a field in table design, this will be used for all labels for that fields - means you can call the field whatever you want and not have to re-type the forms labels.
ScottGem
Good point! thanks.gif
alpha
>Thanks for the suggestion. This form would be used for creating and editing the Profile. To prevent duplicate records, I'll create a multiple key in the Profile table consisting of the Product ID and the Customer ID.

I'm concerned about the ease of editing the Profile. As an alternative, is it possible to display a subform showing all products, with a Select (Yes/No) field for each? If I want to include a product in a customer's profile, then I check the "Select" box, and save the selected records in the Profile table. The Profile may be easier to edit if the forms work in this manner.

The next step is to select products from the Profile table for inclusion in the Order Detail.
Edited by: alpha on Tue Oct 5 11:55:39 EDT 2004.
ScottGem
Correct. If you need to prevent duplicate combinations of customer and Product, then a multi-field index will do the job for you.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.