Like Jeff Boyce, I'm a bit puzzled about how your Table is organized.
But since there is only about one way I'd recommend designing it, I'll
post my guess here.
My suggestion would be to store in each record in your Table the
person's name and the date of one visit. (Or, insted of a name, a link
to another Table containing names. Here, I'll just use names.)
[Visits] Table Datasheet View:
Name Date
------ ----------
Joe 10/31/2005
Joe 11/1/2005
Joe 11/2/2005
Joe 11/3/2005
Joe 11/4/2005
Joe 11/5/2005
Joe 12/1/2005
Joe 12/5/2005
Joe 12/6/2005
Joe 12/7/2005
Joe 12/14/2005
Joe 12/20/2005
Mary 12/25/2005
Then you can calculate the number of visits using a Query like this one:
[Q_Visits] SQL:
SELECT Visits.Name,
Format$(DateSerial(2006,[Month],1),"mmm") AS MName,
(Month([Visits]![Date])) AS [Month],
Count(Visits.Visits_ID) AS [# of visits]
FROM Visits
GROUP BY Visits.Name, (Month([Visits]![Date]));
The results will look like this for the sample Table I showed you.
[Q_Visits] Query Datasheet View:
Name MName Month # of visits
------ ------ -------- ------------
Joe Oct 10 1
Joe Nov 11 5
Joe Dec 12 6
Mary Dec 12 1
Joe has 6 visits in December, 5 in November, 1 in October.
For total visits, the following Query will do that:
[Q_TotalVisits] SQL:
SELECT Visits.Name,
Count(Visits.Visits_ID) AS [Total Visits]
FROM Visits
GROUP BY Visits.Name
ORDER BY Visits.Name;
[Q_TotalVisits] Query Datasheet View:
Name Total Visits
------ ------------
Joe 12
Mary 1
Joe has 12 total visits (1 + 5 + 6).
You can combine these Queries by calling them in another Query to
display both monthly and overall totals.
[Q_Summary] SQL:
SELECT Q_TotalVisits.Name,
Q_Visits.MName, Q_Visits.[# of visits],
Q_TotalVisits.[Total Visits]
FROM Q_TotalVisits
INNER JOIN Q_Visits
ON Q_TotalVisits.Name = Q_Visits.Name
ORDER BY Q_TotalVisits.Name,
Q_Visits.Month;
[Q_Summary] Query Datasheet View:
Name MName # of visits Total Visits
------ ------ ----------- ------------
Joe Oct 1 12
Joe Nov 5 12
Joe Dec 6 12
Mary Dec 1 1
You could display these in a Table via a Query like the following one.
(There are better ways to define this Query, but I'm showing you this as
an illustration of what you might do.)
[Q_VisitsByMonth] SQL:
TRANSFORM Max(Q_Summary.[# of visits])
AS [MaxOf# of visits]
SELECT Q_Summary.Name,
Q_Summary.[Total Visits]
FROM Q_Summary
GROUP BY Q_Summary.Name,
Q_Summary.[Total Visits]
PIVOT Q_Summary.MName
In ("Oct","Nov","Dec");
[Q_VisitsByMonth] Query Datasheet View:
Name Total Visits Oct Nov Dec
---- ------------ --- --- ---
Joe 12 1 5 6
Mary 1 1
All of these Queries were based on that one Table, but you can see that
a variety of datasheet views are possible. Any of these Queries can be
used as a basis for your Reports.
-- Vincent Johns <
[email protected]>
Please feel free to quote anyting I say here.
