Multiple account numbers

[BenM912]

Hey guys, just wondering if anyone could help me overcome this error. I have a sum that adds up all the Income and all the Expenditure a customer has. With these sums I then figure out the Disposable Income by subtracting Income by Expenditure. It seems to work, but when I change the record it retains the first expenditure value and removes it from the next persons income (which is wrong!). Here's the example:



Customer 1 has a total income of £11,000 and an expenditure of £480. This calculates the DI to £10,520 (which is right).

Customer 2 has no values in their income/expenditure boxes. When I add a type of income £10,000 (but no expenditure) and refresh the page it calculates the DI as £9,520 which is wrong! Now, the problem is this (what I think anyway). The code for my DI is this:

SELECT TotalIncome.[Account Number], TotalIncome.SumOfAmountPaid, TotalExpend.SumOfExpenditureAmount, [SumOfAmountPaid]-[SumOfExpenditureAmount] AS SumOfDI
FROM TotalIncome, TotalExpend
GROUP BY TotalIncome.[Account Number], TotalIncome.SumOfAmountPaid, TotalExpend.SumOfExpenditureAmount, [SumOfAmountPaid]-[SumOfExpenditureAmount];

Now, I know what the problem is, but I'm not too sure how to solve it. Basically I have two versions of the account number, the one in TotalIncome and the one in TotalExpend. However, as the account number in TotalExpend isn't being called, it's not associating the expenditure value to the account number. I tried using both but this just flags an error and doesn't calculate the DI.

Anyone have any ideas on how to solve this?

[pbaldy]

I would expect the two tables/queries to be joined:

FROM TotalIncome LEFT JOIN TotalExpend ON TotalIncome.[Account Number] = TotalExpend.[Account Number]

In design view you'd accomplish that by clicking/dragging between the account number field in each table, creating a join line between them. If you right click on that line and edit the join, you'll see a description on the three possibilities.