Solved Need help please (1 Viewer)

[jjgolob]

Hi. Welcome to AWF!

I am looking for help with an Access database. I have a table With multiple fields, but I only need a few to create a report. The fields I have to use are Log In Date, Log Out Date Actual Dollar Amount, and Agent.

Through two queries, I have created a Log In Monthly Count query which has isolated the Count of Log In Date, combined with a Month listing (the table has numerical dates and I need alpha months) and year. Here is the code.

SELECT [Log In Count].PA, [Log In Count].[CountOfLog In], [Month Listing].Month, [Log In Count].[Log In Year]
FROM [Month Listing] INNER JOIN [Log In Count] ON [Month Listing].ID = [Log In Count].[Log In Month];

I have done the same with a Log Out Totals query.

SELECT [Log Out Count].PA, [Log Out Count].[CountOfLog Out], [Log Out Count].[SumOfActual Dollar Amt], [Month Listing].Month, [Log Out Count].[Log out Year]
FROM [Month Listing] INNER JOIN [Log Out Count] ON [Month Listing].ID = [Log Out Count].[Log Out Month];

Both of these work great individually, but when I combine them, I get multiple lines for each month. For example, one agent has a log in count for 5 months, so the query duplicates those 5 log in counts for each of the Count of Log Out Dates. This occurs in the report as well. Here is the coding for my combining query.

SELECT [Log In Monthly Count].PA, [Log In Monthly Count].[CountOfLog In], [Log Out Totals].[CountOfLog Out], [Log Out Totals].[SumOfActual Dollar Amt], [Log Out Totals].Month, [Log Out Totals].[Log out Year]
FROM [Log In Monthly Count] INNER JOIN [Log Out Totals] ON [Log In Monthly Count].PA = [Log Out Totals].PA;

Any ideas how to get rid of the duplicates? My report is grouped by Month, and sorted by agent (PA)

 

[theDBguy]

Hi. Just FYI, I moved your thread out of the Introduction Forum.

You might consider posting a sample copy of your db with test data when/if you can.

 

[jjgolob]

Log In	PA	Log Out	Actual Dollar Amt
4/19/2021	AL	6/24/2021	$45,790.12
4/21/2021	AL	6/15/2021	$15,000.00
4/21/2021	AL	6/15/2021	$30,284.48
4/23/2021	AL	6/16/2021	$20,606.00
4/23/2021	AL	6/24/2021	$257,686.43
4/27/2021	AL	6/15/2021	$7,500.00
4/30/2021	AL	6/15/2021	$8,702.68
4/30/2021	AL	6/23/2021	$37,000.00
5/3/2021	AL	6/15/2021	$17,635.57
5/10/2021	AL	6/15/2021	$14,188.50
5/10/2021	AL	6/16/2021	$7,038.00
5/12/2021	AL	6/15/2021	$0.00
5/12/2021	AL	6/15/2021	$8,096.61
5/24/2021	AL	6/10/2021	$7,925.00
5/24/2021	AL	6/15/2021	$7,673.88
5/25/2021	AL	6/15/2021	$14,323.22
5/25/2021	AL	6/16/2021	$5,199.00
5/28/2021	AL	7/29/2021	$0.00
6/1/2021	AL	6/16/2021	$14,030.98
6/4/2021	AL	6/8/2021	$21,450.35
6/4/2021	AL	6/15/2021	$11,196.00
6/9/2021	AL	6/21/2021	$7,096.91
6/14/2021	AL	6/14/2021	$54,176.10
6/14/2021	AL	6/29/2021	$16,369.00
6/16/2021	AL	6/24/2021	$9,976.00
6/22/2021	AL	6/24/2021	$9,719.00
6/22/2021	AL	6/24/2021	$27,672.96
6/24/2021	AL	7/12/2021	$50,000.00
6/24/2021	AL	7/13/2021	$22,838.40
6/24/2021	AL	7/13/2021	$7,217.00
7/15/2021	AL	7/27/2021	$10,957.87
7/16/2021	AL	7/27/2021	$18,059.05
7/22/2021	AL	7/27/2021	$16,391.00
7/22/2021	AL	7/27/2021	$6,902.01
7/22/2021	AL	8/5/2021	$83,629.00
7/28/2021	AL	8/10/2021	$18,300.00
7/30/2021	AL	8/5/2021	$24,200.00
7/30/2021	AL	8/11/2021	$208,446.75
8/3/2021	AL	8/4/2021	$9,115.00

Here is my test data, and below is my query results.

CountOfLog In	CountOfLog Out	SumOfActual Dollar Amt	Month	Log out Year	PA
2	5	$343,690.75	AUGUST	2021	AL
8	5	$343,690.75	AUGUST	2021	AL
12	5	$343,690.75	AUGUST	2021	AL
10	5	$343,690.75	AUGUST	2021	AL
8	5	$343,690.75	AUGUST	2021	AL
2	8	$132,365.33	JULY	2021	AL
8	8	$132,365.33	JULY	2021	AL
12	8	$132,365.33	JULY	2021	AL
10	8	$132,365.33	JULY	2021	AL
8	8	$132,365.33	JULY	2021	AL
2	26	$676,336.79	JUNE	2021	AL
8	26	$676,336.79	JUNE	2021	AL
12	26	$676,336.79	JUNE	2021	AL
10	26	$676,336.79	JUNE	2021	AL
8	26	$676,336.79	JUNE	2021	AL

So my query should return row 1, 7, and 13 and none of the others.

 

[theDBguy]

Hi. Thanks for the additional information. Just one quick question, how do you determine what to Sum for the amount? For example, you have some records with a login in July with a corresponding logout in Aug. So, into which month do those dollar amounts get summed?

 

[jjgolob]

The sum is based upon the log out month.

 

[theDBguy]

Okay, thanks. Let me give it a try.

qryLogIn
SELECT Format(LogIn, "mmmm") AS LogMonth, Count(*) AS TotalLogin
FROM TableName
GROUP BY Format(LogIn, "mmmm")

qryLogOut
SELECT Format(LogOut, "mmmm") AS LogMonth, Count(*) AS TotalLogOut, Sum(AmountField) AS TotalAmt
FROM TableName
GROUP BY Format(LogOut, "mmmm")

qryFinalCount
SELECT Q1.TotalLogin, Q2.TotalLogOut, Q2.TotalAmt, Q2.LogMonth
FROM qryLogin AS Q1
INNER JOIN qryLogOut AS Q2
ON Q1.LogMonth=Q2.LogMonth

I didn't include the PA column, because I am not sure if that value is always the same or if it changes. Try the above first and make sure it's counting the records correctly. Then, we can go from there.

Hope that helps...

 

[jjgolob]

Here is my query and the error I am getting.

SELECT
FROM [2022 Tracker]
SELECT Format(Log In, "mmmm") AS LogMonth, Count(*) AS TotalLogin
FROM 2022 Tracker
GROUP BY Format(Log In, "mmmm");

Error Message:
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

2022 Tracker is the table name.

 

[MajP]

SELECT FROM

It thinks that FROM is the name of a field since it follows a select statement.
Probably you meant

SELECT Format([Log In], "mmmm") AS LogMonth, Count(*) AS TotalLogin
FROM 2022 Tracker
GROUP BY Format([Log In], "mmmm")

which more resembles what was previously suggested.

 

[theDBguy]

Here is my query and the error I am getting.

SELECT
FROM [2022 Tracker]
SELECT Format(Log In, "mmmm") AS LogMonth, Count(*) AS TotalLogin
FROM 2022 Tracker
GROUP BY Format(Log In, "mmmm");

Error Message:
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

2022 Tracker is the table name.

Hi. When you have spaces in table or field names, make sure you enclose them in square brackets in your SQL statements.

 

[jjgolob]

Ok, I had to put brackets around the table name, but it counted just fine. Did the same with Login Out and it worked, but when I create the total query, I get:

Syntax error in FROM clause. Here is the coding.

SELECT [qrylog in.TotalLogin], [qrylog out.TotalLogOut], [qrylog out.TotalAmt], [qrylog out.LogMonth]
FROM qryLog in AS qrylog in
INNER JOIN qryLogOut AS qrylog out
ON [qrylog in.LogMonth]=[qrylog out.LogMonth]

I was getting errors on the SELECT statements but when I put brackets in, those went away.

 

[theDBguy]

Ok, I had to put brackets around the table name, but it counted just fine. Did the same with Login Out and it worked, but when I create the total query, I get:

Syntax error in FROM clause. Here is the coding.

SELECT [qrylog in.TotalLogin], [qrylog out.TotalLogOut], [qrylog out.TotalAmt], [qrylog out.LogMonth]
FROM qryLog in AS qrylog in
INNER JOIN qryLogOut AS qrylog out
ON [qrylog in.LogMonth]=[qrylog out.LogMonth]

I was getting errors on the SELECT statements but when I put brackets in, those went away.

Best approach is to avoid using spaces when naming your objects.

This [qrylog in.TotalLogin] should be more like this: [qrylog in].TotalLogin. Same for the rest of them.

 

[jjgolob]

Still getting the syntax error in the From clause

 

[jjgolob]

I changed the names of the queries and now it works. Now can we add the PA to the queries?

 

[theDBguy]

I changed the names of the queries and now it works. Now can we add the PA to the queries?

Please show us the result...

 

[The_Doc_Man]

If your FROM clause includes FROM 2022 Tracker then your problem is missing brackets. Maybe From [2022 Tracker] based on what you showed us in your post #7.

Spaces in names are just not worth the trouble most of the time. Run the elements of the names together (like, from an earlier post, use LogIn vs Log In (also from post #7, the Format function's input) and you can get by without bracketing most of the time.

 

[jjgolob]

qryFinalCount qryFinalCount

TotalLogin	TotalLogOut	TotalAmt	LogMonth
81​	3​	$365,247.25​	April
12​	25​	$882,363.75​	August
73​	47​	$3,478,317.85​	July
29​	161​	$32,805,803.83​	June
59​	3​	$389,878.00​	March
13​	7​	$164,716.92​	May

This is the result of the qryFinalCount and it looks great. Now I just need to add the PA(purchasing agent) to the queries please,

 

[theDBguy]

qryFinalCount qryFinalCount

TotalLogin	TotalLogOut	TotalAmt	LogMonth
81​	3​	$365,247.25​	April
12​	25​	$882,363.75​	August
73​	47​	$3,478,317.85​	July
29​	161​	$32,805,803.83​	June
59​	3​	$389,878.00​	March
13​	7​	$164,716.92​	May

This is the result of the qryFinalCount and it looks great. Now I just need to add the PA(purchasing agent) to the queries please,

Okay, thanks for showing us the result. With regards to PA, would there be different PA involved for each month?

 

[jjgolob]

I have a total of 8 purchasing agents and I need their total log ins, log outs, and total dollars per month. When I create my report it will be grouped by Month and I will have to add a conditional to the query to ask for the month of the report to be run.

 

[theDBguy]

I have a total of 8 purchasing agents and I need their total log ins, log outs, and total dollars per month. When I create my report it will be grouped by Month and I will have to add a conditional to the query to ask for the month of the report to be run.

That wasn't my question. It's similar to my original question about a separate login and logout in the same month. I was wondering if within the same month, there would be different PAs. If so, we may have to rethink the whole thing (or parts of it).

 

[jjgolob]

Yes, there would be different PAs each month.