MS Access dynamically change a table in a query for a report

[jpatterson]

Hi, I need to be pointed in the right direction. I'm looking for a way
to dynamically change a table in a query. I have a table called
students and each fiscal year the student table will get rolled to a
new table student2004. Currently all the reports are based on queries
using the student table. I would like to give the user the option to
pick a report and then pick the fiscal i.e. 2004 and then have the
query table change dynamically to student2004. Thanks for you help in
advanced.

[Ken Ismert]

You need to change the SQL statement for the query. Code snippet
(requires reference to Microsoft DAO 3.6 Object Library):

Dim rDB As DAO.Database
Dim rQdf As DAO.QueryDef

Set rDB = CurrentDB()
Set rQdf = rDB.QueryDefs("MyQuery")

rQdf.SQL = "SELECT * FROM student2004;"
rQdf.Close

That said, what you really have is a data normalization problem. I'll
let others in this group point the way to solving this for you.

-Ken

[Albert D. Kallal]

The best approach is to build a form that "prompts" the user.

Assuming that your report has a date field, then you just add a column to
the report query like:

MyYear:Year[DateField]

And, it is possible that you already have a year field (but, if you have a
date field..then you did not need the year field..did you!!).

Anyway, you then simply open the report using the where clause. Assuming
you have a text box on the prompt screen that gets the year from the user,
then you go:

strWhere = "MyYear = " & me.txtWhatYear
docmd.OpenReprot "mycoolRepot",acViewPreview,,strWhere

By use the reports "where" clause, you get dynamic conditions for the
sql..but don't have to modify the sql!!

You can see bunch of screen shots of example report prompt screens here, and
they all use the above idea:
http://www.attcanada.net/~kallal.msn.../ridesrpt.html

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[jpatterson]

Thank you both, I think I have what I need to start the process. Again
thanks.