Report with multiple Linked tables with same type of data.

[jcrew10]

Is there a way to do a report that gets information (data from tables that have same field names) from 5 different tables? I don't know much about databases in general but have this problem. I have 5 different locations that have the same exact data tables (different names) but I wanted to roll up that data in 1 report so I can get calculations done. Is there a way to do this? Using access 2010.

[Galiphraen]

You could try writing an sql query that looks like the following:

select * from table 1
union select * from table 2
union select * from table 3
union select * from tabel 4
union select * from tabel 5

This should give you all the data in each table in one report.

[NeoPa]

It is certainly possible. However, the chances that your design is as it should be in such a scenario are somewhere shaving zero.

You need to consider designing your database to work for you rather than against you. Database Normalisation and Table Structures has a number of points you really need to see.

[jcrew10]

Well this all started as a separate location type database. Each location (5 total) has their own dedicated database because all locations would need to input data at the same time (COB) so it was structured to prevent folks from not being able to save data. Users are not computer savvy. That's why all the tables have the same field names but just are different table names (This is what they are used to from their hard copy). The data is similar in the sense that its all for the same purpose for metrics purposes. Now, they want a roll-up of all those individual reports to calculate when certain locations are selected. That's where I am at a loss. I will review the Database Normalization and Table Structures link and see if I can't figure something out but simple was focus of the database. That's what I am capable of at this point. That's why I am looking for help because now this is becoming more out of my element. I will also give Galiphraen's suggestion a shot too and see if that doesn't work too. Thanks for all the help.

[GKJR]

These tables should be combined into one table with a new field representing the location. You may want to create another table with all of the different locations and use these records as your list to choose from in the original table.

You don't need to worry about multiple users inputting data at the same time. Access Databases are locked at the record level, not the whole file like an Excel or Word document. You would most likely need to split the database into a back end file and multiple front end files (one for each user).

You could use Union queries, but it sounds like that will be more complicated for you if you don't understand normalization.

[jcrew10]

I made a new table (called LinkedTable) that has all the stores rolled up. I did this by doing the SQL query per Galiphraen's suggestion. I got the data to roll up. Now I need to sort the data where I can have some calculations populate when that store and date range is selected. Thanks every for the information so far!

[NeoPa]

GKJR gives sensible advice. It's where I was coming from. However, locking in Access has a few options. A little more complicated than indicated - but fundamentally allows what he suggests even if you get the options wrong.

Using filtering you can still ensure that only one store ever sees the data for each store. Store A only ever sees or works on the data for Store A. The same for Store B. Etc.

[jcrew10]

Thanks everyone, I am understanding more of what's happening with the tables. I had not thought of it in those ways so I am very appreciative of everyone's input/advice.

[NeoPa]

It's always nice to deal with someone who's interested in making progress in their understanding. I'm going to go ahead and set GKJR's post as Best Answer for you on this.

[jcrew10]

I am very appreciative that there are folks, like everyone who has posted, that are willing to give a beginner a chance. Great forum folks! Thank you so much!