Link to home
Start Free TrialLog in
Avatar of snhandle
snhandle

asked on

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
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

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.
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 :)
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

Avatar of snhandle
snhandle

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Hamed Nasr
Hamed Nasr
Flag of Oman image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It worked perfectly!
Great Job!