Combining numbers

[M.Stanley]

Hi,
I'm attempting to create a query that will combine 2 columns of
numbers into one. The followng comes from 1 table with 4 fields
(A,B,C,D)

A B RESULT
700 000 700000
700 001 700001
700 002 700002
..
..
..
700 444 700444

c D RESULT
701 000 701000
701 001 701001
701 002 701002
..
..
..
701 444 701444

I have two problems that I need some some help with.

1. I'm having a problem in combining the numbers..when I try to
combine them using a expression ([A]+[b]) in a query, I get the
results of adding the two fields..not the combining them.

2. Once I can get the two fields combined, I'm going to have to sort
the whole thing as follows

700000
701000
700001
701001
700002
701002
..
..
..
700444
701444

I'm a beginner at using access...I realize it's potential, but little
things like this really trip me up.

Feel free to emai me with suggestions
Thanks,
Mark

[Rick Brandt]

"M.Stanley" <ms********@hot mail.com> wrote in message
news:79******** *************** **@posting.goog le.com...

Hi,
I'm attempting to create a query that will combine 2 columns of
numbers into one. The followng comes from 1 table with 4 fields
(A,B,C,D)

A B RESULT
700 000 700000 [snip] I have two problems that I need some some help with.

1. I'm having a problem in combining the numbers..when I try to
combine them using a expression ([A]+[b]) in a query, I get the
results of adding the two fields..not the combining them.
In Access the operator for concatenating text is the & symbol. The + symbol can
be used if the data is clearly text (contains alpha characters), but as you have
found if the text contains only digits Access will assume that the + should be
applied as an arithmetic addition rather than concatenation.
2. Once I can get the two fields combined, I'm going to have to sort
the whole thing as follows

700000
701000

Once you have the desired output in the query simply add "Ascending" in the sort
row of the query design grid underneath the desired column.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Bob Quintal]

ms********@hotm ail.com (M.Stanley) wrote in
news:79******** *************** **@posting.goog le.com:

Hi,
I'm attempting to create a query that will combine 2 columns
of numbers into one. The followng comes from 1 table with 4
fields (A,B,C,D)

A B RESULT
700 000 700000
700 001 700001
700 002 700002
.
.
.
700 444 700444

c D RESULT
701 000 701000
701 001 701001
701 002 701002
.
.
.
701 444 701444

I have two problems that I need some some help with.

1. I'm having a problem in combining the numbers..when I try
to combine them using a expression ([A]+[b]) in a query, I get
the results of adding the two fields..not the combining them.

what you want to do is create a string that contains the two
numbers. Use the concatenation operator, (&) instead of the
summation operator (+). The problem is that leading zeroes
sometimes get messed up, so 700 000 mifht become 7000 and 701 001
shows as 7011. There's a fix for that too. It's the format
function, so the expression in your query winds up being
Format([A],"000") & format([b],"000")

2. Once I can get the two fields combined, I'm going to have
to sort the whole thing as follows

700000
701000
700001
701001
700002
701002
.
.
.
700444
701444
in the query grid, put a duplicate set of the fields you want to
sort on, in the order to sort by starting from left to right, in
your case b then a. uncheck the show: checkbox and choose
ascending in the sort: field

I'm a beginner at using access...I realize it's potential, but
little things like this really trip me up.

Feel free to emai me with suggestions
Bad thing to ask. Responses should go to the newsgroup so that
everyone can participate, and learn.

Thanks,
Mark

--
Bob Quintal

PA is y I've altered my email address.

[M.Stanley]

Thanks for your help; I'm going to try your fixes ASAP and post my
results to the group.

Mark
Bob Quintal <rq******@sPAmp atico.ca> wrote in message news:<Xn******* *************** @66.150.105.50> ...

ms********@hotm ail.com (M.Stanley) wrote in
news:79******** *************** **@posting.goog le.com:

Hi,
I'm attempting to create a query that will combine 2 columns
of numbers into one. The followng comes from 1 table with 4
fields (A,B,C,D)

A B RESULT
700 000 700000
700 001 700001
700 002 700002
.
.
.
700 444 700444

c D RESULT
701 000 701000
701 001 701001
701 002 701002
.
.
.
701 444 701444

I have two problems that I need some some help with.

1. I'm having a problem in combining the numbers..when I try
to combine them using a expression ([A]+[b]) in a query, I get
the results of adding the two fields..not the combining them.

what you want to do is create a string that contains the two
numbers. Use the concatenation operator, (&) instead of the
summation operator (+). The problem is that leading zeroes
sometimes get messed up, so 700 000 mifht become 7000 and 701 001
shows as 7011. There's a fix for that too. It's the format
function, so the expression in your query winds up being
Format([A],"000") & format([b],"000")

2. Once I can get the two fields combined, I'm going to have
to sort the whole thing as follows

700000
701000
700001
701001
700002
701002
.
.
.
700444
701444

in the query grid, put a duplicate set of the fields you want to
sort on, in the order to sort by starting from left to right, in
your case b then a. uncheck the show: checkbox and choose
ascending in the sort: field

I'm a beginner at using access...I realize it's potential, but
little things like this really trip me up.

Feel free to emai me with suggestions

Bad thing to ask. Responses should go to the newsgroup so that
everyone can participate, and learn.

Thanks,
Mark

[M.Stanley]

Hi,
I was able to get the first part to work, by using the '&' operator
instead of the '+'..but I'm a little lost on how I'm going to change
the sort. At this point I have two columns of joined fields a&c and
c&d. Any clarfication is appreciated.

Thanks,
Mark

ms********@hotm ail.com (M.Stanley) wrote in message news:<79******* *************** ****@posting.go ogle.com>...

Thanks for your help; I'm going to try your fixes ASAP and post my
results to the group.

Mark
Bob Quintal <rq******@sPAmp atico.ca> wrote in message news:<Xn******* *************** @66.150.105.50> ...

ms********@hotm ail.com (M.Stanley) wrote in
news:79******** *************** **@posting.goog le.com:

Hi,
I'm attempting to create a query that will combine 2 columns
of numbers into one. The followng comes from 1 table with 4
fields (A,B,C,D)

A B RESULT
700 000 700000
700 001 700001
700 002 700002
.
.
.
700 444 700444

c D RESULT
701 000 701000
701 001 701001
701 002 701002
.
.
.
701 444 701444

I have two problems that I need some some help with.

1. I'm having a problem in combining the numbers..when I try
to combine them using a expression ([A]+[b]) in a query, I get
the results of adding the two fields..not the combining them.

what you want to do is create a string that contains the two
numbers. Use the concatenation operator, (&) instead of the
summation operator (+). The problem is that leading zeroes
sometimes get messed up, so 700 000 mifht become 7000 and 701 001
shows as 7011. There's a fix for that too. It's the format
function, so the expression in your query winds up being
Format([A],"000") & format([b],"000")

2. Once I can get the two fields combined, I'm going to have
to sort the whole thing as follows

700000
701000
700001
701001
700002
701002
.
.
.
700444
701444

in the query grid, put a duplicate set of the fields you want to
sort on, in the order to sort by starting from left to right, in
your case b then a. uncheck the show: checkbox and choose
ascending in the sort: field

I'm a beginner at using access...I realize it's potential, but
little things like this really trip me up.

Feel free to emai me with suggestions

Bad thing to ask. Responses should go to the newsgroup so that
everyone can participate, and learn.

Thanks,
Mark

[M.Stanley]

Please ignore my previous post...I got the sort to work...thank you
for all of your help.

Mark
ms********@hotm ail.com (M.Stanley) wrote in message news:<79******* *************** ****@posting.go ogle.com>...

Thanks for your help; I'm going to try your fixes ASAP and post my
results to the group.

Mark
Bob Quintal <rq******@sPAmp atico.ca> wrote in message news:<Xn******* *************** @66.150.105.50> ...

ms********@hotm ail.com (M.Stanley) wrote in
news:79******** *************** **@posting.goog le.com:

Hi,
I'm attempting to create a query that will combine 2 columns
of numbers into one. The followng comes from 1 table with 4
fields (A,B,C,D)

A B RESULT
700 000 700000
700 001 700001
700 002 700002
.
.
.
700 444 700444

c D RESULT
701 000 701000
701 001 701001
701 002 701002
.
.
.
701 444 701444

I have two problems that I need some some help with.

1. I'm having a problem in combining the numbers..when I try
to combine them using a expression ([A]+[b]) in a query, I get
the results of adding the two fields..not the combining them.

what you want to do is create a string that contains the two
numbers. Use the concatenation operator, (&) instead of the
summation operator (+). The problem is that leading zeroes
sometimes get messed up, so 700 000 mifht become 7000 and 701 001
shows as 7011. There's a fix for that too. It's the format
function, so the expression in your query winds up being
Format([A],"000") & format([b],"000")

2. Once I can get the two fields combined, I'm going to have
to sort the whole thing as follows

700000
701000
700001
701001
700002
701002
.
.
.
700444
701444

in the query grid, put a duplicate set of the fields you want to
sort on, in the order to sort by starting from left to right, in
your case b then a. uncheck the show: checkbox and choose
ascending in the sort: field

I'm a beginner at using access...I realize it's potential, but
little things like this really trip me up.

Feel free to emai me with suggestions

Bad thing to ask. Responses should go to the newsgroup so that
everyone can participate, and learn.

Thanks,
Mark