Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts