• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 542
  • Last Modified:

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
0
snhandle
Asked:
snhandle
  • 3
  • 2
  • 2
1 Solution
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
snhandleAuthor Commented:
It worked perfectly!
0
 
snhandleAuthor Commented:
Great Job!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now