Ordering Database

[starlancer805]

Hello Everyone!



I am not sure if this is possible and I will try to explain what I am trying to do as best I can. =) I am new at this so any help is appreciated!

I am trying to create an ordering log for the requesting and purchasing of supplies, which I am keeping track of on separate tables. Requests will be done by multiple people and usually contain one item, however, they can be ordered at the same time or separately. I cannot figure out how to combine the multiple requests into one order and so on. So as an example: lets say employee B requests item "x", employee D requests item "t", and employee E requests item "p". Then employee Z can go and place all the items on the same order, two together and one separately, or all separately. Is there any way of going about to do this? I have been following the Northwind Sample database as an guide so I am basing most of the relationships and tables off of that.




Here is part of the relationships I have so far on the database. I am not sure if I have the relation of the request table quite right yet.

Again any help is appreciated!

[June7]

How do you process employee requests? If employee A asks for 5 staplers and employee B asks for 3 staplers, do you combine them into one line item in Order_Details for 8 staplers? Maybe should save ID from Order_Details as foreign key in Requested_Orders.

Beware of circular referencing http://www.codeproject.com/Articles/...atabase-Design
Requested_Orders should not be linked to both Orders and Order_Details.

Why are there two link lines to ProductID in Order_Details?

Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention. Better would be UnitCost. Also, Total Cost should not be saved but calculated when needed.

Should Conformation be spelled Confirmation?

[starlancer805]

I hadn't really considered that scenario because currently the person handing in the request doesn't generally determine the quantity ordered. The person in charge of ordering determines the quantity, based on what we have ordered in the past. If two different quantities of the same item were to be ordered at the same time, I would say that they would be combined on the same order. But to get rid of the circular reference I will use ID as a foreign key in Requested_Orders as you suggested.

The two link lines are for the items that are currently being used and ordered on a regular basis and the second is for the archive of items that we don't use any more. It seems from reading up on "archive" tables that it is not something normally done, so I just put it there and I was planning on tackling that later.

Yea it should be confirmation not conformation.

[June7]

Don't 'archive', set a field as either 'active' or 'inactive', can be a Yes/No or text type field.

[starlancer805]

Yea that is what I have been reading and will be doing that.

I think I figured out how to do what I wanted to try to do. I was thinking just creating two forms. One with a query directly to the supplies list, the other with a query only to the requested_orders table. Any thoughts?

Also, if I did that, I am wondering if access would know to fill in each request with the specific OrderID. With the relationship set up between the order_details and requested_orders shown below, will it know how to relate the info? Or should I relate RequestNumber in Requested_Orders to a field in order_details?

[June7]

No, Access will not 'know' what OrderID to put into the Requested_Orders records. This is user data entry.

What is the business process?

Someone submits a Request. Someone enters into database. Someone reviews request. Someone creates an order record. Someone will have to do data entry to associate request record to order record (or order detail record), assuming this is important.