Keeping track of patients in a clinic

[peach2]

Patients enter the clinic and are given a certain schedule for future visits. From their date of entry, I want to track how many visits each patient made per week for four weeks. Every day I get a list of the visits of all patients, with those that are new highlighted. From this I make two tables in MS Access with the same two fields on each, [VisitDate] and [LastName]. One of the tables is the patients who are new. On it the [LastName] field would be unique. On the other table there would be multiple [visit date]s for each [LastName]. On only the patients I am tracking (those that are within the four week period from their first visit) I want to categorize each of their visits into one of the four week periods; then I want to count how many visits were in each week by each patient; add those counted visits together, and put that number into one of the four fields, [Wk1], [Wk2], [Wk3], and [Wk4] for each patient. I would like to show on a report this data for each patient that is within the four week period, on a continuous report showing all of them. (The first visit needs to be counted by the calendar week instead of seven days from the entry date.)
I hope someone smarter than I am can give me some help.

 

[dhookom]

Every day I get a list of the visits of all patients, with those that are new highlighted

On paper or text file or what?

Why only two fields? I can't imagine using LastName as distinct identifier for a patient. Aren't there lots of patients with the same last name?

Grouping dates by week is fairly simple with a totals query and the DatePart() function.

Duane
Hook'D on Access
MS Access MVP

 

[peach2]

Thank you for your response. I receive from the office the list of patient visits on paper, and I scan them into the computer and convert them to MS Access. The program they use in the office doesn't include the unique number, so I have asked the secretary when another family member or duplicate name comes in, to tack on the first initial of the first name from now on. That will give me a more or less unique field.

After I group the dates by week in the query, do I count them also in the query? I fairly new at this and need some kind of example to follow. Thank you.

 

[dhookom]

Please provide your exact table and field names, a few sample records, and how you want them displayed.

Duane
Hook'D on Access
MS Access MVP

 

[peach2]

Table name is TabVisits. Fields are [VisitDat](Date/Time) - [LastName](Text) - [Wk1](Text) - [Wk2](Text) - [Wk3](Text) - [Wk4](Text) - [TotalVisits](Number) - [VisitsperWk](Text) - [Description](Text) - Second table name is TabEntry. [VisitDat](Date/Time) - [LastName](Text) - [Countfield](Text) [Date/Time] field is a Key field.
Chiropractic Southern Office


16/19/2009 J

For each patient I would like the [Wk1], [Wk2], [Wk3], and [Wk4] fields to show the number of times that the patient came into the office within those weeks. I'm trying to solve the unique field problem.
Thank you for your patients and help.
Peach



[Doctor: Alan Meade, D.C.j

Total Patients: 106

|Avg Time Patient Waited:

7.29


VDate || Lastname | Amount Paid || Pay Method | Time In | Time Spent || Next Appoint.
6/19/2009 ASHFORD $15.00 Cash 8:04:36 AM 2.01 2/17/2004
6/19/2009 MORGAN $15.00 Visa 8:05:43 AM 2.08
6/19/2009 LANTZ $15.00 Check 8:08:29 AM 4.60
6/19/2009 ESTRADA $15.00 Cash 8:38:46 AM 3.36
6/19/2009 GALLAGHER $15.00 Cash 9:38:07 AM 2.84
6/19/2009 OLSON $30.00 Cash 9:47:28 AM 46.55
6/19/2009 LESTER $0.00 PC 9:52:1 9 AM 2.93
6/19/2009 OFFENBERG $15.00 Visa 9:57:21 AM 2.19
6/19/2009 MILLER $15.00 Visa 9:59:37 AM 8.85
6/19/2009 PETERSON $15.00 Cash 10:10:25 AM 6.17
6/19/2009 HAYDEN $15.00 Visa 10:11:12AM 7.70
6/19/2009 BEGAYSr $15.00 Cash 10: 15:34 AM 6.24
6/19/2009 BEGAY $0.00 Cash 10:16:13 AM 8.10
6/19/2009 MARTELLARO $15.00 Cash 10: 17:48 AM 9.34
6/19/2009 SWELFER $15.00 Cash 10:23:58 AM 5.22
6/19/2009 JACQUEZ $15.00 Visa 10:41:29 AM 4.48
6/19/2009 MOORE $15.00 Cash 10:53:20 AM 5.19
6/19/2009 PIZZINO $15.00 Visa 10:58:36 AM 2.27
6/19/2009 BURGESS $15.00 Cash 11:11:29 AM 4.10
6/19/2009 CREASON $15.00 Visa 11:13:00 AM 5.57
6/19/2009 CREASON $12.00 Visa 11:14:09 AM 3.19
6/19/2009 PIZZINO $30.00 Visa 11:14:48 AM 38.91
6/19/2009 BROWN $15.00 Cash 11:24:40 AM 3.17
6/19/2009 VANWINKLE $135.00 Visa 11:34:40 AM 10.63
6/19/2009 ROSE $15.00 Visa 11:36:43 AM 2.91
6/19/2009 ROBERTS $0.00 PC 11:38:13 AM 3.61
6/19/2009 MCCANDLESS $0.00 PC 11:47:34 AM 3.21
6/19/2009 MERRILL $0.00 Cash 11:51:07 AM 6.22
6/19/2009 RUNCORN $15.00 Check 1 1 :56:20 AM 3.47
6/19/2009 RUNCORN $12.00 Check 11:56:30 AM 5.88
6/19/2009 WOLF $15.00 Visa 12:00:05 PM 5.58
6/19/2009 LOFGRAN $15.00 Visa 12:02:58 PM 8.01
6/19/2009 TOMAMICHEL $15.00 Visa 12:11:57 PM 4.83
6/19/2009 SALIDO $15.00 Cash 12:14:42 PM 3.26
6/19/2009 MORGAN $12.00 Visa 12:18:12 PM 2.13
6/19/2009 NICKS $15.00 Visa 12:20:15 PM 4.31
6/19/2009 RASCON $15.00 Cash 1 2:24:48 PM 2.16
6/19/2009 RASCON $12.00 Cash 12:25:01 PM 4.93
6/19/2009 LEON $30.00 Cash 12:27:02 PM 49.72
6/19/2009 RAMIREZ $15.00 Check 12:40:24 PM 5.68
6/19/2009 GREER $15.00 Visa 12:41 :39PM 6.37
6/19/2009 BLANKS $15.00 Visa 12:43:05 PM 6.10
6/19/2009 HARRIS $15.00 Check 1 2:44:00 PM 8.89
6/19/2009 HERNANDEZ $15.00 Cash 12:51 :54PM 6.02
6/19/2009 BEQUEAITH $135.00 Cash 12:52:15 PM 11.85
6/19/2009 BEQUEAITH $0.00 PC 12:52:34 PM 8.58
6/19/2009 COLLIER $15.00 Visa 1 2:59:34 PM 6.97
6/19/2009 HERBERTH $15.00 Cash 1:01:19 PM 7.41
6/19/2009 NYRE $15.00 Cash 1 :06:56 PM 5.06
6/19/2009 CHRISTENSON $15.00 Visa 1:21:20PM 5.86
6/19/2009 SKIDMORE $15.00 Check 1:21:46PM 7.89

 

[dhookom]

What table is the data displayed from?
I would create a crosstab query rather than a table with WK1, Wk2, etc. The crosstab would be built much the same as the crosstab and report for months at faq703-5466. You should be able to change the date interval from month to week.

Duane
Hook'D on Access
MS Access MVP