Count Query (1 Viewer)

Number11

Member
Local time
Today, 23:48
Joined
Jan 29, 2020
Messages
479
So i need to have a query setup to just count the total number calls made but only count the total number as 1 unit (individual customer account)

Fields i have available within a table are

Account Number
Date of call
Time of call
Week Number
Notes

so looking for the query to show like a pivot

Account Number
Date of call
Time of call
Week number
Count of all calls to individual customer (so if 3 calls attempted in the same week just count as 1 call count)


Totally lost please can you help
 

Minty

AWF VIP
Local time
Today, 23:48
Joined
Jul 26, 2013
Messages
9,288
Write a query that pulls in the core data. Account Number, WeekNumber

Once that is correct change the query type to crosstab.

Put the account number down as a Row Heading, The Weeknumber as a column heading.
Now add the weeknumber again and change the crosstab properties to Count, and value
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:48
Joined
May 7, 2009
Messages
16,453
you need a total query:

select [account number], datepart("ww",[date of call]), count("1") as countofcalls from
yourtable group by [account number], datepart("ww",[date of call])
 

Number11

Member
Local time
Today, 23:48
Joined
Jan 29, 2020
Messages
479
you need a total query:

select [account number], datepart("ww",[date of call]), count("1") as countofcalls from
yourtable group by [account number], datepart("ww",[date of call])
ok thanks so i have this now working and the count of call is working, but I am seing within the count of call 2,3,4 ect so i guess i just need to add a further account on account number now
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:48
Joined
May 7, 2009
Messages
16,453
you need 2 queries:

1. Query1
select [account number], datepart("ww",[date of call]) from
yourtable group by [account number], datepart("ww",[date of call])

2. the final query. create a Crosstab query on Query1.
 

plog

Banishment Pending
Local time
Today, 17:48
Joined
May 11, 2011
Messages
10,930
You should fix your table, you have 2 unnecessary fields.

Date of call and time of call can go into one field--its called a Date/Time field for a reason. It can hold both pieces of data simultaneously.

Second, Week umber can be calculated from the date. Sort of like Age, you just need to know the date of birth and you can use logic to obtain age. Since you know the date of the call you can use logic to obtain the week number.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:48
Joined
Feb 19, 2002
Messages
36,387
1. This is a two part aggregation when you are using Access. First you need to find all the users who were called during the time period. Then the outer query counts the number of "customers" in the set of calls.
2. You cannot aggregate properly when you keep details in the result set. So when there are multiple calls to a client, which information do you want to see? First call, last call?
 

Users who are viewing this thread

Top Bottom