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

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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!

Reply | Quote

[indent]

---

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

---

[/indent]
Here’s a MINOR tip: to display

[time]

without getting

Type this:

[t]time[/t]

The “TAG” tags:

[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

Reply | Quote

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] or [/t] is sufficient.

Reply | Quote