Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    660

    Unanswered: Need help with financial report in access database

    This is my first time to do financial report for financial department. They would like to have an excel report that you can find in the attachment. I have no idea about it. In access database, we can use crosstab query to retrieve the data look like the excel report. Anybody, please look and give me some idea or code sample. Many thanks.
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'm not sure how many responses you'll get on this as you're asking for something where you haven't put any ideas on how YOU'VE thought about doing it.

    Financial databases are often varied about what they do. The best advice I can give you is to figure out what fields from the spreadsheet you've attached that you want to track in a table and start adding the field names. Try to come up with something which follows the flow of the spreadsheet, focusing on the column header names. For example, instead of 6249, 5321, 3212, etc...you might have a field called: ProgramCode, then a Payment type field, a PaymentName field, a ReceiptNum field, a Certification field, a Char field, etc... (but stay away from RESERVED words for fieldnames!) It's always fun to make an excel sheet into a database but I would look at a few MSAccess financial examples first.

    Crosstab queries are not difficult but are often hard to understand for someone new to the database world. Play around with a few examples with a stab at a new table design to see how they work. You may find that you don't even need to utilize them.

    Once you have a good table structure designed, crosstabs and reports usually fall into place. MSAccess is very powerful on designing good reports which can do almost anything.
    Last edited by pkstormy; 07-31-07 at 14:15.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Mar 2004
    Posts
    660
    Thanks. Yes, i created the fields as ProgramCode, then a Payment type field, a PaymentName field, a ReceiptNum field, a Certification field, a Char field, etc... But when i creat crosstab query, i need to type all the fields in the column headings. It is not nice. If later i add more categories in the categories table. I have to add in the column headings again. Another thing bother me is that: When i created a report using crosstab query, in the field list, it show all the categories as field name, not just categories. It treated all the categories as a field list. If later we add more categories, i need to redo the report. It is not good.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    It might help if you could upload a sample of it for us to take a look.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •