If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
counting visits
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 |
#2
|
|||
|
|||
counting visits
Simple is to change your table sturcture from spreadsheet type to database
like this -- Name VisitDate VisitCode "Jackie L Preston" wrote: 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 |
#3
|
|||
|
|||
counting visits
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. "KARL DEWEY" wrote: Simple is to change your table sturcture from spreadsheet type to database like this -- Name VisitDate VisitCode "Jackie L Preston" wrote: 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 |
#4
|
|||
|
|||
counting visits
Thanks. I'll give this a try.
Jackie "KARL DEWEY" wrote in message ... 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. "KARL DEWEY" wrote: Simple is to change your table sturcture from spreadsheet type to database like this -- Name VisitDate VisitCode "Jackie L Preston" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|