Shipping Counts

[AceBK]

How to explain my issue, I will try my best. I am building an Access 2010 Database for a business. I have learned that I have to try and not duplicate information so I have three main tables. The first table is a list of the Jobs linked to customer info and contact info, I call it JobsList. The second table is for the items that the job requires, OrderForm. The third table is called Shipping. Here is what I am trying to do.

Job number 123456 has ordered 10 items#456, however, there are multiple ship to locations. I need 2 to go to location A, 3 to location B, and 5 to location C. What I would like to have happen is that as I fill out my shipping form, it knows how many of the 10 items have been assigned to a location, at the same time, it can warn me if I have shipped to many. Is there a way to keep a running tally for a specific item for a specific job as you fill out the shipping form?

I hope I explained what I am trying to accomplish. Please feel free to ask any clarification questions if needed.

Thank you in advance!

 

[jdraw]

You have described HOW you intend to do something. It still isn't clear -- in simple English terms -WHAT is the business description.
Suggestion: Don't name a Table xxxForm. Form has special meaning in Access.

You should research Normalization and Table Design.

I see Job, Location, Item, Shipping as important things to be considered.

Here is a list of steps involved in creating a database (from DatabaseAnswers.org)

These are the Steps in a Top-Down Approach :-

Define the Scope as the Area of Interest,(e.g. the HR Department in an organization).
Define the "Things of Interest",(e.g. Employees), in the Area of Interest.
Analyze the Things of Interest and identify the corresponding Tables.
Consider cases of 'Inheritance', where there are general Entities and Specific Entities.
For example, a Customer is a General Entity, and Commercial Customer and Personal Customer would be Specific Entities. If you are just starting out, I suggest that you postpone this level of analysis.
At this point, you can produce a List of Things of Interest.
Establish the relationships between the Tables.
For example, "A Customer can place many Orders", and "A Product can be purchased many times and appear in many Orders."
Determine the characteristics of each Table,(e.g. an Employee has a Date-of-Birth).
Identify the Static and Reference Data, such as Country Codes or Customer Types.
Obtain a small set of Sample Data,
e.g. "John Doe is a Maintenance Engineer and was born on 1st. August, 1965 and lives at 22 Woodland Street, New Haven.
"He is currently assigned to maintenance of the Air-Conditioning and becomes available in 4 weeks time"
Review Code or Type Data which is (more or less) constant, which can be classified as Reference Data.
For example, Currency or Country Codes. Where possible, use standard values, such as ISO Codes.
Look for 'has a' relationships. These can become Foreign Keys, or 'Parent-Child' relationships.
You need to define a Primary Key for all Tables.
For Reference Tables, use the'Code' as the Key, often with only one other field, which is the Description field.
I recommend that names of Reference Data Tables all start with 'REF_'.
For all non-Reference Data Tables, I suggest that you simply assign an Auto-increment Integer for each Primary Key.
This has some benefits, for example, it provides flexibility, and it's really the only choice for a Database supporting a Web Site.
However, it complicates life for developers, which have to use the natural key to join on, as well as the 'surrogate' key.
It also makes it possible to postpone a thorough analysis of what the actual Primary Key should be. Which means, of course, that it often never gets done.
Confirm the first draft of the Database design against the Sample Data.
Review the Business Rules with Users,(if you can find any Users).
Obtain from the Users some representative enquiries for the Database,
e.g. "How many Maintenance Engineers do we have on staff coming available in the next 4 weeks ?"
Review the Results of Steps 1) to 9) with appropriate people, such as Users, Managers,
Development staff, etc. and repeat until the final Database design is reached.
Define User Scenarios and step through them with some sample data to check that that Database supports the required functionality.

 

[AceBK]

Thank you for the quick reply. I knew that Access has special words, I didn't realize that you couldn't expand on those words to change them. I will change the name. Thanks for the tip. As for the design and stuff, I have built some databases already and have learned quite a few things about designing tables, mostly with advice from this blog, so thank you for adding that part to your response, it was also very helpful.

As for the issue that I originally posted about, the business is your typical manufacturing business. A customer orders a bunch of different items, this database will be designed to take in that information, put a work order together, as well as a shipping docket. The challenge is that the one item might get sent to multiple locations. I am looking for a way to track that the full quantity of that item is being shipped, specifically that only that quantity is being shipped, not more than the ordered amount.

 

[jdraw]

Sounds like

A Customer can make 1 or many Orders
An Order can contain 1 or many OrderDetails
An OrderDetail represents 1 Item (any Quantity and SellingPrice)
A Customer may exist in 1 or many Locations
An Order may be shipped to 1 or many Locations

 

[AceBK]

Correct, that is a fair synopsis. I have all of my one to many relationships in place already.

 

[jdraw]

Can you post a jpg of your relationships window (screen capture). Make sure tables are expanded so we cn see the fields.


What does this really mean

 The challenge is that the one item might get sent to multiple locations.

Perhaps you should be working with Orders and PartialOrders???