asked on

Access Query To Count True/False Values

Hi everyone, I have two tables setup in my access database. The first one is a case history table. It lists such columns as Case #, Aging Parents (Yes/No), Energy (Yes/No) and etc.

I then have another table that is a log of the activity that has occurred for each case. Some cases can have multiple entries.

When I go to create a query that will sum the number of Aging Parents cases, if there are multiple entries in my case table it will count the same case multiple times for one category- when in all reality it's just the same case. Does anyone know how I can sum each column based on each case number and not each entry in my log table?

There is a join between my data table and log table, it's joined by the Case #.

jamesrh

Is the sum the only thing you want from this particular query or are you getting data that is actually in the log table in this same query? If you only need the sum, then you don't have to join the 2 tables in the query just because they are joined in the db.

Anthony

ASKER

I'm using a field from the log table that allows me to input a date criteria...

I know when I take the date criteria out, it works; however, I've then lost all control as to what should be displayed.

mbizup

Give this a try:

SELECT c.[Case #], Sum([Aging Parents]) AS TotalAgingParents, Sum([Energy]) AS TotalEnergy
FROM [case history] AS c INNER JOIN [log table] AS l ON c.[Case #] = l.[Case #]
WHERE [Your Date Field] BETWEEN [Start Date] AND [End Date]
GROUP BY c.[Case #]

Open in new window

The idea is to join your tables to get the date criteria... but only select fields that you are summing. Any other fields will cause the results you describe.

Anthony

ASKER

Ok this is what I have:

SELECT c.[CASE #] AS Expr1, Sum(c.[Aging Parents]) AS TotalAgingParents, Sum(c.[Child]) AS TotalChild
FROM tblDATA AS c INNER JOIN tblLOG AS l ON c.[Case #] = l.[Case #]
WHERE ((([DATE])>=[Forms]![RUNDTEf].[SPEC_START] And <=[Forms]![RUNDTEf].[SPEC_END]))
GROUP BY c.[CASE #];

Open in new window

I'm getting a syntax error on the Where clause...

jamesrh

SELECT c.[CASE #] AS Expr1, Sum(c.[Aging Parents]) AS TotalAgingParents, Sum(c.[Child]) AS TotalChild
FROM tblDATA AS c INNER JOIN tblLOG AS l ON c.[Case #] = l.[Case #]
WHERE ((([DATE])>=[Forms]![RUNDTEf].[SPEC_START] And [DATE] <=[Forms]![RUNDTEf].[SPEC_END]))
GROUP BY c.[CASE #];

You must repeat the name of the field being compared to. In this case, Date

Anthony

ASKER

OK, I got the formula to work; however, I'm still getting the case 1 counted twice because in the log table it has two entries.

Here is the current equation:

SELECT c.[CASE #] AS [CASE#], Sum(c.[Aging Parents]) AS TotalAgingParents, Sum(c.Child) AS TotalChild, Count(c.[SOCIAL SECURITY/MEDICARE]) AS SsMedTotal
FROM tblDATA AS c INNER JOIN tblLOG AS l ON c.[Case #] = l.[Case #]
WHERE (((l.[DAY])>=[Forms]![RUNDTEf].[SPEC_START] And (l.[DAY])<=[Forms]![RUNDTEf].[SPEC_END]))
GROUP BY c.[CASE #];

Open in new window

Anthony

ASKER

See the problem is that the log table has two entries for one case. That's why instead of getting the total of 2 for TotalAgingParents, I'm getting 3.

Anthony

ASKER

OK, I was able to solve my problem to an extent. I added a field to my log tabled called "logged". This is a yes/no field and is defaulted to No. When someone enters more dates, the field changes to logged. It's not until another row of logging is added that it changes.

mbizup

Try this (you shouldn't need the extra field):

SELECT c.[CASE #] AS [CASE#], Sum(c.[Aging Parents]) AS TotalAgingParents, Sum(c.Child) AS TotalChild, Count(c.[SOCIAL SECURITY/MEDICARE]) AS SsMedTotal
FROM tblDATA AS c INNER JOIN (SELECT DISTINCT [CASE #] FROM tblLOG WHERE  [DAY] >=[Forms]![RUNDTEf].[SPEC_START] And l.[DAY]<=[Forms]![RUNDTEf].[SPEC_END])  AS l ON c.[Case #] = l.[Case #]
GROUP BY c.[CASE #];

Open in new window

Anthony

ASKER

When I try that equation, it's looking for the I.DAY field, it can't find it.

It's looking for it as a parameter.

mbizup

Anthony6890,

Can you explain the grading? jamesrh's comment was almost entirely based on my earlier post.

Anthony

ASKER

MB, I didn't realize that it was someone else who altered your original formula.

Sorry about that. I'll have the points adjusted.

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony

ASKER

MB,

That worked like a charm! I just adjusted it to remove the CASE # column. I want the totals to just cumulatively, not for each Case- that wasn't hard at all.

I appreciate all your help and have adjusted the points as such.

-AM

mbizup

Glad that worked out :)