MS Access Report

Posted on 2014-07-15
Last Modified: 2014-07-17
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
Question by:snhandle
    LVL 84
    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

    Open in new window

    After that, you'd have a filled Temp table, so just create the report based on that.
    LVL 84
    If you'd prefer to use a Crosstab, here's an article that describes how to do that:

    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 :)
    LVL 30

    Expert Comment

    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


    Author Comment

    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
    LVL 30

    Accepted Solution

    Try this - In report footer, find total, and dlookup the start amount, then subtract to get the remaining.

    Author Comment

    It worked perfectly!

    Author Comment

    Great Job!

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
    Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now