Anne If I understand the question correctly, I find having 8 tables unnecessary. If the data in the 8 tables are similar then they can be put in one table, the difference being the document type. For example you can have a table of departments and their codes and then another table for the data in your 8 tables.
Alternatively as the case is now you can use a Union query to combine data from all the 8 tables and base your report on this query. HTH Liveson ----- Original Message ----- From: Anne W. To: [email protected] Sent: Wednesday, 05 March, 2008 10:17 PM Subject: [ms_access] Relationship between several similar tables to create report This is making me crazy and I cannot seem to find the answer anywhere: I have about 8 tables that are similar. Each one contains a document number and a document title. The document number consists of a code that stands for a department and the type of document (e.g., 089F might stand for Purchasing department forms). That is obviously a text field. The next field is an auto number that starts with one. So the first record in the table might be 089F0001, Purchasing Requisition and this table would be Purchasing Forms. The 7 other tables are similar, but might be something like 079F0001, Engineering Fax, then 079F0002, title, etc., and this would be Engineering Forms. I use these tables with a couple of others where I have a one to many relationship. I have no problem as long as I'm only using one table at a time. HOWEVER, I now have a table called "Retention Records" and I want to create a report that shows all 8 of the Forms tables with their retention information. The Retention Records table uses the document number (089F0001, 2, 3..., 079F0001, etc.) Of course, I have to use two fields: a text and a long integer field to make the relationship work. I can create a relationship from one form table and create a query and a report, but I cannot figure out how to add the other 7 tables. I'm not a programmer! Can anyone give me a fairly simple solution? Thanks, Anne W. [Non-text portions of this message have been removed]
