Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2011

    Unanswered: Collating data from different fields


    I know how this looks is not best practice, but for various reasons it is all i could do. I have a table

    Staff Job 1 No Job 1 Hrs Job 2 No Job 2 Hrs Job 3 No Job 3 hrs

    Smith N701 20 N300 10
    Jones N300 10 N244 10 N400 20
    Brown N244 20 N400 10 N300 30

    I hope that is clear.

    Anyway, as you can see job number N300 could appear for any staff member, or in any job number up to about Job No 25. But these also have dates / week numbers against them so are actually multiple records. So each staff member has one record per week containing all their job numbers and allotted hours

    I need to be able to produce a collated report showing how many hours has been spent against Each Job. And I can't do it

    I had thought to create an append query into a new table pushing all the job numbers into a single field (essential to create a new record each time the job number appears), but that does not work as I get a duplicate field warning

    Cross tab only seems to work with the fielnames rather than content

    Bascially I am stuck. Any ideas?

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 17
    You can't do it with that table design, unless you want to write a series of queries that will mimic a normalised table.

    Ideally, you ought to normalise your design - it will save you a lot of trouble in the long run, as well as letting you answer your current question.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  3. #3
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    I agree that the design is seriously flawed. If you want to keep it, a work-around is to create a UNION query that pulls all those fields into a normalized structure, and base other queries on that.

  4. #4
    Join Date
    Mar 2011
    Hi both, thanks for your input

    I know the design is poor, but for what I needed to do (quickly as possible) it was all I could come up with - basically some of the job numbers are static and will always be there - but not always have hours against them, also some of the hr fields had to auto populate based oon what was elsewhere. So for example, workers do 8 hours per day, if they work on two jobs for 3 hours each, then the third job was auto populate to fill the gap.

    All very annoying, as I was asked to try to replicate the original (DOS) software already in use (and over 2000 years old - lol)

    So, that is where I was coming on.

    Union queries though, New to them, and with some nudge from here, and advice from elsewhere seems this is the way to go - and it seems to work

Posting Permissions

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