It sounds like you need to structure your data a bit differently you can do the basics of this with 4 tables
Lets look at it like this
1 Each unique (by dimentsions and characteristics) Glass Inventory Item has an ID (One table of Glass Items with characteristic fields and a unique ID) Table has one record per Item
2 Each machine has its own id (One table with Machine MFG. Info and ID) Table has one record per Machine
3 Each machine has a number of Cab Postitions (One table with the MachineID and a number of Cab Positions) Table has one record per Machine/Position Combination
4 Each Position may have one or more glass items that fit it (tint nontint etc quality etc) with MFG ID Position ID and Glass ID that fits Table has one record for each MFG/Position/GlassID that fits. (if there is only one glass item that fits the position then you can dispense with this table and add the item ID fields to the positions table but I suspect you might have 2-3 product types that fit the same space)
You can use relationships and portals to make the data entry and display for each of these easier in a variety of ways.
Instead of having 10 or more fields on the Cab table you would have a related CabPositions table that listed the cab postions.
Very close The second table should be something like rather than seperate fields
MACHINEID POSITION POSITIONID(unseen)
Machine1 1 1
Machine1 2 2
Machine1 3 3
Machine2 1 4
The third table would keep the POSITIONID
We do this because each table should have a unique serial id regardless of the data in the table.
Hey, Thanks again for your reply.
I have to admit that I still cannot grasp this. If you take a look at this, could you tell me what would be wrong with it? I have a feeling that it's not a good thing for the Cabs table to have several fields that contain similar data. Is that where the problem is?
One other thing, even though one cab may potentially have 3 Windscreens for example, we will streamline the items in the cab cab to a minimum number.
Correct there is a better structure
Lets for the moment add 2 tables CAB_POSITIONS
The CAB_POSITIONS will create a POSITION_ID for each place you could place a glass on the Machine
It will have the fields
MACHINE_ID this is the same as your machines table MACHINE_ID
POSITION_ID this is an auto entered serial number unique to each record
MACHINE_POS this is simply a number that used for a human to understand the machines layout
Now the POSITION ITEMS will have the following fields
POSITIONITEM_ID this is an auto enter serial number unique to each record
ITEM_ID this will be the an ITEM_ID that comes from your ITEMS table.
Now this structure is better suited to what you want to do.
Are you planning this to be multi-user?
Thank you again for you reply. After getting my head back into this project I think I have grasped the concept of what you are saying. Woul this work better?
Machine Table Make Model Type CabIDfk JCB 801 Excavator ECAB001 John Deere 7700 Tractor TCAB002 Excavator Cabs Table CabID Windscreenfk Lower Windscreenfk Left Upper Doorfk Left Lower Doorfk Right Front Quarterfk Right Rear Quarterfk Backlightfk ECAB001 ECAB001_Win ECAB001_Lwin ECAB001_LD ECAB001_LD ECAB001_FQ ECAB001_RQ ECAB001_B Tractor Cabs Table CabID Windscreenfk Left Lower Windscreenfk Right Lower Windscreenfk Left Doorfk Right Doorfk Left Rear Quarterfk Right Rear Quarterfk Backlightfk TCAB001 TCAB001_Win TCAB001_LLW TCAB001_RLW TCAB001_LD TCAB001_RD TCAB001_LRQ TCAB001_RRQ TCAB001_B ECAB1 Position ID Item Code 1fk Item Code 2fk Item Code 3fk Item Code 4fk Item Code 5fk ECAB001_Win P10001 P20789 P62236 ECAB001_LWin P98661 P84775 ECAB001_LUD P12334 ECAB001_LLD P67789 P45667 ECAB001_FQ P34567 P67899 ECAB001_RQ P23445 ECAB001_B P30098 TCAB1 Position ID Item Code 1fk Item Code 2fk Item Code 3fk Item Code 4fk Item Code 5fk ECAB001_Win A13665 A45669 A89662 A89663 ECAB001_LLW A10896 ECAB001_LD A56998 A45338 ECAB001_RD A10896 A71023 A456998 ECAB001_LRQ A10655 P67899 ECAB001_RRQ A96336 A45996 ECAB001_B A10994
Would anyone else be able to help me with this? Thanks
I've been hoping Aammondd would respond, but he hasn't been active recently here...
I haven't looked over every single detail of this thread, but your latest post looks much better than the previous one. With the previous design, you could run into trouble should someone produce a redesigned cab with different glass locations as that might require adding new fields to your table and layouts to accomodate the change.
Firstly, I have had some advice to split 'Make', 'Type' and 'Model Group' into seperate tables and I think this is a good idea. I think this is a good idea incase we ever need to store information e.g. notes, images regarding a make. Also, if a customer only ever purchased glass for say a jcb it may be easier to filter only JCB glass at this level. Or if we ever did a campaign for jcb glass, we could filter out all the jcb glass from here. I suspect it may be quicker to search if these entities are in their own table. Am I correct about this or am I talking rubbish?
With regard to linking the Models to the Cabs (groups of glass), can you see anything drastically wrong with doing it this way? (below) I am thinking of not doing it the above way because in order to create a record for every cab position and then referece it to one or more items in the position items table will take forever (we have over 5000 pieces of glass). I aim to end up with just one glass item per postion and take care of any variations of say windscreens in the mode group table (for example, if the windscreen changes in 1999, then this will be a seperate model group and therefore will have only one windscreen associated with it)
All looks very logical and straight forward to me. I can trace both the logic and relaitonships from table to table in the above image and they appear a text book example of implementing one to many and many to many relationships.