Results 1 to 2 of 2
Thread: Design question (2003)
2009-02-03, 11:11 #1
- Join Date
- Jan 2001
- Warrington, Cheshire, England
- Thanked 0 Times in 0 Posts
Design question (2003)
My user has a DB containing details of about 1500 volunteers. She's been given the task of updating award information for each volunteer, each of which could need data in up to seven fields.
The first field is the date that the volunteer registered; without this, none of the other fields can be completed. The award fields are for various lengths of service, namely 2, 5, 10, 15, 20 and 25 years.
The user has also indicated that it would be helpful to produce a report of volunteers who are going to be due an award, say in the coming year. This clearly has to detail, for each volunteer, which level of award becomes due this year.
I thought of writing some queries to produce reports of which volunteers
a) have no start date recorded
are entitled to a 2 year award but do not have a date recorded in the DB
c) repeat for the other award fields.
d) produce the report for who is entitled to what award in the coming year.
My Initial thought was just to pile in and write a bunch of queries and their equivalent reports, but it all looks so similar that I got to thinking that there must be a smarter way to achieve this.
I've looked at the Access help for filters but I've never used one and don't understand how to use them or whether one or more filters would help simplify things.
What's the view on how best to design the solution to the problem, please?
2009-02-03, 11:32 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: Design question (2003)
You'd have to create a separate query for each award; add a column to each that names the award. For example, in the query for the 10 years award you'd create a column
AwardDue: "10 years"
Next, create a union query that combines the results of the individual queries. Use this as the record source for your (single) report.