Sorting Question - Urgent

[Turk Ries]

I have an Access DB that I need to sort my own way. For example, I need to
make the query sort by 123, 123, 123. Not by 111, 222, 333.

Please help as I am under the gun on this project. I can add any number of
fields or columns to achieve the desired results.

Thanks,
Turk

 

[BruceM]

If you are in a hurry you need to give us something to work with. You will
need to descirbe the rules by which you would sort, and include some
examples. You could sort the second set of three numbers in ascending or
descending order, but they have no apparent connection to the first set of
three numbers. Are they different fields in the same record, or different
records, or what? If you are trying to sort numbers stored as text you will
need to convert them to numbers, perhaps by using the Val function, if you
wish to sort in numerical order.

 

[Turk Ries]

Well, I have a range of products that I need to schedule. I have all of these
products in 3 categories ( 1 thru 3 ). I need to figure out a way to sort
these categories in an order of 1,2,3, 1,2,3, 1,2,3. I can currently only
figure out how to make it go 1,1,1, 2,2,2, 3,3,3, or in reverse.

Does this help?

Thanks,
Turk

 

[BruceM]

The order you are describing is arbitrary, based on what you have said so
far. Is there a logical reason behind the sort order? That is, are you
sorting by product, then category, or something like that? Some sample data
would help.

 

[Turk Ries]

Yes, there is a reason for this. Hopefully this will help you understand.

We manufacture fiberglass products and we are in the process of trying to
implement "lean manufacturing". This concept requires us to schedule parts to
be built in a sequential order. In order to accomplish this, we determined
that each part (of which we have 100's of on a daily basis) needs to be built
according to how much time it takes to build them. We have come up with
actually 6 different time categories that we need to run the parts in.

With that in mind, we assigned various parts with a time category. I.E.,
part #"A" is Time 1. Part "B" is Time 2. Part "C" is Time 3 and so on.

I cannot schedule 2 part "A's" together. That is why I need to sort my
schedule as 1,2,3 - 1,2,3 - 1,2,3 and so on.

I really appreciate the time you're taking to help me figure this out.

Thanks,
Turk

 

[Turk Ries]

Yes, there is a reason for this. Hopefully this will help you understand.

We manufacture fiberglass products and we are in the process of trying to
implement "lean manufacturing". This concept requires us to schedule parts to
be built in a sequential order. In order to accomplish this, we determined
that each part (of which we have 100's of on a daily basis) needs to be built
according to how much time it takes to build them. We have come up with
actually 6 different time categories that we need to run the parts in.

With that in mind, we assigned various parts with a time category. I.E.,
part #"A" is Time 1. Part "B" is Time 2. Part "C" is Time 3 and so on.

I cannot schedule 2 part "A's" together. That is why I need to sort my
schedule as 1,2,3 - 1,2,3 - 1,2,3 and so on.

I really appreciate the time you're taking to help me figure this out.

Thanks,
Turk

 

[Turk Ries]

I didn't mean to double post, I wasn't aware that this was the same
discussion group that I found on Mcrosoft's website.

Thanks,
Turk

 

[BruceM]

So you could have this, as I understand:

Part Time
A 1
C 2
B 3
D 1
G 2
B 3

If so, the only thing I can see is to add an extra column for a sort order,
as the only logic is that the time field is in the order 123, 123, etc. (or
123456, 123456, as I understand). This is what I meant by sample data, by
the way: the list as it should be, along with other fields that affect the
sort order.

One problem is that even if you can contrive to sort 123, 123 there is no
way to determine which Time1 goes to the top of the list, which one comes
second, and so forth. There may be a query-based solution to what you are
trying to do, in which case you may get help if you start a new thread in
the Queries group, but I expect they will ask the same thing I have about
how to determine the sort order beyond the 123, 123. That is, which 123
comes first?

 

[Turk Ries]

Yes, that's how I want it to sort. It also doesn't matter which "Time1" goes
to the top of the list.

Also, I have two tables. One is my mold list and the other is my schedule.
In my mold table is where I have my time1 (etc.) fields. On my data entry
form, when I schedule a new part, I have a drop down list that looks up the
values from my mold table to add that data to my schedule. Therefore, my
"time1" values will carry over to the schedule
..
That is why I want to sort the schedule 1,2,3, 1,2,3, etc.

I hope I'm not being dense on this, but how would I create a sort order,
as the only logic is that the time field is in the order 123, 123, etc.

Thanks,
Turk

 

[BruceM]

I don't see a solution other than to add a field to impose a sort order.
Again, a question in the Queries group may turn up an approach, but I can't
think of a way to do it without another field. Perhaps you could have a
Date/Time field with its default value set to Now(). When you build a
schedule each record will include the current time down to fractions of a
second. You can sort on that field.

Sorry I couldn't provide anything more specific.

 

[Fred]

One thought from my peanut gallery is that once you have a good table
structure (including the right fields for your products and mission) and
clear thinking about that a sort question is usually an easy one minute job.


My gut feel is that you should start with that, as, at least in your post,
you don't seem to be dealing much in table structure / table definitions
which is what makes everythign else go easily or badly. Unless "Mold" is
synonymous with "Part", there is a conflict in your basic discussion
because, per your last post, you have no "parts" table. Also, ones your
have a part?/mold? entered in a schedule record, you are talking about
"loading" (copying) attributes of the mold table into the schedule table,
something when is generally not needed and generally a bad idea.

Also, it sounds like your scheduling table really doesn't have fields to
schedule items, and that only a particular view of it that you seek is the
schedule, and that you are thinking that that view can be implemented by just
a sort, when in fact you really want it to implement other rules such as that
no mold can be used twice in a row.

In my opinion, I'd start by putting clear description of your situation that
you are databasing and your objectives into the "database design" section.

Hope that helps a little

 

[Turk Ries]

I can add all the fields that you want me to, I just don't know how to create
the sort order.

Thanks.

 

[Turk Ries]

I'm sorry, yes - "mold" and "part" is the same.

Yes, until now, my scheduling table had no need to be sorted in any order. I
can add any fields needed but, how do I implement other rules such as that no
mold can be used twice in a row.

That solution would probably work just as well as trying to sort them 1,2,3,
- 1,2,3, etc.

Thanks,

Turk