Multiple Tables into Reports with same Formatting

[rezanaghibi]

Hello

I am creating an inventory for artwork owned by my company. I have created a Table for each building (There are 11 buildings). From each, I would like to make a Report, to then print out and/or export to PDF. Trouble is, once I have the Report in design view, and I am happy with the formatting for one building, copying and pasting that Report layout for the next building works but the data from the new building does not plug into the pasted in fields.

So far, I have been redoing the process for each Report. I am hoping that there is a solution. Thanks in advance!

[ItsMe]

When you look at the 11 tables in design view, are they all similar? Does one table contain field names that are not in another table?

[rezanaghibi]

Hey there, the items in each table are the exact same.

[nick404]

When you copy/past the new report you have to set the table with the building you want that report for as the Control Source for the report.

[rezanaghibi]

Right. That has worked for 1 or 2 fields, but the rest don't seem to to follow suit. Once I get past the report from one building into a new one, is there a way to switch all fields to refer to the table associated w the new building?

[ItsMe]

Perhaps it will be easier to query your data if you combined all 11 tables into one table. Identify the fields that describe the individual buildings. Maybe, there is only one field, i.e. BuildingNum. If this is the case, you could simply paste the records from each table into a single table.

If there are multiple fields that describe how one building is different from another building, create a separate table for these fields. Maybe a tblBuildings. You can store the Primary Key from tblBuildings in your other table for the Assets (art).

You want to define your entities and provide tables for the entities. In this case, how a building is described, is an entity. For instance, Administration Building, Bldg 10A, Street Address, City, State, Mailing Address, etc.

After looking at your building descriptions, you may discover that keeping track of inventory by building is not the best thing. Perhaps there are different Departments within the same building. Maybe the Assets table should be related to a Department table. This way each department can be held responsible for and related to the Assets. And, if there is only one department per building or the responsibility for the Assets is associated with an entire building today, Maybe tomorrow this will change. What happens if accounting needs to expand to a new space and moves into the third floor of another building?

[nick404]

ItsMe has a good point. And a query may be a better option

[rezanaghibi]

The Database I am building is for Artwork on a University campus. All work was photographed and measured and the locations recorded.

The initial Excel spreadsheet turned out to be much too large in file size and would crash and repairing the file upon open would break the mouse-over images I had inserted via the Comments fields custom border work-around.

Once I moved the project into Access, I figured attaching images would still yield a large file size, and so that is why I decided to separate the Tables by building and floor. Ie: Table 1 is Birch Building Floor 1, Table 2 is Birch Building Floor 2... and so on.

Now that all tables are complete and the data has been input including ImageAttachments, the generated Report displays all vital data plus a thumbnail of the referenced Artwork. Per 8.5x11 page, in Landscape view, I have 4 pieces of artwork on each. So the problem came about when I tried to work on a different floor, pasted the layout via Report editor Design view and lo-and-behold the data from the new building wouldn't simply plug into the last..

Would putting all buildings into one table yield a massive file size? I intend to export these as PDFs for my boss and to have a database ready to go as I am a temp and this will be a hand-over project in the coming days. I know that if I do create one table, I can simply build these reports via Queries that would act the same way as the separate tables per building structure I currently have.

Phew that was a mouthful

[June7]

What this db is about is tracking art, not buildings. Whether data is one table or table for each building, doesn't really impact file size. Better design is one table for all art. Consider:

tblBuildings
BuildingID (PK)
BuildingName
BuildingAddress

tblArtist
ArtistID
LastName
FirstName

tblArt
ArtID (PK)
ArtName
ArtDimensions
ArtistID (FK)
BuildingID (FK)
FloorNo
LocationDesc
Photo

[rezanaghibi]

Okay then besides copy pasting, is there any sure fire way to compile into one table?

[June7]

Not simple if you have embedded image files in attachment field. An attachment field is a type of multi-value field. If you don't understand multi-value field, review: https://support.office.com/en-us/art...C-6DE9BEBBEC31

I don't think attachment field can be copy/pasted because it is a multi-value field. What happens when you try?

Also review https://www.accessforums.net/access/...nds-38039.html

Another problem with the multi-building tables design is if art is moved from one to another, this means deleting record in one table and adding record to another. Deleting records should be rare.

Alternative to redesigning tables is a UNION query to pull the records into one dataset to emulate the single table, but data is not editable through UNION.

[rezanaghibi]

Not simple if you have embedded image files in attachment field. An attachment field is a type of multi-value field. If you don't understand multi-value field, review: https://support.office.com/en-us/art...C-6DE9BEBBEC31

I don't think attachment field can be copy/pasted because it is a multi-value field. What happens when you try?

Also review https://www.accessforums.net/access/...nds-38039.html

Another problem with the building tables design is if art is moved from one to another, this means deleting record in one table and adding record to another. Deleting records should be rare.

Alternative to redesigning tables is a UNION query to pull the records into one dataset to emulate the single table, but data is not editable through UNION.

Hey

No you cannot paste, nothing happens at all.

Going back to the File size, since these will be exported to PDF files, file size is definitely something I need to be sensitive with. Insights?

[June7]

Yes, embedded files can cause db bloat. Whether or not that is an issue for you depends on how much data you expect to accumulate. Don't know how db file size issue relates to export of records to PDF.