Best way to describe the problem is from the top. Am trying to develop a DB to record various activities undertaken by certain suppliers (unlikely to exceed 10 suppliers in any single instance of the DB). For each supplier the activities can be normalised into 4 related tables with increasing granularity ie 6 sections, 22 subsections, 83 taskgroups and 585 tasks. I have therefore created these four tables and identified them as 'proto tables'. The problem I have is that I want to replicate this table cascade into a set of 'user tables' but with one more level above ie the supplier level. For example if there were three registered suppliers then the 'user sections' table would have 18 records, the 'user subsections' table 66 records etc.
I got as far as replicating the top ('sections') level with an append query but because the 'proto tables' do not have a link to supplier, this same technique cannot be used to create the records in the lower categories. I realise that I can write code to append records by looping through each supplier but I suspect that this would be a rather slow process and wondered if anyone had other suggestions. Any thoughts would be appreciated.
tables can hold millions of records without a problem. your core 4 tables may be ok - can't really comment on that....but just put in all the records into a common set of tables plus of course include an IDfield or supplierName field so that one can differentiate each record by the correct supplier.
you will be easily able to segregate the data by supplier just as if they were in separate tables anytime you need...
Apologies - long session and posted late into the night. What I should have referred to is replication of records rather than tables. Each individual section, subsection, taskgroup and task is a record within its respective table. Each record has certain standard information as well as fields for aditional user input. Because the standard information of a particular record is the same for all suppliers, I have created standard records in the 'proto tables' which can be copied to the 'user tables' as and when a new supplier is added. I anticipate creating a new DB for each project based on the same model of tables. Within each project there may be one or more suppliers. At the start of the project I don't know how many suppliers there will be although it is unlikely that the number will be greater than ten.
Now I could create the four tables with 10x6 section records, 10x22 subsection records etc but this goes against the grain and there is always the remote possibility that some project might exceed the 10 supplier limit.
If you create separate dbs for each project; then getting summary data overall will be difficult if not impossible. Just something to keep in mind.
Generically speaking: any data that is truly non changing & repeating - could be put in its own table once and joined in when needed for display. The classic analogy is of course the Part description; where one only pulls that in when needed to be seen and otherwise just use the brief PartID in the sales/transaction table.
It is not uncommon to want to 'roll forward' data from an existing set of records into a new set of records so that the human only needs to change some data rather than re-enter it all from scratch. What is to be the user experience has alot to do with the best method to implement. One can implement the idea via vb at the form level or you can implement with sql appendquery if more a batch situation.