Thank you. I am going to experiment with the union query (didn't even know about it).
I understand what you are saying about the similar tables and maybe my design is flawed. The reason I created 8 separate tables was because I wanted to use auto number and I was starting from "0001" each time. Now I can see where I probably should have put everything into one table and just let the department I was creating a document for take the next number, no matter what it was. Unfortunately, I now have 8 tables that using 0001, 0002, 0003, etc. --- In [email protected], "L Tumbulu" <[EMAIL PROTECTED]> wrote: > > 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] >
