Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query question (Access 2k Win 2k)

    Apologies for the lack of information in the post title, but I couldn't condense this question into a one liner....

    OK, I have a data table with lots of information in it, part of the information is a group of names, these names can be entered into any of four fields, the first is required and used for the 'primary worker' the other three are for people helping with the job.

    I'm now trying to work out how many hours each person records in any set period of time, is there any way I can set up a query that will put all four fields into one, but keep the names as seperate entries? I don't want to concatenate them. The end result I would expect would be, for example, if between date 1 and date 2 there were two jobs, one had 4 people working on it and the other job had 3 I would have 2 records in the main data table, in the resulting query I would have seven records.

    At the moment I have a method of getting the answer I want that works, but it requires two queries for each person, plus a third query to collect the data and do the math. The first two queries are specific to a name (not much flexibility and requires work if we employ a new person), I'm trying to simplify the database and make it more flexible.

    Thanks for the help

    Ian

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query question (Access 2k Win 2k)

    A setup with separate records for each person involved in the job would have been better.

    In the present setup, you may be able to use a union query. The following is just an example, since I don't know the table and field names:

    SELECT JobID, Person1, HoursWorked, StartDate FROM tblJobs
    UNION
    SELECT JobID, Person2, HoursWorked, StartDate FROM tblJobs WHERE Person2 Is Not Null
    UNION
    SELECT JobID, Person3, HoursWorked, StartDate FROM tblJobs WHERE Person3 Is Not Null
    UNION
    SELECT JobID, Person4, HoursWorked, StartDate FROM tblJobs WHERE Person4 Is Not Null

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (Access 2k Win 2k)

    Hans

    I agree that a different setup would have been better, unfortunately when the system was set up I did not have the knowledge to allow me to make that setup, and I hadn't heard of the lounge either... :-)

    I'll try the Union Query, thanks for the advice.

    Ian

Posting Permissions

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