Count values in one table based upon values in another.

[sbbrown9924]

Can you help? I am writing a report where I have 2 tables, SWcodes and SWlog with fields SWcodes.new_code and SWlog.service1 respectively. I would like to be able to count the number of times the value in SWcodes.new_code has been entered in SWlog.service1. Thanks.


SWcodes

id new_code description
1 1000 assessment
2 1010 treatment
3 1020 referral

SWlog

id patient_name service1
1 John Doe 1000
2 Jane Doe 1010
3 Jack Black 1000
4 James Green 1020

Report:

Service Totals:
Assessment 2
Treatment 1
Referral 1

You get the idea. Any help would be appreciated. I am a newbie to ASP VBscript and SQL

 

[dhookom]

Create a totals query with your two tables. Join the New_code field to Service1. Select View->Totals and add the Description field to the query grid. Set this column to "Group By". Add the Service1 field and select "Count".


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sbbrown9924]

dhookem;

Hey thanks. I was able to generate a query according to your suggestion in the query wizard, then cut and paste it into my web page.

It appears that an alias can't be used in an ORDER BY statement - do you know of a workaround for that?

 

[dhookom]

I'm having a little problem seeing your SQL view. Do you mind sharing it?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

ususally you would just use the expression instead of the alias:

SELECT ID, Count(ID) As Counter From TableName
ORDER BY Count(ID)

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[lespaul]

with the group by clause too!!!

SELECT ID, COUNT(ID) As Counter From TableName
GROUP BY ID
ORDER BY COUNT(ID)

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[sbbrown9924]

Sure - here you go.

SELECT DISTINCTROW SocialWorkCodes.service_type, SocialWorkCodes.description, Count(*) AS [CountOfSocialWorkLog]
FROM SocialWorkLog INNER JOIN SocialWorkCodes ON SocialWorkLog.Service1 = SocialWorkCodes.new_code
GROUP BY SocialWorkCodes.service_type, SocialWorkCodes.description;

This query works great - it generates a count of all SocialWorkCodes found in the Service1 field of SocialWorkLog. From here I'd like to be able to do an ORDER BY on the CountofSocialWorkLog alias.

 

[lespaul]

Like I showed above, use the function, not the alias

SELECT DISTINCTROW SocialWorkCodes.service_type, SocialWorkCodes.description, Count(*) AS [CountOfSocialWorkLog]
FROM SocialWorkLog INNER JOIN SocialWorkCodes ON SocialWorkLog.Service1 = SocialWorkCodes.new_code
GROUP BY SocialWorkCodes.service_type, SocialWorkCodes.description
ORDER BY Count(*);

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[sbbrown9924]

lespaul;

Ok - that was too easy - I'm embarrassed. Thanks for your help on this.

 

[lespaul]

glad to assist! hope it's working now!

les

 

[sbbrown9924]

That query is working fine now.

I need to combine that query with a second one that pulls records from SocialWorkLog between a DateBegin and an DateEnd. This requires the use of an additional field, ServiceDate. I read some of your advice regarding UNION statements and placeholders to join queries and tried to integrate this second query in, but I am having difficulty because of the field mismatches. Will a UNION work in this instance or is there a better way to combine the two queries. Here they are:

Query 1:
"SELECT DISTINCTROW SocialWorkCodes.service_type, SocialWorkCodes.description, Count(*) AS [CountOfSocialWorkLog] FROM SocialWorkLog INNER JOIN SocialWorkCodes ON SocialWorkLog.Service1 = SocialWorkCodes.new_code GROUP BY SocialWorkCodes.service_type, SocialWorkCodes.description ORDER BY Count(*)"

Query 2:

"SELECT * FROM SocialWorkLog WHERE ServiceDate>#" & DateBegin & "# AND ServiceDate <#" & DateEnd & _
"# ORDER BY Servicedate

 

[lespaul]

It depends on what information you are trying to select from the SocialWorkLog table. Do you really want all the fields from that table? What are all the fields in the table? In order for a UNION query to work the SELECT statement has to have the same number of fields in it. So with your first query only selecting 3 fields your second query can only return 3 fields. If there are more fields in one query than in the other you have to create place holders:

SELECT EmpNum, EmpName, EmpHireDate, "" As EmpTermDate FROM CurrentEmployees
UNION
SELECT EmpNum, EmpName, EmpHireDate, EmpTermDate FROM TerminatedEmployees

(this is a horrible example since it's not really normalized, but it can get the point across!) Employees in the CurrentEmployees wouldn't have a TerminationDate field, so in order to return that information in the second query, there has to be a "place" for the information.

HTH

Leslie

 

[sbbrown9924]

Leslie;

From what I've read I think what I need to use is a sub-query since the results of both queries come from the same table.

The gist of what I need to do is perform a query where I select all records that exist in the SocialWorkLog between two dates, then provide a list of services provided (from SocialWorkCodes)regarding how many times these services were provided in that time period.

SocialWorkLog consists of these fields (there are more, but for purposes of this example I've pared it down):

ID
ServiceDate
Lastname
Firstname
Service1

This is the query to select all records between two dates:

SELECT SocialWorkLog.ServiceDate FROM SocialWorkLog WHERE Servicedate BETWEEN #" & DateBegin & "# AND #" & DateEnd & "#.

I then need to provide a count of each service that was provided during the specified time period.

SocialWorkCodes fields are: (these are the services that have provided to clients)
ID1
code
new_code
service_type
description

Here is the query to total each service that was provided:

SELECT DISTINCTROW SocialWorkCodes.service_type, SocialWorkCodes.description, Count(*) AS [CountOfSocialWorkLog] FROM SocialWorkLog INNER JOIN SocialWorkCodes ON SocialWorkLog.Service1 = SocialWorkCodes.new_code GROUP BY SocialWorkCodes.service_type, SocialWorkCodes.description ORDER BY Count(*)

Each of these queries works individually.

 

[sbbrown9924]

Leslie;

I found it. I needed to put in a WHERE condition in the join statement. Its working right now although I am still having some difficulty passing DateBegin and DateEnd between pages. In the meantime I can hard code values into the variables and get a valid result.

SELECT DISTINCTROW SocialWorkCodes.service_type, SocialWorkCodes.description, Count(*) AS [CountOfSocialWorkLog] FROM SocialWorkLog INNER JOIN SocialWorkCodes ON SocialWorkLog.Service1 = SocialWorkCodes.new_code WHERE (ServiceDate BETWEEN #" & DateBegin & "# AND #" & DateEnd & "#) GROUP BY SocialWorkCodes.service_type, SocialWorkCodes.description ORDER BY Count(*)

 

[lespaul]

I'm glad you got it working, but the position of the WHERE clause is exactly where it's suppose to be. The structure is:

SELECT field list
FROM table name
JOIN conditions
WHERE conditions
GROUP BY field list
HAVING conditions
ORDER BY field list

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual