Multiple one to many relationships

[Iain]

I am completely new to database design and don't even know how to go about asking the right questions, but here goes.

If I have a database with 4 tables 1, 2, 3 & 4, is it possible for these tables to be sequentially linked in one to many relationships? i.e. 1 to 2, 2 to 3 etc

If I wanted to create a record in table 4 that points to related fields in tables 1, 2 & 3 is this also possible?

[June7]

Consider:

tblCustomers
CustID (PK)

tblProducts
ProductID (PK)

tblOrders
OrderID (PK)
CustID (FK)

tblOrderDetails
OrderID (FK)
ProductID (FK)

Each customer can have many orders and each order can have many details and each detail would be a record that selects a product.

[Iain]

Thanks.
Have I followed the correct priciples with the attached database? (Hope I've attached it correctly!!)

There are limited categories of hills.
Each category can exist in several sections.
Each section can can contain several hills.
Each walk can have several hills, entered in order to give a rough description of a route.

Problems to be catered for with the data is that regional sections are not the same for each hill category and there are several hills of the same name.
Because of the spelling difficulties with gaelic names, when adding new walks it was intended to filter first by the hill category, then by section, then select hill name: using combo boxes to prevent user spelling errors.
The only new user input would therefore be the walk details.

Have hit a major problem in creating a new walk record.

[June7]

A form cannot have itself as a subform.

I don't understand why you want subform. Enter new record on the 'main' form. The 'Add New' and 'Edit Existing' tabs are useless and redundant. Just navigate to existing or new blank record and enter/edit data in the fields above the tab form. The entire tab form is not helpful. Not that many controls to organize.

Hill Category and Region should not be on this form. Those are attributes of the hill, not the walk.

There is also an error message regarding a macro execution. I did not try to track it down.

[Iain]

Thank you.

I don't understand why you want subform.

I don't necessarily want a subform. Don't even know what they are other than what others have suggested. I had misunderstood what functionality a tab control could offer. This is now removed but I'll keep the edited form for now.

Enter new record on the 'main' form.

Before a hill can be entered, I was trying to give the user a means of finding the correct hill using 3 combo boxes but cannot get the form to filter correctly(frmEditHills) or clear old data from the boxes before selections are made.
i.e select HillCategory limits the choice of Hill Sections which in turn lists the hills in that particular Section.

I've changed the relationships to make Category and Section attributes of Hills. Have I understood you correctly here?

[June7]

Shouldn't the hill boxes on frmNewWalk be comboboxes?

Here is a tutorial on dependent comboboxes http://datapigtechnologies.com/flash...combobox2.html
In your case, only the HillID comboboxes would be bound to field. Any comboboxes used as criteria to restrict the hill choices would be unbound. On frmNewWalk, the textboxes displaying related hill info should be Locked and TabStop No to prevent editing of those fields.

I just realized your first db had HillCategory and SectionCategory. Do you want separate category descriptions for hill and section?

The structure of 5 fields for hills in tblWalks is not normalized. If you want to view the related hill info for each this will mean joining tblHills to tblWalks 5 times, once on each field and each of these tblHills joins will require their own joins to tblCategory and tblSections. Right now the 5 boxes for HillHeight are bound to the same field from qryWalks. This means all 5 hills will display the same height value which is the value for first hill.

Still get the macro error when opening forms. I don't use macros, only VBA.

What is frmHillLog for? It is bound to Hills data but the controls on form are for walks.

Here is a tutorial on form/subform arrangement http://office.microsoft.com/en-us/ac...010098674.aspx

[Iain]

Wow! You have given me a lot to think about and I don't really have any answers at this stage.

I'll give your comments careful consideration and take in the tutorials before I reply, so this may take some time.

Just as a minor point, I don't get any macro errors when opening forms, so can't offer any explanation.

I dare say I'll get back to you on these points as there is a lot I'm confused about.

Thank you for all your comments they are most helpfull in highlighting the weaknesses in my efforts.

Rgds