Solved Need help please (1 Viewer)

[theDBguy]

Yes, there would be different PAs each month.

Okay, using a small set of sample data as follows:

Login    Logout    Amt    PA
Jan      Jan       $1     CA
Jan      Feb       $1     CA
Jan      Feb       $1     WA
Feb      Feb       $1     WA
Feb      Feb       $1     NY

Can you please tell us the correct result you're expecting from that? Thanks!

 

[jjgolob]

It should look something like this.

Monthly Totals Monthly Totals

PA	CountOfLog In	CountOfLog Out	SumOfActual Dollar Amt	Month	Log out Year
AL	12​	26​	$676,336.79​	JUNE	2021​
AL	8​	8​	$132,365.33​	JULY	2021​
AL	2​	5​	$343,690.75​	AUGUST	2021​
EC	10​	52​	$2,216,192.66​	JUNE	2021​
EC	38​	24​	$2,985,144.22​	JULY	2021​
EC	3​	9​	$312,137.40​	AUGUST	2021​
JG	2​	69​	$27,581,284.60​	JUNE	2021​
JG	20​	10​	$182,771.30​	JULY	2021​
JG	6​	8​	$102,259.60​	AUGUST	2021​
MP	3​	3​	$389,878.00​	MARCH	2021​
MP	1​	5​	$152,128.92​	MAY	2021​
MP	1​	5​	$350,982.42​	JUNE	2021​
MP	6​	3​	$18,037.00​	JULY	2021​
MS	4​	3​	$365,247.25​	APRIL	2021​
MS	2​	4​	$1,682,302.90​	JUNE	2021​
MS	1​	1​	$60,000.00​	AUGUST	2021​
SL	2​	5​	$298,704.46​	JUNE	2021​

 

[jjgolob]

I actually stumbled onto the solution by tying the PA and the Month together. That ended up giving me a result just like above, which I manually created in EXCEL.

Now my next question. When I create a report, I group my month, but the report shows all the months in alphabetical order and I need them in chronological order. How do I do that?

 

[theDBguy]

I actually stumbled onto the solution by tying the PA and the Month together. That ended up giving me a result just like above, which I manually created in EXCEL.

Now my next question. When I create a report, I group my month, but the report shows all the months in alphabetical order and I need them in chronological order. How do I do that?

Hi. Glad to hear you got it sorted out. To sort the months in chronological order, you can use the Month Listing ID column for your sort field.

 

[jjgolob]

Do I bring that in via the query?

 

[theDBguy]

Do I bring that in via the query?

Yes!

 

[jjgolob]

Ok, made a minor change in my query and report and it works great now!!!

Thank you for all of your help. I really appreciate it.

 

[theDBguy]

Ok, made a minor change in my query and report and it works great now!!!

Thank you for all of your help. I really appreciate it.

You're welcome. We're all happy to assist. Good luck with your project.

 

[Pat Hartman]

Select PA, Format([Log Out], "yyyy mmm") as OutMonth, Sum([Actual Dollar Amt]) as TotAmt, Count(*) As Logins
From [2022 Tracker]
Group by PA, Format([Log Out], "yyyy mmm")