Results 1 to 7 of 7
  1. #1
    kbwa
    Guest

    Report showing sums from unrelated tables

    (Access 97) I know there must be a simple way to do this, but I can't figure out how. I've got a number of tables which income from various income generating sources. The tables have no relationships. I want to make a report that gives the summaries of the various tables' income field, but I can't make it work. I get messages like cannot connect tables. How do you do this in Access?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report showing sums from unrelated tables

    To link tables, there must be a shared key on which to "relate" the records. That's how relational databases work. Your problem is that your database is not designed to allow you to do this. Normally, you would have a single table to hold income with a field that designated the income source. Then you could use a GroupBy query to return the total income by income source.
    Charlotte

  3. #3
    kbwa
    Guest

    Re: Report showing sums from unrelated tables

    Thanks for the info. I know I can use some programming in FoxPro to pick up totals from various tables. Can't I do this in Access using Modules?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report showing sums from unrelated tables

    You can, using VBA code, but using code is no substitute for designing it right in the first place. FoxPro is a slightly different animal, since its tables are in separate files, not all in the same file as in Access. However, even in FoxPro, multiple tables holding the same kind of information is bad design. In VBA code, you would have to execute a series of queries, one for each table to get the total for that table and then add it to an accumulator variable.
    Charlotte

  5. #5
    kbwa
    Guest

    Re: Report showing sums from unrelated tables

    Does anyone have a sample of how one might use VBA code, which would execute a series of queries, one
    for each table to get the total for that table and then add it to an accumulator variable? That sounds like just what I need to do.

    Thanks for your help.

    Joyce

    In order to put this info in one table, I'd have to add 15 more fields (doubling the number of fields) to accommodate about 2% of the data. That just doesn't seem efficient.

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Indiana, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report showing sums from unrelated tables

    Joyce,

    You may not need to use VBA code. You could set up your queries as Make Table queries, put all the queries in a macro, turn the Make Table warnings off, then in the macro, run a report that reads totals from the new tables. Making the macro, the Make Table queries, and the report would require you to read the online help, but it would be worth your while to take the time. Access can do a lot of things without having to write code. In the long run, though, if you plan to stick with Access, learning some VBA will really make you a good application developer.

    HTH

  7. #7
    kbwa
    Guest

    Re: Report showing sums from unrelated tables

    Thanks Lonnie,
    I'll give that a try. I have no problem using the online help, but finding the right word to search on what I'm looking for is usually the hardest part. The Make Table queries are well documented, and I think I can handle the macros.

    Thanks again
    Joyce

Posting Permissions

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