Extracting data from two or more tables


#1

Hello

Let’s say you have two tables, one is Budget and the other is Expenditure

Both tables have same if not similar field structure such as salaries, materials, travel, fees & total

And each table has records of peoples names and the amounts for the budget that they have been given in the Budget and the amounts that they have spent in the Expenditures

How do I set up a Variance table that calculates the difference between the totals for the Budget and the totals for the Expenditure?

Thanks


#2

I think it might suit your needs better to combine Expenditures into the Budget table. Each record (row) in your budget table would represent either a credit or an expenditure, and you’d represent this either by having a single “Amount” field in which you use either a positive number (credit) or a negative number (expenditure), or else by having separate “Credit” and “Expenditure” fields in which you use positive numbers for both.

Then, your second table would be a “People” table, since this seems to be your critical budget unit. You would link each “Budget” record to a “Person” record. In the “People” table, you’d use Rollups to pull in and SUM all the Credits and Expenditures, and be able to get a “Balance” for each Person.

That’s just one way of doing it – I’m sure there are others that could be devised.


#3

Thank you, that’s very helpful. I’ll give it a try.
I was thinking more along the lines of how MS Access works.