Using different tables with one report

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I have a large number of tables imported from excel all with the same
fields. Is it possible to write a macro or module which would allow me
to change the data source in the report so that I only need to have
one report.

Ideally I would like to be able to select the name of a table from a
combo box on a form and then click a command button to preview the
report for that table.

TIA
Robert
 
Either combine all the tables into a single table with a field that
identifies the source table or create a union query of all the tables. Base
your report on the union query.
 
Thanks for your help. Thats not quite what I wanted to do, but I am
glad to have learnt the technique anyway.

I want to use a single report but replace the source table with one of
6 different tables when I run the report. Each table is the students
in a single school year group. I could combine them into a single
table but I might need to update just one year group and then re-run
the report. I know I could alter the data source in the report
properties, but, if other people are to use the database, I would like
it to be more elegant than that.

Robert
 
I'm not sure who misunderstood who...
Example of my solution:
Three separate tables of students, each from their own class
Tables: tblMrJones, tblMsSmith, tblMsAnderson
Fields: FirstName, LastName, BirthDate, Gender (each table the same)
You could create a union query "quniAllClasses":
SELECT "Mr. Jones" as Teacher, FirstName, LastName, BirthDate, Gender
FROM tblMrJones
UNION ALL
SELECT "Ms. Smith", FirstName, LastName, BirthDate, Gender
FROM tblMsSmith
UNION ALL
SELECT "Ms. Anderson", FirstName, LastName, BirthDate, Gender
FROM tblMsAnderson;

You can then open your report that is based on quniAllClasses. There are
lots of ways of applying a filter either in the report's record source or in
the DoCmd.OpenReport... code.

You can also set the report's record source in the On Open event of the
report. This will have absolutely no affect on other users of the same
database.
 
Back
Top