kevin_maher
New member
- Local time
- Today, 11:08
- Joined
- Aug 12, 2024
- Messages
- 11
Hi all
I've struck an issue which has me stumped, so am hoping you good folk out there can help me, please....
I work for a training organisation which trains the people that work in kindergartens, child care centres and the like. One of our staff visit the centres on a regular basis and lets me know each day the centres she visited, plus a couple of other things that aren't relevant to this problem.
I have a Visits table where the visits are stored (ID, Centre_ID, Visit_Date ...) and one thing I am trying to do is create a report that will show, for a given time period (e.g. 1st of August to 31st of August):
a) how many centres were visited
b) on how many days visits took place so e.g. if she visited on the 26th, 27th, 28th and 29th, that would be 4 days, regardless of how many places she visited
On my report I have two text fields for (a) and (b) above. The report gets its data from
SELECT t_Visits.Centre_ID, t_Visits.Visit_Date
FROM t_Visits
GROUP BY t_Visits.Centre_ID, t_Visits.Visit_Date
HAVING (((t_Visits.Visit_Date) Between [Enter Start Date:] And [Enter end date:]));
The formula to generate a figure for each of the fields in the expression builder is:
- centres visited : =IIf([Report].[HasData],Count([Centre_ID]),0)
- days on which visits took place : =IIf([Report].[HasData],Count(Unique([Visit_Date])),0)
Both of those give a #Error after the dates have been entered. Could someone let me know why, please and how I can get it to do what I want it to do? I've had a look on the 'net for that error and it's saying one reason is that two things in the database are named the same, which as far as I know is not the case because I've given those text fields unique names. It took me a bit to create a query that would count the number of unique days and have been able to do that now, but haven't been successful in getting a variation of that query to work on the query that's behind this report, hence the Count(Unique()).
Let me know if you need any further information.
I've struck an issue which has me stumped, so am hoping you good folk out there can help me, please....
I work for a training organisation which trains the people that work in kindergartens, child care centres and the like. One of our staff visit the centres on a regular basis and lets me know each day the centres she visited, plus a couple of other things that aren't relevant to this problem.
I have a Visits table where the visits are stored (ID, Centre_ID, Visit_Date ...) and one thing I am trying to do is create a report that will show, for a given time period (e.g. 1st of August to 31st of August):
a) how many centres were visited
b) on how many days visits took place so e.g. if she visited on the 26th, 27th, 28th and 29th, that would be 4 days, regardless of how many places she visited
On my report I have two text fields for (a) and (b) above. The report gets its data from
SELECT t_Visits.Centre_ID, t_Visits.Visit_Date
FROM t_Visits
GROUP BY t_Visits.Centre_ID, t_Visits.Visit_Date
HAVING (((t_Visits.Visit_Date) Between [Enter Start Date:] And [Enter end date:]));
The formula to generate a figure for each of the fields in the expression builder is:
- centres visited : =IIf([Report].[HasData],Count([Centre_ID]),0)
- days on which visits took place : =IIf([Report].[HasData],Count(Unique([Visit_Date])),0)
Both of those give a #Error after the dates have been entered. Could someone let me know why, please and how I can get it to do what I want it to do? I've had a look on the 'net for that error and it's saying one reason is that two things in the database are named the same, which as far as I know is not the case because I've given those text fields unique names. It took me a bit to create a query that would count the number of unique days and have been able to do that now, but haven't been successful in getting a variation of that query to work on the query that's behind this report, hence the Count(Unique()).
Let me know if you need any further information.