CODEWITHSUNDEEP
ms-accessms-access-2003
Alistair Weir
Alistair Weir

Reputation: 1849

Match field for each record to specific query record

I have 1 table of the form:

Study Name ¦ xx ¦ yy ¦ zz ¦ Target No ¦ Current No.

This is called study_information

and another of the form:

Study Name ¦ Patient Name ¦ Date ¦ Time

This is called patient_visits

I have created a query that counts the number of patients for each study name:

SELECT patient_visits.study_name, Count(patient_visits.study_name) AS [Count]
FROM patient_visits
GROUP BY patient_visits.study_name;

Which gives output for example of:

Study Name ¦ Count
Study 1    ¦   4
Study 2    ¦   7
Study 3    ¦   11

i.e. there were 4 records (4 patients) in the patient_visits table for Study 1.

What I want to do is display the count in the Current No. field of the study_information table. I have tried setting the datatype of the current no field to lookup the query but it produces a drop down list isntead of matching the study names. I also created a direct relationship betweent the count field of the query and current_no but got the same result.

Hope this is clear; in pseudo:

study_information.current_no = COUNT patient_visits.study_name where     study_information.study_name = patient_visits.study_name

How do I link these two values up?

Upvotes: 1

Views: 463

Answers (1)

HansUp
HansUp

Reputation: 97131

If your GROUP BY query is named qryVisitsPerStudy, this should give you what you need. It doesn't store the counts in a [Current No.] field in study_information like you seemed to want. But that is a good thing because you won't need to update stored count values whenever the data contained in patient_visits changes.

SELECT 
    s.[Study Name],
    s.xx,
    s.yy,
    s.zz,
    s.[Target No],
    q.[Count] AS [Current No.]
FROM
    study_information AS s
    INNER JOIN qryVisitsPerStudy AS q
    ON s.[Study Name] = q.study_name

Upvotes: 1

Related Questions