FishVal,
Thanks for your reply....
Your example still has me a little baffled - I can see what you're doing, breaking down the data even further, but I can't see how this will help me, say, make a point of sale form/subform or the queries it'll be based on. Here are my product table details:
tbl_Tyres;
ProductID - PK (auto)
Handle
Category
Series
Size
Rating
Manufacturer
Notes
CostEach
Trade
Retail
Diameter
Width
Stocklevel
'TYRES' makes up 98% or more of the use of the system...
Tbl_Batteries
ProductID - PK (auto)
Category (always '16' - batteries)
Reference (Manufacturers Ref No)
CostEach
Retail
Notes
StockLevel
tbl_NonTyreProducts
ID - PK (auto)
Category
Product
UnitCost
TradePrice
RetailPrice
Notes
I can see how I could easily narrow it to two Products tables, but I can't see how this would help? I can also see how i can make a form with a combo (say) that when a cetain category is selected, the fields alter to suit. My problem is mainly with building the queries/SQL strings necessary....they come up 'empty' until I remove (say) the batteries table...Help Please!
Stonward the Weeping
Hi, Stonward.
To my mind, you need to make a 4th table (let us say [tblProducts]) where the following fields supposed to be ProductID, Category, <all fields needed for "SalesForm" proper calcilations (price etc.)>.
The rest is your decision:
1) you are going with the solution from post#3 having a flexible db ready to deal with any product category / fields combination
2) you add FK associated with the tblProducts.ProductID to your 3 tables each dedicated to particular product category
Anyway this is a very general issue - the internal ideology of your db.
Without understanding your particular situation I can easily suggest you a not optimal or completely unsuitable tables relationship schema.
You should better than any other understand what functionality your db should have to fit all present and possible requirements. So to make my advices more useful you have to describe the whole situation in a very detailed manner.
P.S. The structure of tables you've provided seems me somewhat useless so far. Did you mean each product has a constant price, size etc?