Problem with Query and count/sum/totals/calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi - I am comfortable using Access to run basic queries, but have had a lot of
trouble getting equivalent functionality in Access to match what I do every
day in Excel. Here is the scenario:

I have a dataset with the following attributes:

User Acct. # TimeConnected Timeout
aa 111 1 0
bb 222 1 0
cc 333 2 0
dd 444 5 0
aa 111 2 0
ee 555 1 0
cc 333 30 1
ff 666 1 0
ff 666 4 0
gg 666 1 0

In Excel, I then take this list, run a filter to give me all unique users,
show their associated account number (can have multiple users on an account,
or a 1:1 relationship),
and then sum how many times that user showed up in the list, as well as how
many times the account shows up in the list (in the event that many users
from the same account appear), the total time connected, avg. time connected,
and the sum of how many timeouts occurred.

Again, this is simple in Excel. However, in Access, when I try to create a
query to calculate the appropriate fields, I can calculate only one field
when I 'Group By' user and acct#, and then every additional 'COUNT' function
appears the same as the previous one. If I try an even more complicated
query and join to another table with additional information about the user, I
start getting very odd results.

Can someone please point me in the right direction on how to get the first
part of this issue resolved, and then I'll tackle the joined tables issue?

Thanks,
Don
 
Access is not Excel. Excel is a spreadsheet and Access is a database. Access
uses SQL to perform queries on data. If you are familiar with SQL, what you
want is not difficult. If you post the SQL you are using, I might be able to
assist. In SQL, a query can read in the results from another query, so its
easy to approach problems step by step.
 
SELECT [Logs].USERID, [Logs].DOMAIN, [Logs].EMAIL, [Logs].ACCTNUMB,
Count([Logs].ACCTNUMB) AS [Account Connects], Count([Logs].EMAIL) AS [User
Connects]
FROM [Logs]
GROUP BY [Logs].USERID, [Logs].DOMAIN, [Logs].EMAIL, [Logs].ACCTNUMB;

When I run the above, both 'Account Connects' and 'User Connects' are the
same value, in all cases. I can validate against my Excel sheet and see
where the two are not equal, but I am not seeing that with this query. If I
then add other tables to the query, and continue the SQL logic above, the
numbers continue to duplicate. I'm adding two tables that show me whether
someone performed one of two actions, and we track those by account number
(as opposed to username). I do a couple of inner joins on these two tables,
trying to calculate, for each instance of a unique email in the Logs file,
how many times the account number for that email shows up in the Calls and
Emails tables. Unfortunately, all the numbers end up duplicating.

Color me mystified at this point.
 
Back
Top