Catering Database Help

[TEDOC]

Hello Guys, First Post from a long time reader.



I am need of a little help on a database I am trying to build.

Basically it is for creating orders for a catering company.
Customer Rings in and details are made in the customer frm/tbl.
Party is then created under that customer name.
My issue is I didn't know how to do the menu or packing list tables.
I currently have them as fields in the party table, but I would rather have them starting from Beef through to Desserts - Other in a Menu table.
and then Apple Sauce through to Tongs in a packing list table. When I tried this I kept getting an error saying they couldn't be linked.

I wanted the Menu and Packing list to be sub forms in my Party Form, that when I click on a party I then get the menu and the quantities for that party in the sub form.
and the same for the packing list.

So I would click on Party 1, and I have Beef - 30 Serves, Chicken 30 serves, Tongs - 2, etc shown in the sub forms below like how the Customer Form is currently showing the party's associated with that customer.

Not sure why it would not work.

Any help would be greatly appreciated, Not sure even if my database is how it should be done. Kind of new at the database relationship thing.
Jubilee.zip

[RuralGuy]

Have you looked at any of the existing Templates that ships with Access are on line to see if there is something close to your needs the might be modified for your case? The NorthWind db that ships with Access has some similar functions in it.

[TEDOC]

I did take a look at the northwind one, when ever i changed something in it, it broke everything. so i tried to build it myself.
Just need to know how I can link the menu and packing tables, but the quantities need to change each party. Or know if it can be done.

[RuralGuy]

What you are describing sounds like a pretty standard Order(Party)/LineItem relationship. One table for Orders and another table for the LineItems.

[orange]

Before you go any further, you need to design your database (tables and relationships).

I'm attaching a link to a tutorial that will lead you through the process of taking business facts; and breaking those into meaningful subjects; assigning attributes to the subjects to identify probable tables and fields; Normalizing the tables to reduce data redundancy and potential for errors; establishing relationships between tables-- all leading to a proper database structure that supports the business facts.
http://www.rogersaccesslibrary.com/T...lationship.zip

As Allan said you should look at an existing data model to get some idea of what tables are involved and how they are related. The models are examples and do not satisfy custom requirements. They are meant as a starting point to be adjusted to meet your specific requirements.

Here is a fairly detailed model of a school catering service
http://www.databaseanswers.org/data_...nt_version.htm