Question Automating Access Tables & Exporting Data to Excel

[dubez]

Hi there!

I have the following following raw data in an access table (Raw_Data):

Raw_Data Table:

Program
Dealer
Agent
Product (either Product A or Product B)
Date Funded
Funded Amount ($)

Now, i have an Excel template I need updated (one template/spreadsheet would need to be updated per Program). For example, in Program "Test", I would need the following updated for the "Test" Program spreadsheet:

Column 1
Top 20 Agents (Name by Year To Date Funded amount)

Column 2
Values for the names in Column 1 By Year To Date Funded amount ($)(highest to lowest)

Column 3
Values for the names in Column 1 by last months Funded amount ($)

Column 4
Top 20 Dealers (Name by YTD Funded amount)

Column 5
Values for the names in Column 4 by Year To Date Funded Amount ($)(highest to lowest)

Column 6
Values of the names in Column 4 by last months Funded amount ($)

Additionally, I need a summary of the following:

Product A
Last month's Total Funded ($)
Last month's # of Funded (count)
Year to Date Total Funded ($)
Year to Date # of Total Funded (count)

Product B
(same criteria as Product A example)

Lastly, I would need the raw data for the Program (in this case the "Test" Program) in a separate tab for last month's data only.

What I need to do is run a query or macro that would ultimately display the information in the excel sheet/template and do this for ALL programs (1 Program per template). There are currently about 40 Programs.

I am currently doing this all manually and I am looking to learn some new skills and simplify my life.

Please help if you can!
Thanks so much!

 

[spikepl]

So do a tutorial on queries so you can produce a query to give you each item of what you want.

Give it a go and then you can ask for support when stuck (that is the usual sequence).

Once you have that, you can export it to Excel from Access.

 

[dubez]

Thanks Spikepl!

I do use and understand queries so i am able to do this and currently do for my manual process. Would you recommend i build out all the queries for each of the Programs first or is there a way this could be done in the bigger macro to bypass the manual process of creating all the various queries for each Program?

I am only asking because I estimate that manually creating all the queries and applying them to each Program will take some time (which i am also fine with).

Thanks for your reply