Single Report, Multiple Queries 1

[cfmonk]

Dear All,

I run an activity camps company for children. The children can either come for the whole week or for individual days. Also, in addition to the days there are optional sessions on either side of the days which they can be signed up to.

I have a database with a table for the children and each child's record has a set of Yes/No fields for each day / extra session for example:
Monday
Monday Exam
Monday 8-9
Monday 9-10
Monday 4-5
Monday 5-6
Tuesday
etc. etc.

What I am trying to create is a system whereby I can easily select the session I want to view and print a report that shows me which children are down for that session. Short of creating a different query and different report for each session, what would be the best way to do this?

So far I have tried referencing a combo box in the "Field" part of a column in a query but this doesn't seem to work. Then I created a different query for each day and tried to use the value in a combo box to choose the query and set it as the filter for a generic report and I am now at a loss!

Please help!

 

[GingerR]

Hi. The first thing you'll want to do is NORMALIZE your database. If you don't know what that is, see the links in my signature.

You should not have fields named "Monday", "Tuesday", etc. Instead you'd have a table something like this:

Table Name: Sessions
Field: SessionID (Autonumber)
Field: SessionWeekDay
Field: SessionTimeOfDay


Then in another table, you'd link up which children are in which sessions:

ChildID SessionID
1 1
1 3

etc.

This is very quick and superficial, but it makes the point. Please look up Normalization and the right ways to put together a relational database. You can see the problems you'll have already; later on after you have it set up properly, one example is that you'd simply select from a drop-down box of Sessions and get a report of who's in that particular session; no hard-coding queries for each day of the week or each session or anything, so if sessions are added or removed, your reports and queries and such just fill up with the changed data instead of someone having create new queries and reports each time.

Hope this helps--g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[dhookom]

Great post Ginger! Deserves a big star.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cfmonk]

Of course!!

I should have known that, I learnt it all for A Level two years ago!!

DUH!