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 batteriesProduct 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 "Mastertable 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]

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....Things 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.

[stonward]

Hi NeoPa,

Yep, I think i get that, but how do i link my sales/purchasing tables - thru the productType table, or direct to the separate product tables? If to the 'main' product table, then what field can I use?

Thanx for your help - this prob is really giving me jip now!

StonwardR

[nico5038]

The ProductType can be a field in the "main" producttable.
This ProductType can be used to determine which fields are available of the linked tables.
Linking main with sub is done with the unique ID of the main table. Each sub will hold the main ID and it's own ID.

Clearer?

Nic;o)

[stonward]

Hi Nic;O,

Yep i get, and have done that - but I have problems linking the Purchase/Sale Details table(s)...If I use the ProductTypeID, that doesn't tell me WHAT product is being sold/bought....See my prob?

StonwardR

[NeoPa]

Hi NeoPa,

Yep, I think i get that, but how do i link my sales/purchasing tables - thru the productType table, or direct to the separate product tables? If to the 'main' product table, then what field can I use?

Thanx for your help - this prob is really giving me jip now!

StonwardR

The main point that I was trying to get across is that there are NO separate product tables. There is no need to separate the items into different tables. Simply include them all in the same table and include an extra field to flag each record as one of the three different types.

As far as linking into (or from) the sales and purchasing tables is concerned, I expect they would want to link to the Product code in the main (single) Product table.

[stonward]

I see,...got 2 remember all what I have tried now. I originally used that very idea, using ONE products table, but with a Categories (type) field. The problem I have is when I have more than one type of product in one sale (on one invoice)...but I'm gonna go back and try it again now.

Thanx for your help.

R

[NeoPa]

If you have multiple products on an order (sales or purchase) then it is necessary to have an Order Header table as well as a linked Order Details table. Each product would go on an Order Detail record. If you try to bundle them all together by having multiple products per Order record you will certainly have problems as the structure of your concept does NOT match the structure of your situation.
Have a look at Normalisation and Table Structures.
This may be a lot to take on for you now - but with it you will avoid many future issue. Without it I'm afraid the reverse is true. Expect various problems to occur unless you have this basic understanding of how to organise your data.

[stonward]

Of course...

I've had that down for some time now (although I don't yet grasp SELF joins!)...and I have Sales/Purchase DETAILS tables because of the many
:many join that would otherwise occur.

This problem arose when the guy who needs this database suddenly required the ability to 'sell' more than one type of product at one time, in addition to adding some services to the product range...

I've got a couple of new ideas to try,...I'll get the details straight in my mind, then come back to you with an accurate explanation of the problem.

Thanks, Guys!

R

(I promise it's nothing so very simple tho...!)

[NeoPa]

That's fine R.
We have very little clue as to how advanced a question (or questionner) is on here. We get the full range of course. I wasn't trying to imply you were less advanced than you clearly are :)