Sub-Query SQL Question 1

[RaphDawg]

Hi All,
I have Sub-Query SQL question.
I can’t figure out the correct Sub-Query SQL that will count the amount of ServiceDate records per day.
I want the CountOfServiceDate column to displays a count value for the total amount of displayed ServiceDate’s for each record. So since Nichol & Bradley have 1 record a piece on 03-Nov-08, I want a 2 to display in the CountOfServiceDate column. Not sure if this is possible the way I have my data structured.
Help Please! Thanks Big in advance.

Current query results (except the CountOfServiceDate column):
DayNum ClientID ServiceDate FamilyID CountOfServiceDate
3 Johnson, Nichol 03-Nov-08 Johnson Family, Mary & Robert 2
3 Johnson, Bradley 03-Nov-08 Johnson Family, Mary & Robert 2
4 Johnson, Nichol 04-Nov-08 Johnson Family, Mary & Robert 1

Current Failed Query:

SELECT DISTINCT [ClientLastName] & ", " & [ClientFirstName] AS ClientFullName, tblCareDay.ServiceDate, tblClients.ClientID,
(SELECT top 1 Count(*) FROM tblCareDay GROUP BY tblCareDay.ServiceDate) AS CountOfServiceDate
FROM (tblClients INNER JOIN tblCareDayRecord ON tblClients.ClientID = tblCareDayRecord.ClientID) INNER JOIN tblCareDay ON tblCareDayRecord.CareDayID = tblCareDay.CareDayID
GROUP BY [ClientLastName] & ", " & [ClientFirstName], tblCareDay.ServiceDate, tblClients.ClientID, tblClients.FamilyID, tblClients.ClientType
HAVING (((tblClients.FamilyID)=2) AND ((tblClients.ClientType)=3))
ORDER BY tblCareDay.ServiceDate;

 

[Remou]

From the above it seems that you may just need a group, rather than select distinct and a sub query.

Is this what you want:

SELECT DISTINCT [ClientLastName] & ", " & [ClientFirstName] AS ClientFullName, 
tblCareDay.ServiceDate, tblClients.ClientID, 
Count(ServiceDate) AS CountOfServiceDate
FROM tblClients INNER JOIN tblCareDayRecord ON tblClients.ClientID = tblCareDayRecord.ClientID
GROUP BY [ClientLastName] & ", " & [ClientFirstName], tblCareDay.ServiceDate, tblClients.ClientID
HAVING tblClients.FamilyID=2 AND tblClients.ClientType=3
ORDER BY tblCareDay.ServiceDate

 

[RaphDawg]

Hey Remou,
Thanks for the reply. Unfortunately that option won’t work for me. Count(ServiceDate) returns a 1 for both Nichol & Bradley. Each child can have 1 or more records per day (from the tblCareDayRecord table). My query just displays the child 1 time if they had at least 1 record for the day. Count(ServiceDate) returns the total amount of records that the child has per day, so if Nichol had 3 different (tblCareDayRecord) records on 03-Nov-08; Count(ServiceDate) will return a 3 for Nichol’s record. I really just want to know that the Johnson family had 2 records on 03-Nov-08. Is this possible? Thanks!

 

[Remou]

I forgot to delete distinct, try:

SELECT  tblClients.ClientID, [ClientLastName] & ", " & [ClientFirstName] AS ClientFullName, 
tblCareDay.ServiceDate, 
Count(ServiceDate) AS CountOfServiceDate
FROM tblClients INNER JOIN tblCareDayRecord ON tblClients.ClientID = tblCareDayRecord.ClientID
GROUP BY [ClientLastName] & ", " & [ClientFirstName], tblCareDay.ServiceDate, tblClients.ClientID
HAVING tblClients.FamilyID=2 AND tblClients.ClientType=3
ORDER BY tblCareDay.ServiceDate

 

[RaphDawg]

Get error that query doesn’t include the specified expression tblClients.FamilyID=2 AND tblClients.ClientType=3 as part of an aggregate function

 

[Remou]

I was not paying attention, this may be better.

SELECT tblClients.ClientID, 
   [ClientLastName] & ", " & [ClientFirstName] AS ClientFullName, 
   tblCareDay.ServiceDate, 
   Count(tblCareDay.ServiceDate) AS CountOfServiceDate
FROM (tblClients 
INNER JOIN tblCareDayRecord 
    ON tblClients.ClientID = tblCareDayRecord.ClientID) 
INNER JOIN tblCareDay 
    ON tblCareDayRecord.CareDayID = tblCareDay.CareDayID
GROUP BY tblClients.ClientID, 
   [ClientLastName] & ", " & [ClientFirstName], 
   tblCareDay.ServiceDate, tblClients.FamilyID, 
   tblClients.ClientType
HAVING (((tblClients.FamilyID)=2) AND ((tblClients.ClientType)=3))
ORDER BY tblCareDay.ServiceDate;

 

[RaphDawg]

Thanks Remou,
CountOfServiceDate still returns a 1 for both Nichol & Bradley. I really just want to know that the Johnson family had 2 records on 03-Nov-08. Is this possible?

 

[Remou]

Yes it is. Please post a little data for the three tables.

 

[RaphDawg]

Hey Remou,
I uploaded the tables and query to the listed URL. Please note the CountOfServiceDate query. Thanks for all your time!!

 

[Remou]

The table tblCareDay seems to be irrelevant - it does not even have a record for the Johnsons, so let us concentrate on the second table, tblCareDayRecord. This, then, is the query I think you want:

SELECT tblClients.FamilyID, tblCareDayRecord.Date, Count(tblCareDayRecord.CareDayRecordID) AS CountOfCareDayRecordID
FROM tblClients INNER JOIN tblCareDayRecord ON tblClients.ClientID = tblCareDayRecord.ClientID
GROUP BY tblClients.FamilyID, tblCareDayRecord.Date
HAVING (((tblClients.FamilyID)=2))

I see you are using look-up fields. These are an anti-feature that will lead you deep into swampy waters, to be bitten by the bugs that live there. You may wish to read this

http://www.mvps.org/access/tencommandments.htm.

 

[RaphDawg]

KOOL!! Almost there. I see You got the 2 value to display for the records on 11/3 as Nichol & Bradley have a record for that day. Problem is on 11/4 only Nichol has a record but the CountOfServiceDate value displays 2. Nichol has 2 tblCareDayRecord’s on the 4th. Here is the deal; my daycare lady gives me a discount if I have 2 or 3 kids on the same day. I need the query to display how many of my kids showed up per day so I can calculate my weekly bill. Does this make sense?
Thanks!!!

BTW great info on the Evils of Lookup Fields. Much appreciated.

 

[Remou]

I had not noticed that a child might have more than one record per day.

SELECT c.FamilyID, j.Date, 
   Count(j.ClientID) AS CountOfClients
FROM tblClients c INNER JOIN 
   (SELECT DISTINCT cr.ClientID, cr.Date 
    FROM tblCareDayRecord cr) AS j 
ON c.ClientID = j.ClientID
GROUP BY c.FamilyID, j.Date
HAVING c.FamilyID=2

Aliases (c, j, cr) make it easier to read the query.

Date is a reserved word and should never be used for field or control names.

 

[RaphDawg]

Awesome!!!!!!!! Just what I needed! A week and a half of frustration over. Thanks big for your patience and time. Great learning experience as well.
Thanks!