Table layout for multiple product types

[stonward]

Help! Please....it was all going so well, but then I had to add some new product types to my database design...

The (vast) majority of my system is based around the stock control/sales etc of TYRES. There are a number of different CATEGORIES of tyres, but all the fields for the types are naturally the same. Now I have to add batteries and an additional few SERVICES (punctures, tyre-removal, tyre-fitment) to the system.

I simply created a batteriesProduc t table and a nonTyre product table, but this hasn't worked when I tried to update my point of sale and purchase forms.

I think the problem is the table layout. 'FishVal' has tried to help by advising adding small 'joining'(?) tables, but I can't quite grasp his concept. Can anyone help? This has really got me stumped this time!

Thanks Guys,

StonwardR :-(

[nico5038]

One general approach is to have one "Master" producttable holding the general information like [ProductCode], [Price], [QtyOnHand], [VAT%], etc. and finally [ProductType].
Each producttype will have a linked table holding the [ProductCode] for linking and the additional fields that are specific for that ProductType, like Ampere for batteries, wheelsize for tyres, etc.

Getting this idea ?

Nic;o)

[stonward]

Sorry Nico, I've only just seen this reply by your good self.

I've been digging deeply into this problem and have found other soultions that are much the same as yours (known as subclassing the table entities!). I tried it but it made making quotation/sales/purchasing forms very difficult to figure.

However, this is still a problem I have. Currently I've gone back to lumping them all together in one table and simply putting up with not using many fields in some instances.

It is necessary, you see, to be able to have (say) one of each product type on ONE invoice or quotation. This is a swine to work out!!

Any further help is gratefully and desperately grasped at!!

Stonward.

[nico5038]

Sorry Nico, I've only just seen this reply by your good self.

I've been digging deeply into this problem and have found other soultions that are much the same as yours (known as subclassing the table entities!). I tried it but it made making quotation/sales/purchasing forms very difficult to figure.

However, this is still a problem I have. Currently I've gone back to lumping them all together in one table and simply putting up with not using many fields in some instances.

It is necessary, you see, to be able to have (say) one of each product type on ONE invoice or quotation. This is a swine to work out!!

Any further help is gratefully and desperately grasped at!!

Stonward.

Having a "Master" table and ProductType "subclassed " tables, isn't hard on your forms, as long as you use a tabbed control per "subclass" table. The tab will then hold the ProductType and the subform is linked to the "Master" table by the unique MasterProductID .
The "Master" tablefields will show above the tabcontrol and are always visible for the user.

On a report you'll need to use per ProductType a different subreport, but by first sorting them on producttype this can be arranged in a decent way too.

When you use the "Mastertabl e with empty fields" solution, you'll still be able to use the tabcontrol to group the fields and only show the tab that's related to the ProductType of the Masterrecord. (Just make all other tabs invisible)

Grasping the "tab control approach" ?

Nic;o)

[stonward]

Nic;o

Think I've got it...

The Main Form will use the main Product table for its source, and the form will have tab controls on, each of which will use a Product 'sub' table for its source...yes?

Gonna try it about now...

I'm seriously grateful for your help...I think this will be the last major hurdle of this (updated) system!

Thanks again.

Stonward.

[nico5038]

Keep me posted and don't hesitate to report here when you get stuck.

Success !

Nic;o)

[stonward]

NICO!!!! Are you out there?! Help - pleeeease.

Havin' a swine of a time trying to get table relationships right to allow for three separate tables of 'product'. Do you remember helping b4? Can you help now?

Thanks

StonwardR

[stonward]

Multiple product types

Hi guys.

I'm (still) having a nightmare with arranging my tables...I have three very different product types, so I have tried creating three product tables linked by a productType table. But then I have no way of correctly linking to the sales and purchasing tables. I know this is known as subclassing, but (as yet) I can find no combination that will work!

Can anyone help with this?

StonwardR

[Motoma]

Hi guys.

I'm (still) having a nightmare with arranging my tables...I have three very different product types, so I have tried creating three product tables linked by a productType table. But then I have no way of correctly linking to the sales and purchasing tables. I know this is known as subclassing, but (as yet) I can find no combination that will work!

Can anyone help with this?

StonwardR

Hi stoneward,
You mistakenly posted your question to the Access Articles area of our site. I have bumped this thread to the Access Forum as I assume you will receive a greater response there.
Motoma

[nico5038]

Can you describe the tables as they are now ?

Nic;o)

[nico5038]

The basics would be to link the sales to the tblProductMain.
When detail info of the "subs" is needed you need to use an "OUTER" join.
Just link by the tblProductMain unique ID and click the connectionline to select option 2 or 3 to make the tblProductMain the "master" table.

Nic;o)

[stonward]

Hi Nic;0,

I tried what you said, but can't seem to get the links right...I made a ProductType table to go with my 3 'products' tables, but i get an indeterminate relationship each time I try to link the sales/purchase details to the ProductType table.

[stonward]

Oops! Sorry motoma....Thing s appear to have changed a little here at the Scripts...

Stonward

[stonward]

Hi Nic;o,

Sorry, but I seem to be talking 'at' you from two different posts!

I am currently trying two avenues, one with three separate 'product' tables linked to a ProductType 'main' table. The other way is to go back and try and put all my products into one table. Both I can't seem to get right. Um, wots the easiest way to explain my table layouts?

StonwardR

[NeoPa]

If you have three types of products then you need a table (tblProductMain ) with a field in it to indicate the type.