MS Access Report

From the attach two tables in Access I want the report that will look like the attach excel Summary . I think we can use crosstab query and then create report on the crosstab query but I am not sure how to put the start balance in the report. The Report should look like the attach spreadsheet. Thanks
test.accdb
Summary.xlsx
snhandleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Have you tried to do this yourself? We're not really here to do your project for you, but instead to help you do this yourself.

You can create a Crosstab query and then simply create your report from there, but working with crosstabs can be difficult, especially when you have dynamic data. Instead, I prefer to create a temporary table that houses the data I need, and then create a report based on that temporary table. All I need to do from there is fill the temporary table, and my report will work as needed.

In your case, you'd need a table that would have all the Columns of your report (Source, Project, Source1, etc etc), and you'd then fill that table with the data you pull from the various tables in your database. Looks like you'd first determine the distinct Source+Project, and add rows for that, and then fill in value for Source1, Source2, etc.

To do that:

Currentdb.Execute "DELETE * FROM TempTable"
Currentdb.Execute "INSERT INTO TempTable(Source, Project) (SELECT DISTINCT Source, Project FROM [Raw Data])"
Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT * FROM TempTable")

Do Until rst.EOF
  Dim rstData As DAO.Recordset
  Set rstData = "SELECT * FROM [Raw Data] WHERE Operation='" & rst("Operation") & "' AND Source='" & rst("Source") & "')"

  Do Until rstData.EOF
    Select Case rstData("Funding")
      Case "Source1"
        rst("Source1") = rst("Source1") + rstData("Funding")
      Case "Source2"
        rst("Source2") = rst("Source2") + rstData("Funding")
      Case "Source3"
        etc etc  
    End Select
    rstData.MoveNext
  Loop
  rst.MoveNext
Loop

Open in new window


After that, you'd have a filled Temp table, so just create the report based on that.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you'd prefer to use a Crosstab, here's an article that describes how to do that:

http://www.blueclaw-db.com/report_dynamic_crosstab_field.htm

Basically, this shows how to dynamically assign properties and data to various objects in your Crosstab report. It's about as labor intensive as the method I suggest above, so you can pretty much pick your poison :)
0
hnasrCommented:
This is the crosstab query.
Use it as the report record source.
Try and comment back.
TRANSFORM SUM([Total Budget])
SELECT Source, Project
FROM [Raw data]
GROUP BY Source, Project
PIVOT Funding IN ("Source1", "Source2", "Source3", "Source4");

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

snhandleAuthor Commented:
It did produce the report but I want to have beg amount from the Start amount table included in this report for each source. So I can have the beg balance for each source. How I can do that? Thanks
0
hnasrCommented:
Try this - In report footer, find total, and dlookup the start amount, then subtract to get the remaining.
test-2.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
snhandleAuthor Commented:
It worked perfectly!
0
snhandleAuthor Commented:
Great Job!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.