Excel data from a Access

[ThomasHOL]

I have build a Excel report that draws data from a Access database, I know that access have some limitations but I’m hoping that you have some tips for me. I need to make a monthly overview of ex revenue and salary expense. So I connect to the database set a filter that draws the data from January for the selected project. For February I connect again set a different filter for February etc. So the small sample below have 6 diffrent connections to access

	January	February	March
Revenue	500	480	520
Salary	300	280	320

<tbody>
</tbody>

So in no time I will get a lot of connections to Access (at least 120 connections for a single years report for one project) and then you can add at least 20 active project and then we would be talking 1200+ connections. As you might have guessed Excel / access will have none of this. And is breaking down after I add more that abouth 30 connections and try to copy it to an extra sheet.

So my question is there a better way of connecting to the database. The data I’m looking for is placed in one database file and 4 different tables and several fields come from the same table. So is there any way to connect to the database once and then getting multiple results out of it and still precenting the data as i need?

 

[Kyle123]

What is your table structure & field names?

 

[ThomasHOL]

The table structure is like this (and a lot more data)

ID	Period	Project	Revenue	Salary
1	31-1-15	1	100	80
2	31-1-15	2	120	90
3	28-2-15	1	110	85
4	28-2-15	2	130	95
5	31-3-15	1	120	105
6	31-3-15	2	140	115
7

<tbody>
</tbody>

 

[Kyle123]

Have a look at Cross tab queries https://support.office.com/en-us/ar...cc8-ba60-2cd8484667e8?ui=en-US&rs=en-US&ad=US

 

[ThomasHOL]

That looks like it could be helpful but as i said i need to return that data to Excel so i can work with it and present it to the users. And i dont see an option for doing this from whitin Excel.

 

[Kyle123]

So why can't you just query the cross tab report from Excel like you would any other Access query?

 

[ThomasHOL]

Just to make sure that we understand each other. You would make the structure of the report in Access and then just connect to aces and paste it in the report like I would if I wanted the entire table?

 

[Kyle123]

There wouldn't be any "Pasting" involved, but yes. I'd use a query table from "Get external data"

 

[ThomasHOL]

Good i have tested it on my data and it looks like this is what i'm looking for. But perhaps you can gelp me a bit more so i understand how to use it properly.

I have created 2 crosstab query one for revenue and one for Salaries using the wizard and those i would just call from Excel by a query.

The views in acces is giving me a bit of truble. I have selected the data and i need to get it listed by period as my example above. So i the date for the colums and then i selected period month. This gived me the desired spread however it will only work this year as Januar 2015 and Januar 2016 would be added together in the same colum. Is there a workarround for this.

sorry for the Danish code but i think you can understand it anyway. in the design view i get this formula in the period.

Udtryk1: Format([Periode];"mmm")

This gives me all 12 months and i can import it. But our projects are longer than 12 month so i would like to add 2016 and 2017 data without sacrifising the periods or needing me to manually update things every time i add more data to the table.