Report showing sums from unrelated tables

Topic

New Reply

(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?

Reply | Quote

Replies

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote