Multiple Table for Multiple Reports

[isaidi]

Consider this scenerio

A List of equipment uniqely identified by EquipmentID

List of Reports that must be completed for every equipment. Kind of like a QC Checklist for each equipment. But there are different kinds for reports A, B,C,D,E...
Each equipment may have only one Report of the same type associated with it, but multiple reports of different types.
for example EquipID-324 can have Reports A-324, B-324 etc..
Notice the report is unqily identified by A-324 (the number of the equipment it was completed for).

Now each report has different information, data types and field names... so assume that the fields pretaining to report A are independent of the fields in report B and so on.. they have different data types and field names they canot be grouped or categorized.



I have originally propesed designing the following

tblEquipmentList
-------------
equip ID
... other info relating to equip


tblReportA
--------
Equip ID - uniqe ID in this table.

tblReportB
-------------
Equip ID - unique ID in this table


tblReportC, tblReportD...and so on....


Note that the same equipment canot be repeated in each table, therefore equipID-321 in report-A would only be there once, since you cannot complete the same report type twice for the same equipment.




After this point i am stuck.. becuase i read that this is not recommended to have multiple tables in that fashion. Becuase if a new report is added in the future, i would have to add another table... i would like to make the database easy to add new reports...

I was thinking of maintaining a list of Reports
tblReports
------------
ReportName
ReportID
Description
ReportTableName

this way I am linking the table names to individual records in tblReports... is this good practice? if not, how can i avoid it.. there wuold be alot of code involved to achive this features of a record refering to a table??




Note, i have read the fundemantals...

 

[isaidi]

One more note.. when i say report.. i dont mean an Access Report... it is a physical hard-copy report that is used as a checklist for that equipment.. when completed it must be entered into the database via a FORM..

 

[SkipVought]

Hi,

All that you are talking about is the SAME query form the SAME table (tblEquipmentList) with a DIFFERENT criteria VALUE (Equip ID=[What Equip ID?])

That value can be supplied by entering a value, from a drop down, or even in a loop like (psuedocode)...

for each eid in [Equipment Id List]
  Run the query for eid
  Print the results
next

Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[isaidi]

Baisically anytime i have to lookup information in these tables, i would use my lookup table to get the table name and pass that name as an argument to the query.

it's not alot of code, but i can't predict what else might come up... i am just starting with the table design right now, i feel that storing my own table defition list which i use for lookup will cuase more problems... ...
however it seems like i have exhuasted all other solution.. i may have to resort to this.

Thanks for the post.