A single report applying to different queries

[David Weddell]

Help for a beginner please.

I started a new job in a school as a Lecturer and IT Support in April of this year. For the first time I am using Office(XP) prior to that I was very happy using MSWorks for the past 13 years.

In the school many members of staff have similar lists in Excel and I have recommended that we use Access.

The current problem is how you apply a report to differnt queries. I have learnt how in using the report wizard to choose a query. I suspect that as we have four terms per year I will use a series of standard reports to present information on the new and exsisting students, contained in my single table, for each term (semester). I think this is a simple thing but cannot find the solution.

I have bought "Using MS Access 2002, Special Edition" published by QUE

Another problem I had, but got around by individual postings, was to repeat information common to a large number of my records. In MSWorks I went into list view and via fill down could do this (after sorting into the required order). In Access I cannot find an equivilent.

I have to give a Board presentation this Friday and the first point is the only major problem I have to solve at this point in time.

Thanks in anticipation

 

[Fizzio]

2 things to consider, both involving your database structure. If you use a parameter query to supply your report, you only need 1 query for the report but just supply the query with different criteria each time eg semester, year, class etc. This may mean that you have to look at the data structure to ensure that you are only storing relevent information to each table eg Student table would include Name, Address, DOB etc but NOT Semester, Class, Year etc (Use lookup tables and refer to them in a join table. This leads onto your second point. The idea of storing info in a Db is mainly to ensure efficient storage and retrieval of information. Efficient storage means only having to input the information once so If you are having to duplicate data, your data structure is probably not ideal. Look up normalisation/normalization here in the search and at microsoft.com also.
Hope this gets the grey matter working

 

[catt]

If one report can handle the data of several queries, it is easy to do.
First, all the fields in each query must be named the same, if not use aliases.
Second, copy the report as many times as there are queries giving each a new unique name.
Then, open each report in design, click on the bottom below the report and right click to get the properties.
Finally, change the source of the report to the new name of the query.

Now you have the same report for many queries. I have thought of trying to figure out a way to get prompted for the name of the source, but I am afraid that by that time I would have forgotten the name of the query needed.

Hope this helps.

 

[David Weddell]

Thank you Fizzio and Catt

Both answers are a great help. Thank you both very much.

Fizzio -
My second problem has only arisen because I was importing Excel lists which did not have this data included and I then had to add to each record. However I still think I would like this feature as we often have a lot of students from a few countries and I would prefer not to type the country into every record if I could automate it. I realise I can set a default for the majority country and it would reduce my work load.

On the first aspect I had (by accident!) set up a parameter (I think!) on my little test data at home here and it seems to provide the solution. Have to remember/lookup how I did it.

Catt
Your answer on right clicking at the bottom of the report and altering the properties to reflect the source is the information I was missing. I will now play around here a bit more and also on the paramenter on the query suggested by Fizzo

 

[Fizzio]

Catt, There is a much easier way that also uses less space in the Db. Rather than having multiple copies of the same report apart from being supplied by a different query, you can set this dynamically using VB. This enables you to set the query for the report at runtime and may have many queries populating the one report.

Essentially the VB line is
Reports!NameofReport.Recordource = NameofQuery

This can cut down a lot of duplication but I still recommend my first solution.

 

[dungstar]

How do you use this line:
Reports!NameofReport.Recordource = NameofQuery

The report needs to be open before it's recordsource can be changed, and when the report is open (in print preview) it will not allow the change.