counting visits

[Jackie L Preston]

I have a patient visit table structured as Name, Date, VisitCode, Date1,
VisitCode1, ...Date6, VisitCode6 corresponding to the 7 days of the week.
Name is a text field, Date* - date field, and VisitCode* text field.

What I want to do is get a report with the count of the number of patients
seen on a particular date. For one pt it might be Date1 and another it
might be Date6 depending on whether it was an initial visit or a subsequent
one. I don't need to know the particular patients or visits, just the total
number seen on Date2 (Wednesday, 2/14/07) for example. I suspect there is a
fairly simple answer but I am somewhat "query challenged". An average of
the number seen on all dates would be great.

Thanks,
Jackie L Preston MD

 

[Guest]

Simple is to change your table sturcture from spreadsheet type to database
like this --
Name
VisitDate
VisitCode

 

[Guest]

Use a union query like this substituting your table name for Jackie_L ---
SELECT Jackie_L.Name, Jackie_L.Date, Jackie_L.VisitCode
FROM Jackie_L
UNION ALL SELECT Jackie_L.Name, Jackie_L.Date1, Jackie_L.VisitCode1
FROM Jackie_L
UNION ALL SELECT Jackie_L.Name, Jackie_L.Date2, Jackie_L.VisitCode2
FROM Jackie_L
UNION ALL SELECT Jackie_L.Name, Jackie_L.Date3, Jackie_L.VisitCode3
FROM Jackie_L
UNION ALL SELECT Jackie_L.Name, Jackie_L.Date4, Jackie_L.VisitCode4
FROM Jackie_L
UNION ALL SELECT Jackie_L.Name, Jackie_L.Date5, Jackie_L.VisitCode5
FROM Jackie_L
UNION ALL SELECT Jackie_L.Name, Jackie_L.Date6, Jackie_L.VisitCode6
FROM Jackie_L;

You can use the union query to normalize your data. You then run a select
query eith on the new table or the union query with your date as criteria.

 

[Jackie L Preston]

Thanks. I'll give this a try.



Jackie

Use a union query like this substituting your table name for Jackie_L ---
SELECT Jackie_L.Name, Jackie_L.Date, Jackie_L.VisitCode
FROM Jackie_L
UNION ALL SELECT Jackie_L.Name, Jackie_L.Date1, Jackie_L.VisitCode1
FROM Jackie_L
UNION ALL SELECT Jackie_L.Name, Jackie_L.Date2, Jackie_L.VisitCode2
FROM Jackie_L
UNION ALL SELECT Jackie_L.Name, Jackie_L.Date3, Jackie_L.VisitCode3
FROM Jackie_L
UNION ALL SELECT Jackie_L.Name, Jackie_L.Date4, Jackie_L.VisitCode4
FROM Jackie_L
UNION ALL SELECT Jackie_L.Name, Jackie_L.Date5, Jackie_L.VisitCode5
FROM Jackie_L
UNION ALL SELECT Jackie_L.Name, Jackie_L.Date6, Jackie_L.VisitCode6
FROM Jackie_L;

You can use the union query to normalize your data. You then run a
select
query eith on the new table or the union query with your date as criteria.