Running total in query without key

[bd528]

Hi all,

I have a query where I need to record a running total. My current code is below

SELECT tblBrokerages.BDM_Name, Format([DateTPIAgreementSigned],"mmm-yy") AS xMonth, Count(tblBrokerages.DateTPIAgreementSigned) AS Original
FROM tblBrokerages
GROUP BY tblBrokerages.BDM_Name, Format([DateTPIAgreementSigned],"mmm-yy"), Format([DateTPIAgreementSigned],"yyyy-mm")
HAVING (((Count(tblBrokerages.DateTPIAgreementSigned))>0))
ORDER BY tblBrokerages.BDM_Name, Format([DateTPIAgreementSigned],"yyyy-mm");

I need a running total per tblBrokerages.BDM_Name. I've looked online for ways to achieve this, but each method I've found involves having a unique key, which my query doesn't have.

Is there another way to produce a running total column?

Thanks in advance

 

[Ranman256]

You can do it in a report. Set the field to running sum=true

 

[plog]

You don't need a unique key (actually using an autonumber is a bad idea), you need a unique way to order your data. Looks like you have it.

Can you post sample data to demonstrate what you hope to end up with? Provide 2 sets:

A. Starting sample data from your datasource. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what you expect to end up with when you feed your A data into the query.

 

[bd528]

You don't need a unique key (actually using an autonumber is a bad idea), you need a unique way to order your data. Looks like you have it.

Can you post sample data to demonstrate what you hope to end up with? Provide 2 sets:

A. Starting sample data from your datasource. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what you expect to end up with when you feed your A data into the query.

Please see attached - thanks

I'm also trying Ranman256's report option

 

[plog]

Below is the SQL you can use to get the Running Total in a query:

SELECT Main.BDM_Name, Main.xMonth, Main.Original, (SELECT SUM(Original) FROM YourDataSourceName WHERE BDM_Name=Main.BDM_Name AND xMonth<=Main.xMonth) AS RunningTotal
FROM YourDataSourceName AS Main;

2 notes:

1. You will need to replace instances of "YourDataSourceName" in the code above with the actual name of the data source you will be building this on.

2. You will need to make xMonth an actual date. If you make it a date, it will then sort like a date (Jan, Feb, Mar, etc.). As it is now, its a string and will sort like a string (e.g Apr, Aug, Dec, Feb, etc.)

 

[bd528]

Below is the SQL you can use to get the Running Total in a query:

SELECT Main.BDM_Name, Main.xMonth, Main.Original, (SELECT SUM(Original) FROM YourDataSourceName WHERE BDM_Name=Main.BDM_Name AND xMonth<=Main.xMonth) AS RunningTotal
FROM YourDataSourceName AS Main;

2 notes:

1. You will need to replace instances of "YourDataSourceName" in the code above with the actual name of the data source you will be building this on.

2. You will need to make xMonth an actual date. If you make it a date, it will then sort like a date (Jan, Feb, Mar, etc.). As it is now, its a string and will sort like a string (e.g Apr, Aug, Dec, Feb, etc.)

Thank you.