Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    A Union query question (Acc 97 sr2 on 95b)

    Can I use a union query to combine multiple fields into 1 field?

    I have an Original table that has job detail and the hours allocated per dept.

    eg Job# Fab Turn Mill Slot
    49114 | .5 | 4 | 3 | 1

    I want to change it to:

    Job# Dept Time
    49114 | Fab | .5
    49114 | Turn | 4
    49114 | Mill | 3
    49114 | Slot | 1

    so I can create a report that shows jobs grouped by Dept

    Is this the way to go about it or am I barking up the wrong tree?
    "Heading for the deep end"

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: A Union query question (Acc 97 sr2 on 95b)

    Yes you could use a union query.

    first create 4 separate queries like these two:
    SELECT tbljobs.Jobid, tbljobs.Fab AS [time ], "Fab" AS Dept FROM tbljobs;
    SELECT tbljobs.Jobid, tbljobs.Turn AS [time ], "Turn" AS Dept FROM tbljobs;

    Then combine them all together like this
    SELECT tbljobs.Jobid, tbljobs.Turn AS [time ], "Turn" AS Dept
    FROM tbljobs
    union
    (SELECT tbljobs.Jobid, tbljobs.Fab AS [time ], "Fab" AS Dept
    FROM tbljobs);

    However you might be better off to restructure your tables. Because you have four time fields for each job, a normalised solution would have a job time table with records like the ones youy want from the union query.

    ps : I have an extra space to the end of [time ] to avoid time being replaced with an hourgalss image.
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: A Union query question (Acc 97 sr2 on 95b)

    Thankyou, I thought it would be something like that. As for restructuring, you may be right, but it more than I need to do at the moment. My mdb has evolved over time to what it is today and is used too extensively by our office to think about restructuring it now. Boy, have I learnt some things along the way!
    "Heading for the deep end"

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: A Union query question (Acc 97 sr2 on 95b)

    <hr>I have an extra space to the end of [time ] to avoid time being replaced with an hourgalss image.<hr>
    Here's a MINOR tip: to display

    <!t>[time]<!/t>

    without getting

    <img src=/S/time.gif border=0 alt=time width=23 height=39>

    Type this:

    <!t>[t]<!/t>time<!t>[/t]<!/t>

    The "TAG" tags:

    <!t>[t]<!/t> <!t>[/t]<!/t>

    display brackets around the enclosed text so that a stupid smilie doesn't take the place of an expression in brackets. I even resorted to recording a simple Word "macro" to do this automatically (it can be tedious to do manually if you have a lot of brackets to replace...)

    HTH

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: A Union query question (Acc 97 sr2 on 95b)

    Actually, you only need to replace one of the brackets with a "tag" tag. It takes an intact pair of square brackets to cause a smilie to pop up. Replacing one of them with <!t>[t] or [/t]<!/t> is sufficient.
    Charlotte

Posting Permissions

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