Quickly Change Report Data Source

**tampaC66** · 03-23-2015, 02:48 PM

Hi all,

I have a master access file that has several excel tables linked into it. Each excel file is for a different project but the types of data are all the same. I create a query for each excel linked file and use that in my reports.

I have about 6 different reports and so far i've been creating new reports for every project. However... it is beginning to be a lot having 6x reports for 10+ projects and any time I need to make a change to the format it becomes a huge time sink.

Is there some VBA code that I can use to quickly change the data source of a report from one query to another?

**mikeTRON** · 03-23-2015, 03:38 PM

Yeah it is possible as I have done exactly this in the past, but I havent touched access in a few years (I have tried to convert over to powerpivot) so I can't offer exact help, other than it IS possible with VBA.

**tampaC66** · 03-24-2015, 12:31 PM

Does anyone know how I could achieve this?

**tampaC66** · 03-25-2015, 10:38 AM

Bump /10char

**mikeTRON** · 03-25-2015, 03:43 PM

I would attempt to help you but I don't have excel 2003 and I don't know if the reference libraries are the same, so even if I figured it out with my version, it might not translate perfectly.

Did you try recording a macro then making that code dynamic?

**tampaC66** · 03-25-2015, 05:39 PM

Can you record macros in access?

**alansidman** · 03-25-2015, 05:59 PM

It would certainly be easier if you did not have a different table for each project. This is counter to normalization of data for RDBMS. Consider changing your data source to not have this type setup as it will continue to haunt you as your project expands. You would be better served to have the project name as one of the fields in your table and then pull data in your query based upon the project. You then need only 6 queries for all your reports.

For a quick tutorial on how the Fundamentals of Database design, look at this link

http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf

**tampaC66** · 03-30-2015, 10:19 AM

Originally Posted by alansidman

It would certainly be easier if you did not have a different table for each project. This is counter to normalization of data for RDBMS. Consider changing your data source to not have this type setup as it will continue to haunt you as your project expands. You would be better served to have the project name as one of the fields in your table and then pull data in your query based upon the project. You then need only 6 queries for all your reports.

For a quick tutorial on how the Fundamentals of Database design, look at this link

http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf

I wish that were an option. Unfortunately the excel files already exist as a company standard and because of the way it works, we wouldn't be able to combine all projects into 1 excel file.

Surely there has to be a a quick way to change the query that the report uses as a data source.

**alansidman** · 03-30-2015, 07:25 PM

Ok, since normalization is not an option, then I propose the following to simulate normalization:

Create a Union Query to join all the tables. Then use this Union Query as the record source for your reports, changing up the criteria for each report as needed.

**tampaC66** · 03-31-2015, 04:58 PM

okay. I did some research and got a union query. So, now I have 2 tables combined into 1.

There is no column in my tables for "project name" though, so I dont know how to differentiate which data came from which table. Is there a way to add a parameter to the query that shows the table that each record came from?

**mikeTRON** · 03-31-2015, 05:33 PM

Sorry I cant help much, I would do this differently.
I would use Powerpivot, a free Microsoft Addin, to link to the access tables then join to one table and pivot on that.

**alansidman** · 03-31-2015, 09:21 PM

For each table, create a query and add a new field in the QBE. In the field name for the new field, type: Table:"TableName", where TableName is the actual table name.
Once you have created this query for each table, then join each of these queries in a Union Query.

Look at this video and at the around the five minute mark, he shows how to easily build the Union Query.

http://www.datapigtechnologies.com/f...nionquery.html

See attached example