Having a Group Represented by More than One Possibility (1 Viewer)

[themurph2000]

I looked for this solution through the forums, but I could not find it. And I'm sure this is one of those "why didn't I think of that" moments. So I apologize in advance if I waste anybody's time.

I have a query that gives me a list of orders to be run on a particular date. One of the fields is a "Shift" field with 5 possibilities: run only one one of the existing three shifts, run it 1st and 2nd shift, or run it all three shifts.

What I want to do is have a group for each shift within a day (a higher-level group), but the transaction would show up for each shift in which its supposed to run.

EXAMPLE

Order 1 1/26/10 Shift=1
Order 2 1/26/10 Shift=4
Order 3 1/26/10 Shift=5
Order 4 1/26/10 Shift=2

Where a 4 in the shift means run it on first and second shift and a 5 means run it all three. Here is the grouping.

FIRST SHIFT
Order 1
Order 2
Order 3

SECOND SHIFT
Order 2
Order 3
Order 4

THIRD SHIFT
Order 3

Any suggestions?

Thanks

 

[Brianwarnock]

I don't see how you can do it in one query but you could use 3 and 2 unions to join them

Select.."First" as Shift maybe? or 1
From..
Where Shift=1 or 4 or 5
Union
Select..
From
Where Shift =2 or 4 or 5
union
from
where shift = 5

Brian

 

[themurph2000]

I don't see how you can do it in one query but you could use 3 and 2 unions to join them

Select.."First" as Shift maybe? or 1
From..
Where Shift=1 or 4 or 5
Union
Select..
From
Where Shift =2 or 4 or 5
union
from
where shift = 5

Brian

OK, I tried that. My code is

SELECT [Date Selection Experiment].*, "First" as Shift
FROM [Date Selection Experiment]
WHERE ((([Date Selection Experiment].[Shift Code])=1)) OR (((4)<>False)) OR (((5)<>False));
UNION
SELECT [Date Selection Experiment].*, "Second" as Shift
FROM [Date Selection Experiment]
WHERE ((([Date Selection Experiment].[Shift Code])=2)) OR (((4)<>False)) OR (((5)<>False));
UNION
SELECT [Date Selection Experiment].*, "Third" as Shift
FROM [Date Selection Experiment]
WHERE ((([Date Selection Experiment].[Shift Code])=3)) OR (((5)<>False));

Unfortunately, it's simply combining the words First, Second, and Third with every record.

 

[vbaInet]

Not quite clear what you want, but try this instead:

SELECT *, Shift AS "First"
FROM [Date Selection Experiment]
WHERE ([Shift Code]=1 OR [Shift Code]=4 OR [Shift Code]=5);
UNION
SELECT *, Shift AS "Second"
FROM [Date Selection Experiment]
WHERE ([Shift Code]=2 OR [Shift Code]=4 OR [Shift Code]=5);
UNION
SELECT *, Shift AS "Third"
FROM [Date Selection Experiment]
WHERE ([Shift Code]=3 OR [Shift Code]=5);

You may also need a UNION ALL in place of UNION. But try it out first. I just amended the aliases as well

 

[themurph2000]

Not quite clear what you want, but try this instead:

SELECT *, "First" as Shift
FROM [Date Selection Experiment]
WHERE ([Shift Code]=1 OR [Shift Code]=4 OR [Shift Code]=5);
UNION
SELECT *, "Second" as Shift
FROM [Date Selection Experiment]
WHERE ([Shift Code]=2 OR [Shift Code]=4 OR [Shift Code]=5);
UNION
SELECT *, "Third" as Shift
FROM [Date Selection Experiment]
WHERE ([Shift Code]=3 OR [Shift Code]=5);

You may also need a UNION ALL in place of UNION. But try it out first

How's this for irony? That's how the SQL came out when, somehow, I got the QBE grid for the union query. Basically, that's what Microsoft did to the SQL.

What you wrote worked perfectly. Thanks!

 

[vbaInet]

Interesting stuff. Glad it worked. You're welcome.