Ordering results, not as simple as it sounds

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

Hey Everyone, first my disclaimer...not my design, inherited, so please don't
complain about table structure, I already know. Thanks...onward.

There are 2 tables

First table holds time-keeping data (just the important stuff listed)
Job Number (Number, Long Int.)
Operation (Text, 50)

Second Table shows the "Process Control" on a job...this is the ugly one
Job Number (Number, Long Int.)
Sequence01 (Text,50)
Sequence02 (Text,50)
....
Sequence17 (Text,50)

I can get the query that shows the Job# and Operation from table 1, but I
need all the gathered information to be in order according to the sequences
in table 2, as in seq01, seq02, and so on.
BTW, this will be for use in a report if that makes a difference.

So I'm stumped on a Friday and will be in and out of here this weekend, any
help is greatly appreciated.
TIA, Joe
 
I don't think that is going to be enough info. IF you only had
sequence1,2 and 3, how would you want your results? From what you
wrote, I would say you just want sort ascending on Sequence1, then
Sequence2,...sequence17, but that would be simple. Something must be
missing...a couple of examples would help.
 
OK, examples, no problem
in the 1st table, showing just the Job# & operation fields, you would have
these records... (BTW there are other fields for time on job, employee#,
date, etc., I figure those are really unimportant at this stage, their
uniqueness is identified by an autonum used as a PK, which I'm not showing)

14000, 'Incoming Inspection'
14000, 'Final Inspection'
14000, 'Deburr Inspection'
14000, 'Deburr'


in the 2nd table, there is 1 record for Job#14000...the Job# field, then the
17 sequence fields (some MAY be blank), 1 record will always contain the Job#
and the 17 fields of 'Sequence01'...'Sequence17' and the Job# is PK
14000, 'Incoming Inspection','Deburr','Deburr Inspection','Final
Inspection','','','','','','','','','','','',''

If I try to base a report on the 1st table, it alphabetizes the report, what
I really need is to show the information in the order as it's listed in the
second table...so the results should look and be sorted like this...

14000, 'Incoming Inspection', ...(other fields)
14000, 'Deburr', ...(other fields)
14000, 'Deburr Inspection', ...(other fields)
14000, 'Final Inspection', ...(other fields)

I hope that clarifies it a little better, but if not, let me know and I'll
try again.
Thanks again, Joe
 
That is the kind of trouble you get when you use a spread sheet.
Use a union query like this and continue for all your fields ---
SELECT Joe.Job, Joe.Sequence01, 1 AS Seq
FROM Joe
UNION ALL SELECT Joe.Job, Joe.Sequence02, 2 AS Seq
FROM Joe
UNION ALL SELECT Joe.Job, Joe.Sequence03, 3 AS Seq
FROM Joe
UNION ALL SELECT Joe.Job, Joe.Sequence04, 4 AS Seq
FROM Joe
UNION ALL SELECT Joe.Job, Joe.Sequence05, 5 AS Seq
FROM Joe
UNION ALL SELECT Joe.Job, Joe.Sequence06, 6 AS Seq
FROM Joe;
 
Karl, Thanks for the input. I am using a similar query for combobox contents
but can't see how I can use this in the way I need. I see how this will order
the second table, but how can I use this list to order the first table (or am
I just missing the whole point?)
I'll keep trying, please let me know if you have any further suggestions
cause I am definitely struggling with this one.
 
Maybe it has just been a lond weekend but you lost me. I thought you wanted
to report on the labor in the sequence it happened.
Please restate your problem.
 
Karl, I think it's me who had the long weekend, I started working with your
query, which gives me the correct sequence, I just needed a large dose of
caffeine to get it through my head...
~BAM~ Of course...you get the correct order of events through use of your
query, then stick in a sub-report to pull the corresponding info out of the
other table.
So thanks for kick-starting my brain on a Monday, guess Friday evenings are
a bad time to start new projects, sometimes the simplest solutions are the
hardest to solve (at least looking back it seems like a simple solution).
Anyway,
Thanks a million, I would probably still be struggling without your input.
 
Back
Top