Running total using subquery not quite working

[Sheridan]

I took this example code from rogersaccesslibrary.com:

SELECT tblOrderDetails.OrderDetailID,
     tblOrderDetails.OrderID,
     tblOrderDetails.ProductID,
     tblOrderDetails.Price,
     (SELECT Sum(Price) FROM tblOrderDetails as OD
        WHERE OD.OrderDetailID <= tblOrderDetails.OrderDetailID
           AND OD.OrderID=tblOrderDetails.OrderID) AS RunningSum
FROM tblOrderDetails;

...and put this together this query for an invoices database I'm building:

SELECT INVOICES.INVOICE_NUM_PK, 
INVOICES.PSSN_FK, 
INVOICES.LIFE_AMOUNT, 
INVOICES.DEPENDENT_AMOUNT, 
INVOICES.DENTAL_AMOUNT, 
INVOICES.SUPPLE_AMOUNT, 
INVOICES.AMOUNT_PAID, 
INVOICES.INVOICE_DATE, 
SUM(INVOICES.LIFE_AMOUNT + INVOICES.DEPENDENT_AMOUNT + INVOICES.DENTAL_AMOUNT + INVOICES.AMOUNT_PAID) AS [Amount Due], 
(SELECT SUM(INVOICES.LIFE_AMOUNT + INVOICES.DEPENDENT_AMOUNT + INVOICES.DENTAL_AMOUNT + INVOICES.AMOUNT_PAID) FROM INVOICES AS OD 
WHERE OD.INVOICE_NUM_PK <= INVOICES.INVOICE_NUM_PK
AND OD.PSSN_FK = INVOICES.PSSN_FK) AS RunningSum 
FROM INVOICES 
GROUP BY INVOICES.INVOICE_NUM_PK, INVOICES.PSSN_FK, INVOICES.LIFE_AMOUNT, INVOICES.DEPENDENT_AMOUNT, INVOICES.DENTAL_AMOUNT, INVOICES.SUPPLE_AMOUNT, INVOICES.AMOUNT_PAID, INVOICES.INVOICE_DATE
ORDER BY INVOICES.PSSN_FK, INVOICES.INVOICE_NUM_PK;

The main difference between my SQL query and the example is that I'm trying to sum several columns in RunningSum, not just one. At first it seemed to be working, but when I tried plugging amounts into AMOUNT_PAID that were less than the AMOUNT_DUE, I noticed that RunningSum wasn't totaling correctly. (I hid PSSN_FK in these screenshots)

I'm still fairly new to Access and SQL in general. Appreciate any help. Thanks!

 

[plog]

I got a lot of questions and comments. Let's start with comments:

I don't think you are storing your data properly. Field (and table) names should be generic. They shouldn't contain organization terms and types. All those X_AMOUNT fields (LIFE_, DEPENDENT_, DENTAL_, SUPPLE_) should be in their own table where all those words are in a TYPE field instead of field names:

tblAMOUNTS
amount_ID, autonumber, primary key
amount_TYPE, text, will hold LIFE, DENTAL, etc. values
amount_VALUE, number, will hold the dollar amount currently in each of those fields

So instead of 4 fields for those costs you would add 4 records (or less if some types have no values to hold) to tblAMOUNTS. That's how that data should be stored. Of course that doesn't really address the issue you are facing, so here's the questions:

1. You posted 2 queries--do they work independently? Does the running total query work correctly by itself? If you exclude the running query field from the second query you posted is it correct?

2. Why did you GROUP BY every field in the second query? That seems odd and is most likely the culprit. Why did you do that--before you added the running total field to it did you try that query without the GROUP BY and it was wrong? A running total query usually doesn't group the individual records. Plus with the ID fields you have in there the GROUP BY probably doesn't do anything. Super confused by this part of the query.

3. Why isn't the date part of the running total? The IDs come out in order, but what if someone goes back in and changes a date on you such that ID=17 happens in January but ID=16 occurs in February? Those are now out of order and your running total is wrong.

4. Can you better illustrate your issue? We don't have the underlying data so the data you posted doesn't illustrate anything. It's hard to determine what went wrong when all you show us is the finished data and maybe what is suppose to be the correct data. If you could provide all the data we would need to manually walk through this thing we can better pinpoint what went wrong.

 

[LarryE]

As others have said, it appears your table design is flawed from the beginning. Please take a screen shot of the Database Relationship window and copy it into a new Reply so someone can assess the original design and the relationships you have between tables. No query is going to show data properly until the design is properly created.

 

[Pat Hartman]

In addition to plog and Larry's valid points, calculating a running sum in a query is a very expensive operation and the more data in your tables, the slower the query will run. This functionality is better when performed in a report where the process is simple and liniar.

 

[CJ_London]

You can use a non standard join to link the table to itself on a.pk=b.pk and b.invnum <=a.invnum where one table is aliased as a and the other as b

group on fields in a, sum fields in b

can only be done in sql, the qbe can’t do it

 

[mike60smart]

I took this example code from rogersaccesslibrary.com:

SELECT tblOrderDetails.OrderDetailID,
     tblOrderDetails.OrderID,
     tblOrderDetails.ProductID,
     tblOrderDetails.Price,
     (SELECT Sum(Price) FROM tblOrderDetails as OD
        WHERE OD.OrderDetailID <= tblOrderDetails.OrderDetailID
           AND OD.OrderID=tblOrderDetails.OrderID) AS RunningSum
FROM tblOrderDetails;

...and put this together this query for an invoices database I'm building:

SELECT INVOICES.INVOICE_NUM_PK,
INVOICES.PSSN_FK,
INVOICES.LIFE_AMOUNT,
INVOICES.DEPENDENT_AMOUNT,
INVOICES.DENTAL_AMOUNT,
INVOICES.SUPPLE_AMOUNT,
INVOICES.AMOUNT_PAID,
INVOICES.INVOICE_DATE,
SUM(INVOICES.LIFE_AMOUNT + INVOICES.DEPENDENT_AMOUNT + INVOICES.DENTAL_AMOUNT + INVOICES.AMOUNT_PAID) AS [Amount Due],
(SELECT SUM(INVOICES.LIFE_AMOUNT + INVOICES.DEPENDENT_AMOUNT + INVOICES.DENTAL_AMOUNT + INVOICES.AMOUNT_PAID) FROM INVOICES AS OD
WHERE OD.INVOICE_NUM_PK <= INVOICES.INVOICE_NUM_PK
AND OD.PSSN_FK = INVOICES.PSSN_FK) AS RunningSum
FROM INVOICES
GROUP BY INVOICES.INVOICE_NUM_PK, INVOICES.PSSN_FK, INVOICES.LIFE_AMOUNT, INVOICES.DEPENDENT_AMOUNT, INVOICES.DENTAL_AMOUNT, INVOICES.SUPPLE_AMOUNT, INVOICES.AMOUNT_PAID, INVOICES.INVOICE_DATE
ORDER BY INVOICES.PSSN_FK, INVOICES.INVOICE_NUM_PK;

The main difference between my SQL query and the example is that I'm trying to sum several columns in RunningSum, not just one. At first it seemed to be working, but when I tried plugging amounts into AMOUNT_PAID that were less than the AMOUNT_DUE, I noticed that RunningSum wasn't totaling correctly. (I hid PSSN_FK in these screenshots)

View attachment 114621

View attachment 114622

I'm still fairly new to Access and SQL in general. Appreciate any help. Thanks!

Hi
It would help if you uploaded a zipped copy of your database so we can see the table structures.

 

[Sheridan]

I got a lot of questions and comments. Let's start with comments:

I don't think you are storing your data properly. Field (and table) names should be generic. They shouldn't contain organization terms and types. All those X_AMOUNT fields (LIFE_, DEPENDENT_, DENTAL_, SUPPLE_) should be in their own table where all those words are in a TYPE field instead of field names:

tblAMOUNTS
amount_ID, autonumber, primary key
amount_TYPE, text, will hold LIFE, DENTAL, etc. values
amount_VALUE, number, will hold the dollar amount currently in each of those fields

So instead of 4 fields for those costs you would add 4 records (or less if some types have no values to hold) to tblAMOUNTS. That's how that data should be stored.

That's a good recommendation. I'll try to implement it soon. I was so focused on trying to get a running total going that I didn't think about that. I was trying to get a running total working as a proof of concept going first.

Right now I just have a Members table and an Invoices table in a one-to many relationship. We're only invoicing about 90 members, and it's a closed group so it's not going to grow ever. The rates we bill each person per month varies per person, and is subject to change year to year. Their current rates are stored in the Members table. I have INSERT INTO query that can create an invoice for each member every month; it copies the values in the DENTAL_AMOUNT, SUPPLE_AMOUNT, LIFE_AMOUNT, DEPENDENT_AMOUNT Member fields into the equivalent fields on the Invoice record. I did this as the rates are subject to change and I didn't want rate changes to affect past invoices.

INSERT INTO Invoices ( PSSN_FK, INVOICE_DATE, DENTAL_AMOUNT, SUPPLE_AMOUNT, LIFE_AMOUNT, DEPENDENT_AMOUNT )
SELECT [Members].PSSN, CURRENT_DATE, DENTAL_AMOUNT, SUPPLE_AMOUNT, LIFE_AMOUNT, DEPENDENT_AMOUNT
FROM Members
WHERE ((([Members].INVOICE_GROUP)=True))
ORDER BY [Members].PSSN;

1. You posted 2 queries--do they work independently? Does the running total query work correctly by itself? If you exclude the running query field from the second query you posted is it correct?

I tried the subquery by itself and it produced a single total that matches a total of all the values in the AMOUNT_DUE column. So I think it works correctly alone?

2. Why did you GROUP BY every field in the second query? That seems odd and is most likely the culprit. Why did you do that--before you added the running total field to it did you try that query without the GROUP BY and it was wrong? A running total query usually doesn't group the individual records. Plus with the ID fields you have in there the GROUP BY probably doesn't do anything. Super confused by this part of the query.

I kept getting "Your query does not include the specified expression 'INVOICE_NUM_PK' as part of an aggregate function." It would give me this error message until every non-aggregate field was grouped.

3. Why isn't the date part of the running total? The IDs come out in order, but what if someone goes back in and changes a date on you such that ID=17 happens in January but ID=16 occurs in February? Those are now out of order and your running total is wrong.

Good point. I'll adjust for that.

4. Can you better illustrate your issue? We don't have the underlying data so the data you posted doesn't illustrate anything. It's hard to determine what went wrong when all you show us is the finished data and maybe what is suppose to be the correct data. If you could provide all the data we would need to manually walk through this thing we can better pinpoint what went wrong.

I can't share the database as it contains real records with sensitive information. I can just do screenshots of relationship schema and some SQL queries.

 

[plog]

#2: I kept getting "Your query does not include the specified expression 'INVOICE_NUM_PK' as part of an aggregate function." It would give me this error message until every non-aggregate field was grouped.

I don't think you need to SUM and add all your amount fields. SUM works vertically (on multiple records) and + works horizontally (on multiple fields in the same record). You are not adding up multiple records in that second query, just multiple fields in each record:

SELECT INVOICES.INVOICE_NUM_PK, 
...
SUM(INVOICES.LIFE_AMOUNT + INVOICES.DEPENDENT_AMOUNT + INVOICES.DENTAL_AMOUNT + INVOICES.AMOUNT_PAID) AS [Amount Due],

Because you included SUM in that field, syntactically you had to add GROUP BY. Because you had the primary key of the table (INVOICE_NUM_PK) in the GROUP BY, logically it wasn't grouping anything because each record was unique. What you should do is remove the SUM and the GROUP BY. You can still add all those fields together with +, just don't put them inside a SUM, the results will be the same.

As for #4, It looks good. The only thing I can think of is dates not occuring in the same order as the primary key values. So, make those 2 changes---remove the SUM/GROUP BY and change the running total query to look at dates instead of primary keys. If that doesn't fix it post your new SQL and one example. For that one example really drill down on it--pick only one invoice and give us all the data in the tables, the actual results of the query and the expected results of the query.

 

[LarryE]

That's a good recommendation. I'll try to implement it soon. I was so focused on trying to get a running total going that I didn't think about that. I was trying to get a running total working as a proof of concept going first.

Right now I just have a Members table and an Invoices table in a one-to many relationship. We're only invoicing about 90 members, and it's a closed group so it's not going to grow ever. The rates we bill each person per month varies per person, and is subject to change year to year. Their current rates are stored in the Members table. I have INSERT INTO query that can create an invoice for each member every month; it copies the values in the DENTAL_AMOUNT, SUPPLE_AMOUNT, LIFE_AMOUNT, DEPENDENT_AMOUNT Member fields into the equivalent fields on the Invoice record. I did this as the rates are subject to change and I didn't want rate changes to affect past invoices.

INSERT INTO Invoices ( PSSN_FK, INVOICE_DATE, DENTAL_AMOUNT, SUPPLE_AMOUNT, LIFE_AMOUNT, DEPENDENT_AMOUNT )
SELECT [Members].PSSN, CURRENT_DATE, DENTAL_AMOUNT, SUPPLE_AMOUNT, LIFE_AMOUNT, DEPENDENT_AMOUNT
FROM Members
WHERE ((([Members].INVOICE_GROUP)=True))
ORDER BY [Members].PSSN;

I tried the subquery by itself and it produced a single total that matches a total of all the values in the AMOUNT_DUE column. So I think it works correctly alone?



I kept getting "Your query does not include the specified expression 'INVOICE_NUM_PK' as part of an aggregate function." It would give me this error message until every non-aggregate field was grouped.



Good point. I'll adjust for that.



I can't share the database as it contains real records with sensitive information. I can just do screenshots of relationship schema and some SQL queries.

You have duplicate fields in the two tables which obviously will not work in ACCESS. You are going to keep getting field name errors because ACCESS won't know which field in which table you are using.

Please give us an explanation of exactly what you are attempting to track with this system. It appears you are invoicing members of some kind of limited group. What is the field "Invoice Group" used for? Can each Member have multiple Invoice Groups or can each Invoice Group have multiple members? Does each Invoice Group have multiple invoices? It seems this has something to do with insurances policies of some kind but it is unclear.

All these questions need answering before any queries can be created. Build you table and relationship design based upon your knowledge of the business workflow you are attempting to keep track of.