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
test.accdb
Summary.xlsx
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 :)
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.
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");
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It worked perfectly!
ASKER
Great Job!
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:
Open in new window
After that, you'd have a filled Temp table, so just create the report based on that.