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.
