Return 1st Client Meeting

[Keiron via AccessMonster.com]

I am having a few problems trying to work this one out.

I want to make a qry that returns the 1st meeting a client had within a 6
month period 1 oct 05 - 31 mar 06 that are in certain types of Care and
length of care.

I have 2 tables one stores the client information and another table that
stores all the client meetings.

In the qry I have
from the Client Table - tbl_LACChecklist
LACChecklistID
CareType - "HBC" is the type of care i need returned
TypeofCare - "FTC" is the length of care i need returned
From the meeting Table - tbl_RequestRCCP
MeetingDate - Between #1/10/2005# And #31/03/2006#

I have manually counted and there are 25 records that fall within the
criteria 17 of which are first time meetings and another 8 that are second
meetings. my problem is min returns 12 records and Max returns 17 records.
same if i use first or last when using Totals on the MeetingDate

i thought that using either value would return 17 seeing as thats the amount
that have at least 1 meeting. but max and last are the only ones that return
the correct total. i have played around with the joins but still returns the
same results.

Any help would be much appreciated.

Cheers
Keiron

 

[Tom Ellison]

Dear Keiron:

Please post the query you have so far, along with what is wrong with it.
That would be a much better starting point, along with the description you
have already given.

Tom Ellison

 

[Keiron via AccessMonster.com]

this one is using max returns 17 records which is correct.

SELECT tbl_LacChecklist.ChecklistID, tbl_LacChecklist.CareType,
tbl_LacChecklist.TypeOfCare, Max(tbl_RequestRCPP.MeetingDate) AS
MaxOfMeetingDate
FROM tbl_LacChecklist INNER JOIN tbl_RequestRCPP ON tbl_LacChecklist.
ChecklistID = tbl_RequestRCPP.ChecklistID
GROUP BY tbl_LacChecklist.ChecklistID, tbl_LacChecklist.CareType,
tbl_LacChecklist.TypeOfCare
HAVING (((tbl_LacChecklist.CareType)="hbc") AND ((tbl_LacChecklist.TypeOfCare)
="ftc") AND ((Max(tbl_RequestRCPP.MeetingDate)) Between #10/1/2005# And
#3/31/2006#));


this one is using min returns 12 records which is wrong.

SELECT tbl_LacChecklist.ChecklistID, tbl_LacChecklist.CareType,
tbl_LacChecklist.TypeOfCare, Min(tbl_RequestRCPP.MeetingDate) AS
MinOfMeetingDate
FROM tbl_LacChecklist INNER JOIN tbl_RequestRCPP ON tbl_LacChecklist.
ChecklistID = tbl_RequestRCPP.ChecklistID
GROUP BY tbl_LacChecklist.ChecklistID, tbl_LacChecklist.CareType,
tbl_LacChecklist.TypeOfCare
HAVING (((tbl_LacChecklist.CareType)="hbc") AND ((tbl_LacChecklist.TypeOfCare)
="ftc") AND ((Min(tbl_RequestRCPP.MeetingDate)) Between #10/1/2005# And
#3/31/2006#));

Dear Keiron:

Please post the query you have so far, along with what is wrong with it.
That would be a much better starting point, along with the description you
have already given.

Tom Ellison

I am having a few problems trying to work this one out.

[quoted text clipped - 30 lines]

Cheers
Keiron

 

[John Spencer]

It would be a great help if you posted the SQL text of your query - even if it
isn't giving you the desired results. It shows the fields involved, the tables
involved, and the relationships between the tables.

First thing I noticed is that your date strings are not in U.S. (MM/DD/YYYY)
format or in YYYY/MM/DD format. Access queries require this format for datestrings.

You need a query something like the following to get the first meeting date for
each client. Since CareType and TypeofCare seem to be associated with the
client table you don't need that in this query.

SELECT M.ClientID, Min(M.MeetingDate) as FirstMeeting
FROM tbl_RequestRCCP as M
WHERE M.MeetingDate Between #10/1/2005# and #3/31/2006#
GROUP BY M.ClientID

Then you can use that as a subquery or a nested query to get the other details
you want.

Nested Query: Save the above as qFirstMeeting and then join that to your
existing query on ClientID and MeetingDate.

 

[Keiron via AccessMonster.com]

I just noticed in the sql text that the dates are all wrong thanks for that
ill try and fix it and see if that works.

New to access and sql so its a slow proccess for me.

Cheers
Keiron

It would be a great help if you posted the SQL text of your query - even if it
isn't giving you the desired results. It shows the fields involved, the tables
involved, and the relationships between the tables.

First thing I noticed is that your date strings are not in U.S. (MM/DD/YYYY)
format or in YYYY/MM/DD format. Access queries require this format for datestrings.

You need a query something like the following to get the first meeting date for
each client. Since CareType and TypeofCare seem to be associated with the
client table you don't need that in this query.

SELECT M.ClientID, Min(M.MeetingDate) as FirstMeeting
FROM tbl_RequestRCCP as M
WHERE M.MeetingDate Between #10/1/2005# and #3/31/2006#
GROUP BY M.ClientID

Then you can use that as a subquery or a nested query to get the other details
you want.

Nested Query: Save the above as qFirstMeeting and then join that to your
existing query on ClientID and MeetingDate.

I am having a few problems trying to work this one out.

[quoted text clipped - 27 lines]

Cheers
Keiron

 

[Keiron via AccessMonster.com]

It would be a great help if you posted the SQL text of your query - even if it
isn't giving you the desired results. It shows the fields involved, the tables
involved, and the relationships between the tables.

First thing I noticed is that your date strings are not in U.S. (MM/DD/YYYY)
format or in YYYY/MM/DD format. Access queries require this format for datestrings.

You need a query something like the following to get the first meeting date for
each client. Since CareType and TypeofCare seem to be associated with the
client table you don't need that in this query.

SELECT M.ClientID, Min(M.MeetingDate) as FirstMeeting
FROM tbl_RequestRCCP as M
WHERE M.MeetingDate Between #10/1/2005# and #3/31/2006#
GROUP BY M.ClientID

Then you can use that as a subquery or a nested query to get the other details
you want.

Nested Query: Save the above as qFirstMeeting and then join that to your
existing query on ClientID and MeetingDate.

I am having a few problems trying to work this one out.

[quoted text clipped - 27 lines]

Cheers
Keiron

 

[Keiron via AccessMonster.com]

John,
Thanks for that i tried your code and it works great.

SELECT M.ChecklistID, Min(M.MeetingDate) AS FirstMeeting
FROM tbl_RequestRCPP AS M
WHERE M.MeetingDate Between #10/1/2005# And #3/31/2006#
GROUP BY M.ChecklistID;

This gives me the correct amount of 1st meetings. I then created another qry
and added the first to get the HBC and FTC.

SELECT tbl_LacChecklist.ChecklistID, tbl_LacChecklist.CareType,
tbl_LacChecklist.TypeOfCare, RCPP1st.FirstMeeting
FROM tbl_LacChecklist INNER JOIN RCPP1st ON tbl_LacChecklist.ChecklistID =
RCPP1st.ChecklistID
WHERE (((tbl_LacChecklist.CareType)="hbc") AND ((tbl_LacChecklist.TypeOfCare)
="ftc"));

I have 1 more question in the tbl_RequestRCCP i have 10 fields that record
how many people turned up to each meeting and who received information. I was
wondering how i would add these into either query so i can get that
additional information.

CYP
BP
C
PW
O
CYPE
BPE
CE
PWE
OE

Thanks
Keiron

 

[John Spencer]

Add the table tbl_RequestRCPP to the second query. Join it to
tbl_LacChecklist as you did in your original query. If you do this in the
query grid it should be straightforward.

Or you can change the first query to something like the one below to return
entire rows.

SELECT tbl_RequestRCPP.*
FROM tbl_RequestRCPP
WHERE tbl_RequestRCPP.MeetingDate =
(SELECT Min(M.MeetingDate) AS FirstMeeting
FROM tbl_RequestRCPP AS M
WHERE M.MeetingDate Between #10/1/2005# And #3/31/2006#
AND M.CheckListID =tbl_RequestRCPP.CheckListID)