Querying and totalling 2

[kleinicus]

I have an Access database with one table called "Transactions." This table contains an AutoNum primary key, TransactionDate, TransactionAmount, EmployeeID, and CompanyID fields. I am trying to create a query that will accept a date range input from the user, calculate the sum of TransactionAmount for that date range, and then group the results by the month. What I'm after here is monthly totals.
I've gone through the query creation wizard and it will group all of the sums by month, but it automatically converts the display of the month to MMMM, YYYY format. This results in an alphabetical listing of the months, i.e. April, 2006 being displayed, then April 2007, etc. I would prefer this to be in chronological order, rather than alphabetical.
I have tried to force the wizard-generated query to accept a date range as input, but then the query displays no data at all.
I have also tried to create a query from scratch that accepts the date input and generates the sum of transactions. This results in the data being grouped by the day, rather than month, on which the transactions occurred.
I know there is probably a way to do this with SQL statements, but I'm not programming savvy. Any help would be appreciated.

Thanks,

SK

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kleinicus]

Here's an example that totals transaction amounts and groups in months, but it sorts in alphabetical order, instead of chronological...

SELECT DISTINCTROW Format$(TransactionsTable.Date,'mmmm yyyy') AS [Date By Month], Count(TransactionsTable.Amount) AS [Total Policies]
FROM TransactionsTable
GROUP BY Format$(TransactionsTable.Date,'mmmm yyyy'), TransactionsTable.Agent, Year(TransactionsTable.Date)*12+DatePart('m',TransactionsTable.Date)-1
HAVING (((TransactionsTable.Agent)="S"));


Here's an example that totals transaction amounts but groups it by the date rather than the month...

SELECT TransactionsTable.Date, Sum(TransactionsTable.Amount) AS SumOfAmount
FROM TransactionsTable
GROUP BY TransactionsTable.Date
HAVING (((TransactionsTable.Date) Between [Enter begin date] And [Enter end date]));


Thanks again for your help.

SK

 

[PHV]

What about this ?
SELECT Format$([Date],'mmmm yyyy') AS [Date By Month], Count(*) AS [Total Policies], Sum(Amount) AS SumOfAmount
FROM TransactionsTable
WHERE Agent = 'S' AND [Date] Between [Enter begin date] And [Enter end date]
GROUP BY Format$([Date],'mmmm yyyy'), Format$([Date],'yyyymm')
ORDER BY Format$([Date],'yyyymm')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kleinicus]

That's great! Thanks so much!

SK

 

[kleinicus]

Now I have another query I need to customize.

This query should accept an agent code and date range, and return the agent code, the total transactions (count of) and the total $ amount of transactions (sum of). This is basically to get a single row showing the total of each of those for the given date range. Here's what I have so far...

SELECT TransactionsTable.Agent, TransactionsTable.Date, Count(TransactionsTable.Amount) AS [Total Policies], Sum(TransactionsTable.Amount) AS [Total $]
FROM TransactionsTable
GROUP BY TransactionsTable.Agent, TransactionsTable.Date
HAVING (((TransactionsTable.Agent)=[Enter agent code]) AND ((TransactionsTable.Date) Between [Enter begin date] And [Enter end date]))
ORDER BY TransactionsTable.Agent;

Once again, the GROUP BY statement groups the results by the date they occurred, but I just want a single result displaying the sum and count of transactions for the given date range. It is not necessary to display the date range with the results. It could come in handy, but it's not as important as the single result goal.

Thanks for your help.

SK

 

[VRoscioli]

Why not just remove both references to the date:

SELECT TransactionsTable.Agent, Count(TransactionsTable.Amount) AS [Total Policies], Sum(TransactionsTable.Amount) AS [Total $]
FROM TransactionsTable
GROUP BY TransactionsTable.Agent
HAVING (((TransactionsTable.Agent)=[Enter agent code]) AND ((TransactionsTable.Date) Between [Enter begin date] And [Enter end date]))
ORDER BY TransactionsTable.Agent;

Or maybe displaying the most recent date:

SELECT TransactionsTable.Agent, Max(TransactionsTable.Date), Count(TransactionsTable.Amount) AS [Total Policies], Sum(TransactionsTable.Amount) AS [Total $]
FROM TransactionsTable
GROUP BY TransactionsTable.Agent
HAVING (((TransactionsTable.Agent)=[Enter agent code]) AND ((TransactionsTable.Date) Between [Enter begin date] And [Enter end date]))
ORDER BY TransactionsTable.Agent;

-V

 

[kleinicus]

Thanks for your post, V. I tried both of the code samples you provided, but they did not work. However, it did get give me the idea to try some different things with the date field. Here's what I eneded up with...

SELECT TransactionsTable.Agent, Count(TransactionsTable.Amount) AS [Total Policies], Sum(TransactionsTable.Amount) AS [Total $]
FROM TransactionsTable
WHERE (((TransactionsTable.Date) Between [Enter begin date] And [Enter end date]))
GROUP BY TransactionsTable.Agent
HAVING (((TransactionsTable.Agent)=[Enter agent code]))
ORDER BY TransactionsTable.Agent;

Unfortunately, it doesn't display a date range, but it does meet my primary goal of having a single total for that date range.

I'm still giving a star on this one because it was helpful, just not in the traditional way. : )
Thanks!

BTW, what is the tag for code samples? I did a search for TGML on the forums and I couldn't find how to isolate my code in a box like you did.

 

[PHV]

to get this:

my code here

type this:
[ignore]

my code here

[/ignore]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VRoscioli]

If you want it to display the dates of the first and last transactions in your range, why not:

SELECT TransactionsTable.Agent, Min(TransactionsTable.Date) AS First_Transaction, Max(TransactionsTable.Date) AS Last_Transaction, Count(TransactionsTable.Amount) AS [Total Policies], Sum(TransactionsTable.Amount) AS [Total $]
FROM TransactionsTable
WHERE (((TransactionsTable.Date) Between [Enter begin date] And [Enter end date]))
GROUP BY TransactionsTable.Agent
HAVING (((TransactionsTable.Agent)=[Enter agent code]))
ORDER BY TransactionsTable.Agent;

Or if you want to display the input range, I think the following works:

SELECT TransactionsTable.Agent, [Enter begin date] AS First_Transaction, [Enter end date] AS Last_Transaction, Count(TransactionsTable.Amount) AS [Total Policies], Sum(TransactionsTable.Amount) AS [Total $]
FROM TransactionsTable
WHERE (((TransactionsTable.Date) Between [Enter begin date] And [Enter end date]))
GROUP BY TransactionsTable.Agent
HAVING (((TransactionsTable.Agent)=[Enter agent code]))
ORDER BY TransactionsTable.Agent;

-V

 

[kleinicus]

Thanks V. I used the second one. It works great!

Thanks PH for the TGML info too.