How to create a large table to report data?

[lovelykid23]

Hi guys,

I'm moving my office Excel file to Access. The excel file has a few 'report' tabs that give overview of the database. I need to re-create these reports in Access as closely to the ones in Excel as possible. Along the way, I have a problem with a gigantic report table in Excel. It looks like this.

All of the fields (except the County) are calculated fields and there are 100 Counties (which means 100 rows of records). Access report does not let me create as many calculated fields as needed. To further complicate things, I have a database of thousands of customers. I set up a query, and my user can input parameters of the time period they want to search and the type of products. This County table needs to choose out customers from specific County that purchase the type of product and within the time period that my database user put in the parameters.
Normally, I would create a Report based on the query. Yet, as I said earlier, Access does not let me create a humongous table with that many calculated fields.
Do you guys have any suggestion of how I can do this? or should do this?

 

[plog]

Without seeing under the hood (the tables/fields you have) I can only help generally. I have 2 suggestions:

1. Keep the report in Excel. Simply have a query(ies) that generate the data you need to report on. Copy it from Access into a tab on Excel called 'data', then have another tab in Excel called 'report' that references the data and puts it where it needs to go.

2. Do this in Access via sub-queries. When I look at what you posted I see 5 sections (I assume the grey columns mean something). That's how I would tackle this with queries. Each section becomes its own sub-query. Then in the end you build one main query to bring them all together. You would base that main query on a datasource that has eveyr county and then you would link your sub-queries to it and bring in the fields you need from each.

Again, without specific table/field data we can only help generally.

 

[Uncle Gizmo]

Should do this?

If you have the time, inclination and patience, if MS Access is something you want to learn then yes, I think you should.



Sent from my SM-G925F using Tapatalk