John,
If needed you can have a big table in Access, after all it provides for 255 fields. I aslo use a very large main table but it is not large because of lots of multiple instances of the same data.
If you do it all in one table then it will in fact be more complicated not less complicated. Let me illustrate with a very simple example, often found in personal contact data bases.
Among the details we wish to keep are the names, ages, gender, school or whatever of the children in each familiy. There are two ways to go in one table.
Let's say John Andrews has 3 kids. We make three records for John Andrews, all the same except for the details of each child. We will need to enter John Andrews, his address etc 3 times. If he changes his phone number etc.we need to change in in 3 places and if he has 10 kids then we need to do it in 10 places. If there were two tables, one with the children's details we could make a query that would join the two tables and duplicate the 3 entries (or 10 or 100) in one table but we would not have to enter the data more than once.
The second way is to give John Andrews one record and allow several fields for different children, same principle as you are proposing. This solves the problem of any details being changed. But how many fields do we allow for entering childrens data.
Next problem. We are going to need fields for the children like Name1, Age1, Gender1...Name2, Age2, Gender2 etc and onto perhaps name10, Age10 and Gender10
Now lets say we want to know how many children there are in our data base who are between 8 and 10 years of age, who are girls etc. That would be one huge job if they are all on one row. How many of the fields would you search. Then the problem that each set of child's data has to have a different field name.
If I had a table of doors and a table of attachments, could I have fields within the attachments table that stated quantities for that particular door, if this is a yes do I need a third intermedairy table with unique id's for both door and attachment? If this is yes how do I display the list of attachments so I can choose quantities for each door (hope that makes sense).
Yes, you can have what you like. At one extreme there is one record with say a 6 in a field and at the other end there are 6 records for attachments. The attachmenst record (s) do not have unique IDs. They have the same ID as the Door.
As to choosing the quanity from a list that is now getting into macros, code. For example, if 6 of attachment A are required then Attachment A could be selected from a drop down list and if 6 is the quantity then a simple click would either create 6 records of each attachment, inset the Door ID number or if it was done as a single record then the macro/code would stick a 6 in the appropriate field and insert the Door ID number.
You said you have 75 different attachment types. What happens if for some reason you come to need to 3 fields to describe each attachment. That will then burn up 225 of the possible 255 fields.
