Sorting Records

[DS]

I Have Records that sort as such....
E1
E2
E4
E7
Q3
Q5
Z6

How can I make them sort....
E1
E2
Q3
E4
Q5
Z6
E7

Thanks
DS

 

[Rick B]

Just sort on the second digit.

In your sort put something like...

Mid([YourFiledName],2)

 

[Guest]

You can't sort in the table like that but you can create a query and add an
unshown column which is sorted ascending. The column should look something
like this
SortCrit: Right(YourField,1)

 

[Joseph Meehan]

I Have Records that sort as such....
E1
E2
E4
E7
Q3
Q5
Z6

How can I make them sort....
E1
E2
Q3
E4
Q5
Z6
E7

Thanks
DS

Since Rick and Dennis already told you how to sort I won't cover that,
but I wonder if you may have a bad table design.

From your question it would appear that the letter and the number each
have their own independent significance. It may be that you really should
have two fields one for the letters and the second for the numbers and then
join them for a unique ID.

 

[DS]

You can't sort in the table like that but you can create a query and add an
unshown column which is sorted ascending. The column should look something
like this
SortCrit: Right(YourField,1)

:

Thanks
I put Right([SalesID,1)
And it didn't work. Am I doing something wrong?
DS

 

[DS]

Just sort on the second digit.

In your sort put something like...

Mid([YourFiledName],2)

Thanks Rick.
Your help is always appreciated!
DS

 

[DS]

Since Rick and Dennis already told you how to sort I won't cover that,
but I wonder if you may have a bad table design.

From your question it would appear that the letter and the number each
have their own independent significance. It may be that you really should
have two fields one for the letters and the second for the numbers and then
join them for a unique ID.

OH BOY!!!!!!
I was afraid another can of worms would open!!!!
I have a SalesID field which is a text field. It has a DMax ststement
attached to it..... what happens is if its a Eat In ticket then the
SalesID Number is E plus the Number, If its Delivery then its D plus the
number...I might as well go on. If its QuikServ, then its Q, Take Out
is P and finaly Pick Up is P......I'm doing this so that the SalesID can
easily be reconized on the type it is from. Any suggestions are helpful...
Thank You
DS

 

[Guest]

You omitted the closing bracket ( ] ). It should read --
Right([SalesID],1)

You can't sort in the table like that but you can create a query and add an
unshown column which is sorted ascending. The column should look something
like this
SortCrit: Right(YourField,1)

:

Thanks
I put Right([SalesID,1)
And it didn't work. Am I doing something wrong?
DS

 

[Joseph Meehan]

OH BOY!!!!!!
I was afraid another can of worms would open!!!!
I have a SalesID field which is a text field. It has a DMax ststement
attached to it..... what happens is if its a Eat In ticket then the
SalesID Number is E plus the Number, If its Delivery then its D plus
the number...I might as well go on. If its QuikServ, then its Q,
Take Out is P and finaly Pick Up is P......I'm doing this so that the
SalesID
can easily be reconized on the type it is from. Any suggestions are
helpful... Thank You
DS

You can make your ID a two field ID.

Your records:

E1 E2 E4 E7 Q3 Q5 Z6

Would become

IDType IDNumber

E 1
E 2
E 4
E 7
Q 3
Q 5
Z 6

Then in a query or form you could use

IDMerg: [IDType]&[IDNumber] You could sort on number first than type and
that would give you the sort you want.

Note: the next question will be sorting

1, 2, 5, 10, 11

If you are using a number type field they will sort as above, if you are
using a text type field it will be more like

1, 10, 11, 2, 5.

 

[DS]

Joseph Meehan wrote:



OH BOY!!!!!!
I was afraid another can of worms would open!!!!
I have a SalesID field which is a text field. It has a DMax ststement
attached to it..... what happens is if its a Eat In ticket then the
SalesID Number is E plus the Number, If its Delivery then its D plus
the number...I might as well go on. If its QuikServ, then its Q,
Take Out is P and finaly Pick Up is P......I'm doing this so that the
SalesID
can easily be reconized on the type it is from. Any suggestions are
helpful... Thank You
DS

You can make your ID a two field ID.

Your records:

E1 E2 E4 E7 Q3 Q5 Z6

Would become

IDType IDNumber

E 1
E 2
E 4
E 7
Q 3
Q 5
Z 6

Then in a query or form you could use

IDMerg: [IDType]&[IDNumber] You could sort on number first than type and
that would give you the sort you want.

Note: the next question will be sorting

1, 2, 5, 10, 11

If you are using a number type field they will sort as above, if you are
using a text type field it will be more like

1, 10, 11, 2, 5.

Thank You. Would you suggest making it a 2 field as a opposed to a 1
field. Are there advantages to this? Is there a downside to the other
way? (The way it is now?) Any input appreciated.
DS

 

[Joseph Meehan]

Joseph Meehan wrote:


DS wrote:


OH BOY!!!!!!
I was afraid another can of worms would open!!!!
I have a SalesID field which is a text field. It has a DMax
ststement attached to it..... what happens is if its a Eat In ticket
then the SalesID Number is E plus the Number, If its Delivery then
its D plus the number...I might as well go on. If its QuikServ,
then its Q, Take Out is P and finaly Pick Up is P......I'm doing
this so that the SalesID
can easily be reconized on the type it is from. Any suggestions are
helpful... Thank You
DS

You can make your ID a two field ID.

Your records:

E1 E2 E4 E7 Q3 Q5 Z6

Would become

IDType IDNumber

E 1
E 2
E 4
E 7
Q 3
Q 5
Z 6

Then in a query or form you could use

IDMerg: [IDType]&[IDNumber] You could sort on number first than
type and that would give you the sort you want.

Note: the next question will be sorting

1, 2, 5, 10, 11

If you are using a number type field they will sort as above, if
you are using a text type field it will be more like

1, 10, 11, 2, 5.

Thank You. Would you suggest making it a 2 field as a opposed to a 1
field. Are there advantages to this? Is there a downside to the
other way? (The way it is now?) Any input appreciated.
DS

Well there is the advantage of being able to sort it the way you want.
It would also make it easier to select based on either type or number part
of the ID.

I would make them separate, because based on my experience it is likely
to cause less problems in the long run. However I would not say it was
wrong to do it the other way.

 

[DS]

Joseph Meehan wrote:

DS wrote:


Joseph Meehan wrote:



DS wrote:


OH BOY!!!!!!
I was afraid another can of worms would open!!!!
I have a SalesID field which is a text field. It has a DMax
ststement attached to it..... what happens is if its a Eat In ticket
then the SalesID Number is E plus the Number, If its Delivery then
its D plus the number...I might as well go on. If its QuikServ,
then its Q, Take Out is P and finaly Pick Up is P......I'm doing
this so that the SalesID
can easily be reconized on the type it is from. Any suggestions are
helpful... Thank You
DS


You can make your ID a two field ID.

Your records:

E1 E2 E4 E7 Q3 Q5 Z6

Would become

IDType IDNumber

E 1
E 2
E 4
E 7
Q 3
Q 5
Z 6

Then in a query or form you could use

IDMerg: [IDType]&[IDNumber] You could sort on number first than
type and that would give you the sort you want.

Note: the next question will be sorting

1, 2, 5, 10, 11

If you are using a number type field they will sort as above, if
you are using a text type field it will be more like

1, 10, 11, 2, 5.

Thank You. Would you suggest making it a 2 field as a opposed to a 1
field. Are there advantages to this? Is there a downside to the
other way? (The way it is now?) Any input appreciated.
DS

Well there is the advantage of being able to sort it the way you want.
It would also make it easier to select based on either type or number part
of the ID.

I would make them separate, because based on my experience it is likely
to cause less problems in the long run. However I would not say it was
wrong to do it the other way.

Thank You.....So it seems to be a matter of choice. Would the merged
field be the one to hold the real record, Since I'm using DMax...where
would it look to for the next avaiable number. The IDNumber, IDType
field...also I'm setting up for multiple users so I need to hols the
last number in one table, would that table hold the merged number?
Thanks
DS

 

[Joseph Meehan]

Joseph Meehan wrote:


DS wrote:


Joseph Meehan wrote:



DS wrote:


OH BOY!!!!!!
I was afraid another can of worms would open!!!!
I have a SalesID field which is a text field. It has a DMax
ststement attached to it..... what happens is if its a Eat In
ticket then the SalesID Number is E plus the Number, If its
Delivery then its D plus the number...I might as well go on. If
its QuikServ, then its Q, Take Out is P and finaly Pick Up is
P......I'm doing this so that the SalesID
can easily be reconized on the type it is from. Any suggestions
are helpful... Thank You
DS


You can make your ID a two field ID.

Your records:

E1 E2 E4 E7 Q3 Q5 Z6

Would become

IDType IDNumber

E 1
E 2
E 4
E 7
Q 3
Q 5
Z 6

Then in a query or form you could use

IDMerg: [IDType]&[IDNumber] You could sort on number first than
type and that would give you the sort you want.

Note: the next question will be sorting

1, 2, 5, 10, 11

If you are using a number type field they will sort as above, if
you are using a text type field it will be more like

1, 10, 11, 2, 5.


Thank You. Would you suggest making it a 2 field as a opposed to a 1
field. Are there advantages to this? Is there a downside to the
other way? (The way it is now?) Any input appreciated.
DS

Well there is the advantage of being able to sort it the way you
want. It would also make it easier to select based on either type or
number part of the ID.

I would make them separate, because based on my experience it is
likely to cause less problems in the long run. However I would not
say it was wrong to do it the other way.

Thank You.....So it seems to be a matter of choice. Would the merged
field be the one to hold the real record, Since I'm using DMax...where
would it look to for the next avaiable number. The IDNumber, IDType
field...also I'm setting up for multiple users so I need to hols the
last number in one table, would that table hold the merged number?
Thanks
DS

I assume you want a consecutive number. Since Access is not going to
give you that (Auto number is sort of like that but not all the time) you
will do to code your own and I don't have any sample code for you, but it is
often posted here. Maybe a search would find it, if not you can ask a
direct question on it.

My guess is that this would be a good reason for using separate fields
for the number portion and the letter portion. It also would allow you to
do some error checking on what letter someone enters.

 

[DS]

DS wrote:


Joseph Meehan wrote:



DS wrote:



Joseph Meehan wrote:




DS wrote:


OH BOY!!!!!!
I was afraid another can of worms would open!!!!
I have a SalesID field which is a text field. It has a DMax
ststement attached to it..... what happens is if its a Eat In
ticket then the SalesID Number is E plus the Number, If its
Delivery then its D plus the number...I might as well go on. If
its QuikServ, then its Q, Take Out is P and finaly Pick Up is
P......I'm doing this so that the SalesID
can easily be reconized on the type it is from. Any suggestions
are helpful... Thank You
DS


You can make your ID a two field ID.

Your records:

E1 E2 E4 E7 Q3 Q5 Z6

Would become

IDType IDNumber

E 1
E 2
E 4
E 7
Q 3
Q 5
Z 6

Then in a query or form you could use

IDMerg: [IDType]&[IDNumber] You could sort on number first than
type and that would give you the sort you want.

Note: the next question will be sorting

1, 2, 5, 10, 11

If you are using a number type field they will sort as above, if
you are using a text type field it will be more like

1, 10, 11, 2, 5.


Thank You. Would you suggest making it a 2 field as a opposed to a 1
field. Are there advantages to this? Is there a downside to the
other way? (The way it is now?) Any input appreciated.
DS


Well there is the advantage of being able to sort it the way you
want. It would also make it easier to select based on either type or
number part of the ID.

I would make them separate, because based on my experience it is
likely to cause less problems in the long run. However I would not
say it was wrong to do it the other way.

Thank You.....So it seems to be a matter of choice. Would the merged
field be the one to hold the real record, Since I'm using DMax...where
would it look to for the next avaiable number. The IDNumber, IDType
field...also I'm setting up for multiple users so I need to hols the
last number in one table, would that table hold the merged number?
Thanks
DS

I assume you want a consecutive number. Since Access is not going to
give you that (Auto number is sort of like that but not all the time) you
will do to code your own and I don't have any sample code for you, but it is
often posted here. Maybe a search would find it, if not you can ask a
direct question on it.

My guess is that this would be a good reason for using separate fields
for the number portion and the letter portion. It also would allow you to
do some error checking on what letter someone enters.

OK, I've got it now. The SalesID field is a numer field, The TypeID
field is a Text field, I put the DMax on the Number field so its always
going up from there. I have the type attached to the tables so thats
always there. Then when I need to run any report I use the Merge on
Query, but can I use it in code as well?......Doing it your way when I
setup the table numbers I put the type with it, so now I don't have to
hard code it and keep moving it around. Thank you, much appreciated.
Sincerely,
DS

 

[Joseph Meehan]

OK, I've got it now. The SalesID field is a numer field, The TypeID
field is a Text field, I put the DMax on the Number field so its
always going up from there. I have the type attached to the tables
so thats always there. Then when I need to run any report I use the
Merge on Query, but can I use it in code as well?......Doing it your
way when I setup the table numbers I put the type with it, so now I
don't have to hard code it and keep moving it around. Thank you,
much appreciated. Sincerely,
DS

I'm glad I might have been some help. Access does have something of a
steep learning curve, and every new idea throws in a new curve, but in the
long run it is well worth the time and effort.

 

[DS]

DS wrote:




I'm glad I might have been some help. Access does have something of a
steep learning curve, and every new idea throws in a new curve, but in the
long run it is well worth the time and effort.

Your right about a steep learning curve...everytime you thinkyou know a
lot...it seems you know less!!!!!
Once Again Thank You
DS