Creating a new Orders Database - Require help with ordering system

[snedie]

So my current employer has tasked me with creating a new database that we can use to process all of our orders. Pretty simple stuff but I can't for the life of me get past one hurdle.

Simple things first:

- Database is for processing turkey orders, two types of turkeys can be purchased. Each type must have it's own unique numbering system.


- One table with all the customer Information (Name, address, telephone etc etc)
- One table for "Type 1 Turkeys"
- One table for "Type 2 Turkeys"
- Relationship from "Customers" to "Type" 1 using "Customer ID" as unique identifier.

We need a single form that can be used to see all of the customer information, as well as create an order for either/both type(s) of turkey. My problem is this; I can create a relationship between "customers" and "type 1" and have a form showing all of the customer information and a table allowing for the creation of "type 1" orders, but cannot find a way to have a second table allowing for the creation of "type 2" orders. If I create a second relationship between Customers and Type 2 I am forced to choose which I want to be displayed.

Any help is greatly appreciated

[edit]

A screen shot of the current database. The current relationships just allow for a drop down selection under each orders table for selecting products:

[June7]

What do you mean by 'unique numbering system'?

Are fields of the two tables identical?

I recommend one table with another field for the 'type'.

Why are table names including year? If you intend to create a new table for each year - bad idea.

Have you looked at Access database templates? The Northwind or Sales Pipeline might suit your requirements.

[snedie]

What do you mean by 'unique numbering system'?

Just Auto Number, except that the "Crown Orders 2012" table will start at 1000 and work upwards and the "Orders 2012" table will start from 0 and work upwards.

Are fields of the two tables identical?

Yes

I recommend one table with another field for the 'type'.

Due to frequent processing errors by staff we need a secure method of differentiating between standard birds and crowns. Having them start at a higher number simply makes this idiot proof, and saves me having to spend already stretched time each day tracking down where orders have gone wrong. However I think this is going to be the easiest method and when I speak to the boss tomorrow I'll discuss physical methods of marking each bird to denote it as a crown.

Why are table names including year? If you intend to create a new table for each year - bad idea.

I would simply remove the relationships between the customer table and the order tables, delete the order tables (to reset the auto number counter), recreate them and recreate the relationships.


Another problem I can't find the answer to is having the table/form auto fill in data depending upon a choice made in another cell:

- In "orders" there are columns "Required Weight" and "Long Legged Weight". When we select the required weight it would be desirable if the Long Legged Weight could be selected automatically, simply to save time and avoid human error.

[June7]

0 to 999 would be enough numbers for Orders 2012? Are you aware that autonumber does not prevent gaps in sequence? Numbers can be 'lost' due to canceled data entry.

"I would simply remove the relationships between the customer table and the order tables, delete the order tables (to reset the auto number counter), recreate them and recreate the relationships."

Why - you don't want to save data from year to year?

The Long Legged Weight could be retrieved by several methods. All are complicated by or impossible due to the two-table structure. Review this thread that describes the methods in post4 https://www.accessforums.net/queries...eld-29272.html

[snedie]

Just reworked the database to have just a single orders table with a "Type" field and product selection (actually just a list of weights) which clearly differentiates the types. I'll see what the boss thinks to it thanks for your help bud.

[ninja edit]

We keep all our records as a physical medium, this is the first year we would be using a database. I realised after posting that I could simply remove the relationships from the order tables and keep them for future reference

[snedie]

0 to 999 would be enough numbers for Orders 2012? Are you aware that autonumber does not prevent gaps in sequence? Numbers can be 'lost' due to canceled data entry.

Gaps in sequence will probably cause confusion when it comes to confirming every order is there however this is something we can work around for this year: Unless there is a workaround?

And yes 0-999 is plenty for the foreseeable future, our public orders are less than 500 currently.

[June7]

Workarounds:

1. unbound form for data entry

2. don't allow users to cancel data entry - the record must be completed - or have a 'void number' procedure, a record would be saved but with a 'Voided' entry

Now with one table the designation of number series for turkey type is irrelevant.

[snedie]

Workarounds:

1. unbound form for data entry

2. don't allow users to cancel data entry - the record must be completed - or have a 'void number' procedure, a record would be saved but with a 'Voided' entry

Now with one table the designation of number series for turkey type is irrelevant.

Many thanks for your help. If the AutoNumber assignments prove to be to much of a hassle I will simply go back to how we normally do things and manually enter them. It's only 50 or so orders each day, and once I repopulate the Customers table the following year will be very quick.