Joining different parts of the same table

[Goldar]

I have a table (Table A) that contains Item_# and Description fields. I want
to create another table that contains all of the Table A records whose first
character of Item_# is numeric followed by all of the Table A records whose
first character of Item_# is alphabetic. The resulting table should contain
the numeric records in decending sequence and the alphabetic records in
ascending sequence. How can I structure a query to do this?

Resulting table example sequence:
4
3
2
1
A
B
C
D


Thanks

 

[vanderghast]

In SQL view, add (or modify the existing) an ORDER BY clause:


ORDER BY isNumeric(Left(stringExpression, 1)) ASC , val(stringExpression)
ASC, stringExpression ASC

will list the expression where the first character is a digit, and next, the
others. In each the first group, the string will be listed in increasing
order of the number starting the string, then ascendingly. In other words,
99a will appear before 100a, and 100a will appear before 100b.


Replace stringExpression by your field name.



Vanderghast, Access MVP

 

[KARL DEWEY]

Try this --
SELECT Left([Item_#], 1) AS [Results]
FROM [Table A]
WHERE IsNumeric(Left([Item_#], 1)) = -1
ORDER BY [Item_#]
UNION ALL SELECT Left([Description], 1) AS [Results]
FROM [Table A]
WHERE IsNumeric(Left([Description], 1)) = 0
ORDER BY [Description]

 

[Goldar]

While trying to make my example clear, I think I made it confusing. In the
real world, Table A contains an Item_# field made up of yyyy-q-nnn
data("2008-1-001", "2008-2-001","2009-2-002", etc). The Description field is
just a text field. The resulting table should contain
"2009-2-002","2008-2-001","2008-1-001", etc.

The source table contains:
"Group 1","Group 1 description"
"Group 2","Group 2 description"
"2008-1-001","2008 item-quarter 1-part 001"
"2008-2-001","2008 item-quarter 2-part 001"
"2009-2-002","2009 item-quarter 2-item 002"
"2009-2-003","2009 item-quarter 2-item 003"

The resulting table should contain
"2009-2-003","2009 item-quarter 2-part 003"
"2009-2-002","2009 item-quarter 2-part 002"
"2008-2-001","2008 item-quarter 2-part 001"
"2008-1-001","2008 item-quarter 1-part 001"
"Group 1","Group 1 description"
"Group 2","Group 2 description"

I hope this clears up my example. I tried the suggested approaches without
success.

 

[KARL DEWEY]

Did you try my posting?

While trying to make my example clear, I think I made it confusing. In the
real world, Table A contains an Item_# field made up of yyyy-q-nnn
data("2008-1-001", "2008-2-001","2009-2-002", etc). The Description field is
just a text field. The resulting table should contain
"2009-2-002","2008-2-001","2008-1-001", etc.

The source table contains:
"Group 1","Group 1 description"
"Group 2","Group 2 description"
"2008-1-001","2008 item-quarter 1-part 001"
"2008-2-001","2008 item-quarter 2-part 001"
"2009-2-002","2009 item-quarter 2-item 002"
"2009-2-003","2009 item-quarter 2-item 003"

The resulting table should contain
"2009-2-003","2009 item-quarter 2-part 003"
"2009-2-002","2009 item-quarter 2-part 002"
"2008-2-001","2008 item-quarter 2-part 001"
"2008-1-001","2008 item-quarter 1-part 001"
"Group 1","Group 1 description"
"Group 2","Group 2 description"

I hope this clears up my example. I tried the suggested approaches without
success.

 

[vanderghast]

Your first field seems to carry more than ONE atomic information, such as a
YEAR and a MONTH and something else.

Can't you get a real Date_time field and order on it, in a decreasing order?
have a null as value for that date_time, for other records (what those
other records are doing there is another question): null-s values are
ordered together, anyhow, so if you can have a date_time field, that will
simply help you much more than defining a home-made code/decode operation on
your data, which is a very very bad thing in a database.



Vanderghast, Access MVP

 

[Goldar]

Yes, I tried your posting and I'm not sure what the query did, but it wasn't
was I was looking for. See my new example of the data.

Thanks for your help.

 

[KARL DEWEY]

Try this --
SELECT Left([Item_#], 1) AS [Results]
FROM [Table A]
ORDER BY [Item_#];

 

[Goldar]

This abbreviated query works fine, and would solve my problem if I could add
alphabetic half of the source table to the end of this one. Any thoughts?

Try this --
SELECT Left([Item_#], 1) AS [Results]
FROM [Table A]
ORDER BY [Item_#];

Yes, I tried your posting and I'm not sure what the query did, but it wasn't
was I was looking for. See my new example of the data.

Thanks for your help.

 

[KARL DEWEY]

if I could add alphabetic half of the source table to the end of this one.
What do you mean by 'alphabetic half'?

This abbreviated query works fine, and would solve my problem if I could add
alphabetic half of the source table to the end of this one. Any thoughts?

Try this --
SELECT Left([Item_#], 1) AS [Results]
FROM [Table A]
ORDER BY [Item_#];

Yes, I tried your posting and I'm not sure what the query did, but it wasn't
was I was looking for. See my new example of the data.

Thanks for your help.

:

Did you try my posting?

:

While trying to make my example clear, I think I made it confusing. In the
real world, Table A contains an Item_# field made up of yyyy-q-nnn
data("2008-1-001", "2008-2-001","2009-2-002", etc). The Description field is
just a text field. The resulting table should contain
"2009-2-002","2008-2-001","2008-1-001", etc.

The source table contains:
"Group 1","Group 1 description"
"Group 2","Group 2 description"
"2008-1-001","2008 item-quarter 1-part 001"
"2008-2-001","2008 item-quarter 2-part 001"
"2009-2-002","2009 item-quarter 2-item 002"
"2009-2-003","2009 item-quarter 2-item 003"

The resulting table should contain
"2009-2-003","2009 item-quarter 2-part 003"
"2009-2-002","2009 item-quarter 2-part 002"
"2008-2-001","2008 item-quarter 2-part 001"
"2008-1-001","2008 item-quarter 1-part 001"
"Group 1","Group 1 description"
"Group 2","Group 2 description"

I hope this clears up my example. I tried the suggested approaches without
success.

:

I have a table (Table A) that contains Item_# and Description fields. I want
to create another table that contains all of the Table A records whose first
character of Item_# is numeric followed by all of the Table A records whose
first character of Item_# is alphabetic. The resulting table should contain
the numeric records in decending sequence and the alphabetic records in
ascending sequence. How can I structure a query to do this?

Resulting table example sequence:
4
3
2
1
A
B
C
D


Thanks

 

[Goldar]

A portion of Table A are records where the field Item_# is not numeric. I
need to be able to add these records in ascending sequence to the end of the
table that was created by your first query. That is what I mean by the
"alphabetic half" of the source table. I would like to be able to create
query that does both steps at once. Please note the sample input and output
records I have show below.

Thanks for all of your help.

What do you mean by 'alphabetic half'?

This abbreviated query works fine, and would solve my problem if I could add
alphabetic half of the source table to the end of this one. Any thoughts?

Try this --
SELECT Left([Item_#], 1) AS [Results]
FROM [Table A]
ORDER BY [Item_#];


:

Yes, I tried your posting and I'm not sure what the query did, but it wasn't
was I was looking for. See my new example of the data.

Thanks for your help.

:

Did you try my posting?

:

While trying to make my example clear, I think I made it confusing. In the
real world, Table A contains an Item_# field made up of yyyy-q-nnn
data("2008-1-001", "2008-2-001","2009-2-002", etc). The Description field is
just a text field. The resulting table should contain
"2009-2-002","2008-2-001","2008-1-001", etc.

The source table contains:
"Group 1","Group 1 description"
"Group 2","Group 2 description"
"2008-1-001","2008 item-quarter 1-part 001"
"2008-2-001","2008 item-quarter 2-part 001"
"2009-2-002","2009 item-quarter 2-item 002"
"2009-2-003","2009 item-quarter 2-item 003"

The resulting table should contain
"2009-2-003","2009 item-quarter 2-part 003"
"2009-2-002","2009 item-quarter 2-part 002"
"2008-2-001","2008 item-quarter 2-part 001"
"2008-1-001","2008 item-quarter 1-part 001"
"Group 1","Group 1 description"
"Group 2","Group 2 description"

I hope this clears up my example. I tried the suggested approaches without
success.

:

I have a table (Table A) that contains Item_# and Description fields. I want
to create another table that contains all of the Table A records whose first
character of Item_# is numeric followed by all of the Table A records whose
first character of Item_# is alphabetic. The resulting table should contain
the numeric records in decending sequence and the alphabetic records in
ascending sequence. How can I structure a query to do this?

Resulting table example sequence:
4
3
2
1
A
B
C
D


Thanks

 

[KARL DEWEY]

My second post does that as far as I can tell. Are you not getting all
records?
There is nothing in the query to omit any records.
In fact you do not need anything but a select query with order by [Item #]
or so it seems to me.

The results should be like this ---
2008-1-001 2008 item-quarter 1-part 001
2008-2-001 2008 item-quarter 2-part 001
2009-2-002 2009 item-quarter 2-item 002
2009-2-003 2009 item-quarter 2-item 003
Group 1 Group 1 description
Group 2 Group 2 description

A portion of Table A are records where the field Item_# is not numeric. I
need to be able to add these records in ascending sequence to the end of the
table that was created by your first query. That is what I mean by the
"alphabetic half" of the source table. I would like to be able to create
query that does both steps at once. Please note the sample input and output
records I have show below.

Thanks for all of your help.

if I could add alphabetic half of the source table to the end of this one.

What do you mean by 'alphabetic half'?

This abbreviated query works fine, and would solve my problem if I could add
alphabetic half of the source table to the end of this one. Any thoughts?

:

Try this --
SELECT Left([Item_#], 1) AS [Results]
FROM [Table A]
ORDER BY [Item_#];


:

Yes, I tried your posting and I'm not sure what the query did, but it wasn't
was I was looking for. See my new example of the data.

Thanks for your help.

:

Did you try my posting?

:

While trying to make my example clear, I think I made it confusing. In the
real world, Table A contains an Item_# field made up of yyyy-q-nnn
data("2008-1-001", "2008-2-001","2009-2-002", etc). The Description field is
just a text field. The resulting table should contain
"2009-2-002","2008-2-001","2008-1-001", etc.

The source table contains:
"Group 1","Group 1 description"
"Group 2","Group 2 description"
"2008-1-001","2008 item-quarter 1-part 001"
"2008-2-001","2008 item-quarter 2-part 001"
"2009-2-002","2009 item-quarter 2-item 002"
"2009-2-003","2009 item-quarter 2-item 003"

The resulting table should contain
"2009-2-003","2009 item-quarter 2-part 003"
"2009-2-002","2009 item-quarter 2-part 002"
"2008-2-001","2008 item-quarter 2-part 001"
"2008-1-001","2008 item-quarter 1-part 001"
"Group 1","Group 1 description"
"Group 2","Group 2 description"

I hope this clears up my example. I tried the suggested approaches without
success.

:

I have a table (Table A) that contains Item_# and Description fields. I want
to create another table that contains all of the Table A records whose first
character of Item_# is numeric followed by all of the Table A records whose
first character of Item_# is alphabetic. The resulting table should contain
the numeric records in decending sequence and the alphabetic records in
ascending sequence. How can I structure a query to do this?

Resulting table example sequence:
4
3
2
1
A
B
C
D


Thanks