Help Creating Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I work for a doctors office usually just doing grunt stuff, backups and restores and updates and other things of the sort. But now I have been asked to create a database which shows procedures that we have done through out the year. Im trying to create a query that would look back over the past 11 years the doctor has been in practice and would pull the information it was asked for. For instance the billing manager may want to compare january of 2003 with january of 1996 As shown Below. Any help or guidance would be greatly appreciated.

Procedures Jan-03 Jan-96
Injections 20 45
Consultations 10 30
Office Visits 5 15

This is how the tables are set up the procedures are constant and never change
Table 1

Procedures
Injections
Consultations
Office Visits

Each year is on a different table
Table 2
Jan-03 Feb-03 Mar-03
15 10 15
20 31 35
22 45 22
 
Dear Will:

It would be so much better to put all the years into one table. If
there is a column such as a date/time value that can be used to
segregate the data into years, this will allow you all the same
effects without nearly as much aggravation:

WHERE Year(DateColumn) = 2003

If you cannot avoid having separate tables for this, then you could
create a query that is a UNION ALL of all these annual tables so you
could create the same effect out of all of them, but this is a "second
choice" way of doing it and not optimal.

This might look something like:

SELECT * FROM Procedures1996
UNION ALL
SELECT * FROM Procedures1997
UNION ALL
SELECT * FROM Procedures1998
UNION ALL
SELECT * FROM Procedures1999
UNION ALL
SELECT * FROM Procedures2000
UNION ALL
SELECT * FROM Procedures2001
UNION ALL
SELECT * FROM Procedures2002
UNION ALL
SELECT * FROM Procedures2003

You would have to change this once a year to add yet another table to
the union.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top