Max Question.

[Mr-Re Man]

I have a table called tblDeptVisits with a fields ID, Dept, Service,
VisitingDate, Visiting Time.

I have a form where the user just has to press a command button and it
tallies up the number of visitors by all the criteria listed above into the
table.

I also have 3 reports that break down the information to provide statistics
to managers, these are on total number of visitors, totals by day, totals by
hour.

Is it possible to include some code on the form within a label that indicates:
The highest number of visitors was XX on the XX/XX/XXXX
The most popular day for visitors is XXXXXX
The most popular time for visitors is XX:XX

thanks in advance

 

[Douglas J. Steele]

You can create queries that will give you that information.

For the first, use a query along the lines of:

SELECT TOP 1 VisitingDate, Count(*)
FROM tblDeptVisits
GROUP BY VisitingDate
ORDER BY 2

For the second,

SELECT TOP 1 Format(VisitingDate, "dddd"), Count(*)
FROM tblDeptVisits
GROUP BY Format(VisitingDate, "dddd")
ORDER BY 1

For the third,

SELECT TOP 1 VisitingTime, Count(*)
FROM tblDeptVisits
GROUP BY VisitingTime
ORDER BY 1

An issue, though, is that TOP 1 doesn't actually guarantee that you'll only
get one row returned: if there's a tie (such as, say, Tuesday and Wednesday
each are just as popular), multiple rows will be returned. For that reason,
you'd be best off putting subforms on your form, so that multiple rows can
be returned if necessary.

 

[Mr-Re Man]

Thank you, it works a treat.

You can create queries that will give you that information.

For the first, use a query along the lines of:

SELECT TOP 1 VisitingDate, Count(*)
FROM tblDeptVisits
GROUP BY VisitingDate
ORDER BY 2

For the second,

SELECT TOP 1 Format(VisitingDate, "dddd"), Count(*)
FROM tblDeptVisits
GROUP BY Format(VisitingDate, "dddd")
ORDER BY 1

For the third,

SELECT TOP 1 VisitingTime, Count(*)
FROM tblDeptVisits
GROUP BY VisitingTime
ORDER BY 1

An issue, though, is that TOP 1 doesn't actually guarantee that you'll only
get one row returned: if there's a tie (such as, say, Tuesday and Wednesday
each are just as popular), multiple rows will be returned. For that reason,
you'd be best off putting subforms on your form, so that multiple rows can
be returned if necessary.