Sorting a query with a twist

[Opal]

I have a query I am trying to create based on another query
(inventory) and a table. The inventory query shows the date, shift,
Die set and the # of shifts remaining for each die set in inventory.
The table has a corresponding date and shift field as well as two more
fields that tell me what die set was left in the line at the end of
the previous shift and what die set is staged to go in next (again
left from the previous shift). When the inventory query is run it
tells me what die set has the lowest inventory to the highest.
However, the die set with the lowest inventory may not be the one
already in line or the one staged to go in line next. I need to be
able to sort the new query so that the die set in line and the die set
staged to go next are 1st and second in the query followed by the
remaining die sets in ascending order. I'm thinking that I should set
criteria based on an IIF statement, but I'm not sure where to begin.
Can anyone provide some advice?

 

[Guest]

You don't say what the data types of the columns which indicate the die set
left in line and that next in line are, but I'll assume they are Boolean
(Yes/No). If so then you can first sort on an expression, using nested IIF
function calls, which returns 0 for the die set currently left in line, 1 for
that to go next in line and 2 for the remainder. Assuming the two Boolean
columns are named InLine and NextInLine a suitable expression would be:

IIF([InLine],0,IIF([NextInLine],1,2))

Order the query by this expression first, then by the current lowest to
highest inventory sort order.

Ken Sheridan
Stafford, England

 

[Opal]

You don't say what the data types of the columns which indicate the die set
left in line and that next in line are, but I'll assume they are Boolean
(Yes/No). If so then you can first sort on an expression, using nested IIF
function calls, which returns 0 for the die set currently left in line, 1 for
that to go next in line and 2 for the remainder. Assuming the two Boolean
columns are named InLine and NextInLine a suitable expression would be:

IIF([InLine],0,IIF([NextInLine],1,2))

Order the query by this expression first, then by the current lowest to
highest inventory sort order.

Ken Sheridan
Stafford, England

Thank you Ken, I will look into that. However, they are not Boolean.
They
are text as they indicate the name of the die set left in line. Will
that make
a difference with the IIF statement you noted above?

 

[Guest]

It will make a difference, though the basic principle should be the same.
Can you give an example of what would be in three rows in the table, one for
the die set left in line, one for that next to go in line and one of the
remaining rows. If for instance the Inline column for that left in line is
Null in all rows but the die set in question, similarly for that next to go
in line for the NextInLine column and all remaining rows being Null for both
columns the expression would be:

IIF(Not IsNull([InLine]),0,IIF(Not IsNull([NextInLine]),1,2))

or you might have a situation where the value in the InLine column = the
value in a DieSetName column for that left in line, and similarly for the
NextInLine column, in which case:

IIF([DieSetName] = [InLine],0,IIF([DieSetName] = [NextInLine],1,2))

I'd point out, however that having two columns is not the best design for
this. It amounts to what's called 'encoding data as column headings',
whereas a fundamental principle of the relational database model is that data
should only be stored as values at column positions in rows in tables. A
better design would be to have a single column InLineStatus say, which could
have values such as Left In Line, Next In Line, Not in Line for instance.
Even better would be to have a separate InLineStatuses table with three rows
with such values in a text column and a numeric InLineStatusID column as its
primary key with values 1, 2 and 3. You could then reference this table with
an InLineStatus foreign key column in the current table. You could then
order your query firstly by this foreign key column, which by virtue of the
values being 1, 2 or 3 would give you the sort order you want.

Ken Sheridan
Stafford, England

You don't say what the data types of the columns which indicate the die set
left in line and that next in line are, but I'll assume they are Boolean
(Yes/No). If so then you can first sort on an expression, using nested IIF
function calls, which returns 0 for the die set currently left in line, 1 for
that to go next in line and 2 for the remainder. Assuming the two Boolean
columns are named InLine and NextInLine a suitable expression would be:

IIF([InLine],0,IIF([NextInLine],1,2))

Order the query by this expression first, then by the current lowest to
highest inventory sort order.

Ken Sheridan
Stafford, England

Thank you Ken, I will look into that. However, they are not Boolean.
They
are text as they indicate the name of the die set left in line. Will
that make
a difference with the IIF statement you noted above?