Help creating query

[Will]

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

 

[John Vinson]

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

Very straightforward if you have properly normalized tables...

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

But you DON'T. This table structure is going to be VERY awkward! Not
only are you storing data (a year) in the table NAME, you're storing
data (months) in fieldnames. A properly normalized table would have
one procedure (or, if you don't keep track of individual procedures,
one month's count) per row, with a field for the date; there'd only be
ONE table with this data, and then a separate table of procedure
types. Ouch!

Ok... you can still do this, if I understand aright. Create a Query;
add Table1 to it; add the 1996 table; and add the 2003 table. I'm not
sure how Table2 is set up - does it have a field containing
"Procedures"? If so, join Table1 to Table2 by this field. Select the
fields you want to see from the two tables.

But a complete restructuring of your tables is something you should
very seriously consider! Right now you have very good spreadsheets:
but Access isn't a spreadsheet!