Discussion Locked

same fields from diff tables in Access

0 helpful
11 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have a databse with our computer, monitors, printers and misc equipmet in tables with the primary key being the Asset number assigned. What I want to do is to create a report that will list the assets. I want the report to list the assett numberand serial number, model, Description and where it is located. These fields are in all of my tables how can I combine them in a report. I tryed a query but all the fields from the different atbales are all on the same line.

11 replies

by sbhana

All the tables that you want in your report should have a field in common. In this way you can link all tables using this common field. Give us more information on the tables you have and the field of each table.

by dennisbv

The tables are tblComputer, tblPrinters, tblMonitors, tblMiscEquipment. The primary key in each one is the Asset Number.
The fields I want on the report are in all the tables and are Asset number, Description, serial, model, Location,Notes .

by dennisbv

I forgot on my response to answer one, The only field that could have the same information in is the Location field.

by dr. dale

If I understand the problem correctly, you have several tables with exactly the same structure. The only thing different is the information each table is tracking (e.g., printer, monitor, etc.). My solution would be to create a temporary table that merges all the data, and then base a report on this temporary table. To create the table, use append queries for each of the existing tables, and append all the information into the new table. Since the fields are the same in each table, this should be easy. Then create your report.

by dennisbv

For answer two
I had thought of that but I don't want to do that if there is another way. The computer table has detail on the computer that the other table don't have. Thats why I went to separate tables for the different Items

by ae_maillist

Create a new table with the same columns as in ther original tables. Add a new field type.
Type can be 1 for computers, 2 for printers and so on...
Then import all the other tables into the new table.

Anders

This discussion is closed to new replies.