differentiating product lines with tabs

[EvilSewingMachine]

I have 6 product lines, each product line has a high level of
configurability. Using tabs you configure each of the product lines.
Should each product line be stored in its' own table or one table with
all product configuration fields combined. Can you have a Parent Table
with one to many relationships going to 6 Child tables from the same
PK? If not, then how do I differentiate the product records later?

 

[Allen Browne]

If practical, put all 6 in the one table, with a ProductTypeID field that
distinguishes them. It's okay to have several fields that will be used by
one of the types only.

If you have myriads of fields that are different for the different types of
products, you still want a Product table, but it will have very few fields.
Then you will have 6 further tables related to the product, each one with
the fields relevant to that type of product. For more info, search for
subclassing at http://groups.google.com/advanced_group_search?hl=en in the
groups:
microsoft.public.access.*
comp.databases.ms-access

 

[EvilSewingMachine]

Really appreciate your help. ok, no problem putting all product lines
in one table. Having a ProductTypeID would work, but would I get blank
records in my "configurator" tabs. If I'm at record 3 on the "mugs"
tab, but record 3 was entered in on the "staplers" tab, record 3 in
the "mugs" tab would be blank. Can I filter out the blank records when
I'm in each tab and still have the ability to add new orders? What is
subclassing? I'm inexperienced in Access, thank you for your patience.

 

[Allen Browne]

You have a form with 6 tabs, so you can show each product type on a
different tab? Presumably you have a subform in each tab page.

If so, create 6 queries.
Enter the criteria in the query so the right product type shows up.
Use these queries as the RecordSource property of your subforms.

 

[EvilSewingMachine]

That works beautifully, Thanks for you help. The concept is simple,
putting it to use is the hard part.
-Noah

 

[EvilSewingMachine]

I thought it was working great, once I shut Access down and came back
to it later, the products I had configured would not show up in the
product configuration subforms. They were in the tables and showed up
in the query but not the forms. what could be going on?

 

[Allen Browne]

You have all products in *one* Product table, with a ProductTypeID (or
CategoryID or whatever) field to distinguish the different types.

You have saved 6 queries - each with criteria to display one product types.

You have a form with a tab control that has 6 pages.
Each page contains a different subform.

If a subform is not showing the correct data:
1. Open the subform in design view (directly from the Database window.)

2. Set its Row Source property to the correct query.
Test that is shows the right products. Save. Close.

3. Repeat Step 2 for the other subforms.

 

[EvilSewingMachine]

Got it, You're instructions were clear, thank you. Access was having a
hard time knowing which records to relate to from the 6 queries
because the product tabs are a subform in a subform controlled by a
subform on the main form. I had to put an "ActiveJobID" field that
references the Primary Key from the control subform on the main form
and then to the other forms to tie them all together. It works great
now, thanks Allen.
Noah