Access SQL Sub-Query – Help/Advice Needed (1 Viewer)

I’m trying to get an invoice/receipt payment allocation system built up in my Commercial Real Estate Database.

Because I need to bring together Invoice records and receipt records within the same recordset I’ve decided to go down the route of creating a temp table in VBA/SQL and populating the table with data from both Invoice and Receipts tables - as well as unbound columns, which will be filled with receipt data before writing pertinent data back to the underlying receipts table .

This temp table will be viewed in a subform, with common data (to all sub records) such as date of receipt etc being part of the Master form.

If my Invoice allocation system is to be anyway successful it needs to be able to:

• Receipt multiple amounts against a single (or multiple) invoices
• Be able to display an adjusted balance (in the sub form) for an outstanding invoice i.e. If a part payment is made prior to the current allocation event then this must be reflected.

I have been messing around with some test data and enclose a (stripped down) copy of my production DB.

• tblDRInvLineItem: contains the line items (or Invoice) number and the amount outstanding.
• tblDRRecM2M: Is a junction table for a many-to-many relationship between the Line item and Invoice Tables. This contains FK’s for the other two tables and effectively links receipts to invoice/line items
• tblDRRec: Contains the receipts information

The major issue that I have run into is when I try to display the adjusted outstanding balance. I originally thought that this would be quite simple; Create a query and then a simple expression to minus one from the other. Unfortunately, this doesn’t work when there is more than one receipt for a single invoice - I have receipted two $50 payments against one $100 invoice.
You can see this demonstrated in ‘qryInvoice_Receipts’ (Look at the line item ID and then the 2nd record total).

I then decided to sum the Receipts column (qryInvoice_Receipts2), expecting the result $100 - $100 = 0, but I get $50 as an answer. I understand why this is happening so I have decided to try my hand at (my first) sub query. The idea is to calculate the sum of receipts in the inner query and then use that result in the outer query to calculate the adjusted balance.

This is where things start to get tricky for me as I have been relying on access to create the syntax SQL for me, but now I have to do this myself.

Can anyone offer any advice or -hopefully – a solution to my frustrating problem?

eddix99 said:

I’ve decided to go down the route of creating a temp table in VBA/SQL and populating the table with data from both Invoice and Receipts tables

Click to expand...

If you need to move data around in tables in order to process it, you almost certainly have a table design problem. You should be able to draw directly from your stored data to process your result without moving stuff around.

So before you design this process, consider troubleshooting your tables first, and getting advice on how to structure them better. Then, all tasks will be easier.

Hope this helps,

Consider an aggregate query of the join of tblDRInvLineItem and tblDRRecM2M grouped by the DRInvLineItemID_PK and DRInvLineItemAmount and the sum of DRRecAmount or


Then you get the balance in this from [DRInvLineItemAmount]-[SumOfDRRecAmount]. If you copy and paste the SQL above into the query designer and look at it in design view you will probably see what I mean.

Sneuberg, Nailed it!

I will digest this now and report back. Many thx

It's so simple when you have the solution!!

I can see that access did not generate the correct expression in the SELECT statement. By subtracting the Sum of Receipts instead of just Receipts it has solved my issue.

I noticed that the expression was duplicated in the GROUP BY Statement, which also had to be removed.

GROUP BY tblDRInvLineItem.DRInvLineItemID_PK, tblDRInvLineItem.DRInvLineItemAmount, [DRInvLineItemAmount]-[DRRecAmount];

Click to expand...

Is it simply a question of Access generating the wrong syntax for the job at hand?

Many thanks again for the assistance with this.

NB also no subquery needed after all

eddix99 said:

I noticed that the expression was duplicated in the GROUP BY Statement, which also had to be removed.

Click to expand...

I had already taken that out and it wasn't in the SQL I posted which I guess means Access put in back in. I don't know why that happened. When I took it out and changed the Group By to Expression I thought it would stick.