joining multiple fields in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I am sure I learned how to do this at one point way back when but, I
can't remember how. I have two different sets of data:
UWI is made up of the fields: LE, LSD, SEC, TWP, RGE, MER and should look
like:
100/14-30-075-06W5
NTS is made up of the fields: QUnit, Except, Unit, 4Block, Map, 6Block,
7Block and should look like: A-B-005-B/097-A-15
The reason I have the fields split out is because I needed to allow the
users to search on any one field, or multiple fields.
You will always have either a UWI or a NTS but not both. I am trying to
figure out how to make my query join all the UWI fields together into one
field and all the NTS fields together into one field (with the appropriate
-'s and /'s) so that I can put them into my reports and then set the blank
one to not be visible.

Is this possible? Can someone please point me in the right direction?
Thanks in advance - don't know what I'd do without this discussion group!
 
Do you mean having a new field UWI as LE & "/" & LSD & "-" & SEC & "-" & TWP
& "-" & RGE & MER?

The same for NTS. Use ampersand for concatenation.

Herbert
 
That is what I mean! Gave it a try in my query but must be doing something
wrong - I have the following currently listed in Field (I tried to call the
field UWI_JOIN) but it doesn't seem to be working:

Expr1: [UWI_JOIN]=[LE] & "/" & [LSD] & "-" & [SEC] & "-" & [TWP] & "-" &
[RGE] & "" & [MER]

Am I supposed to create a new field in my table?

Thanks
 
Yes, you're supposed to create a new field. And if you don't need the
separate fields, you don't need them in the query.

How is it not working?

Herbert

carriey said:
That is what I mean! Gave it a try in my query but must be doing something
wrong - I have the following currently listed in Field (I tried to call the
field UWI_JOIN) but it doesn't seem to be working:

Expr1: [UWI_JOIN]=[LE] & "/" & [LSD] & "-" & [SEC] & "-" & [TWP] & "-" &
[RGE] & "" & [MER]

Am I supposed to create a new field in my table?

Thanks

Herbert Chan said:
Do you mean having a new field UWI as LE & "/" & LSD & "-" & SEC & "-" & TWP
& "-" & RGE & MER?

The same for NTS. Use ampersand for concatenation.

Herbert

group!
Click to expand...
Click to expand...
 
Hi, I created two new fields in my table "UWI_JOIN" and "NTS_Join" then in
my query I have selected the UWI_JOIN field and then I have built the
expression below. Nothing comes up in that field - it is blank so I am
wondering if I am missing something?

Herbert Chan said:
Yes, you're supposed to create a new field. And if you don't need the
separate fields, you don't need them in the query.

How is it not working?

Herbert

carriey said:
That is what I mean! Gave it a try in my query but must be doing something
wrong - I have the following currently listed in Field (I tried to call the
field UWI_JOIN) but it doesn't seem to be working:

Expr1: [UWI_JOIN]=[LE] & "/" & [LSD] & "-" & [SEC] & "-" & [TWP] & "-" &
[RGE] & "" & [MER]

Am I supposed to create a new field in my table?

Thanks

Herbert Chan said:
Do you mean having a new field UWI as LE & "/" & LSD & "-" & SEC & "-" & TWP
& "-" & RGE & MER?

The same for NTS. Use ampersand for concatenation.

Herbert

"carriey" <[email protected]> |b?l¢Do
?????g...
Hi, I am sure I learned how to do this at one point way back when but, I
can't remember how. I have two different sets of data:
UWI is made up of the fields: LE, LSD, SEC, TWP, RGE, MER and should look
like:
100/14-30-075-06W5
NTS is made up of the fields: QUnit, Except, Unit, 4Block, Map, 6Block,
7Block and should look like: A-B-005-B/097-A-15
The reason I have the fields split out is because I needed to allow the
users to search on any one field, or multiple fields.
You will always have either a UWI or a NTS but not both. I am trying to
figure out how to make my query join all the UWI fields together into one
field and all the NTS fields together into one field (with the appropriate
-'s and /'s) so that I can put them into my reports and then set the blank
one to not be visible.

Is this possible? Can someone please point me in the right direction?
Thanks in advance - don't know what I'd do without this discussion group!
Click to expand...
Click to expand...
Click to expand...
 
Hi, I am sure I learned how to do this at one point way back when but, I
can't remember how. I have two different sets of data:
UWI is made up of the fields: LE, LSD, SEC, TWP, RGE, MER and should look
like:
100/14-30-075-06W5
NTS is made up of the fields: QUnit, Except, Unit, 4Block, Map, 6Block,
7Block and should look like: A-B-005-B/097-A-15
The reason I have the fields split out is because I needed to allow the
users to search on any one field, or multiple fields.
You will always have either a UWI or a NTS but not both. I am trying to
figure out how to make my query join all the UWI fields together into one
field and all the NTS fields together into one field (with the appropriate
-'s and /'s) so that I can put them into my reports and then set the blank
one to not be visible.

Is this possible? Can someone please point me in the right direction?
Thanks in advance - don't know what I'd do without this discussion group!
Click to expand...

You can take advantage of the fact that both the & and + operators
concatenate strings, but they handle NULL differently. & treats NULL
as a zero length string, but + "propagataes nulls" - [LE] + [LSD] +
[SEC] + [TWP] + [RGE] + [MER] will be NULL if any one of the six
fields is NULL.

You should be able to set the Control Source of a report textbox to

=[LE] + "/" + [LSD] + "-" + [SEC] + "-" + [TWP] + [RGE] + "-" + [MER]

and similarly for the NTS.

If you set the Can Grow and Can Shrink properties of the textboxes to
true, the one which contains NULL (if any of its constituent fields
are null) will disappear.

John W. Vinson[MVP]
 
Ah-Ha! I took out the spaces between the &'s and "'s and now it works.
Thanks again for your time
 
Well, I am no sure what you are doing, but it looks like you want an
update query.

Based on what you wrote, you want two extra fields. you want to
populate the first and sencond fields with existing values on the same
table.

Try this:

Since you haven't provided an example of the fields' values, and
assuming that all of the fields have the same type, modify the table
structure and add two more fields with the correct field size and type
(f1 and f2) as the other fields. Then create an update query:

first query
update tbl1
set f1 = le & '-' & lsd & '-' & sec & '-' & twp & '-' & rge & '-' &
mer

second query
update tbl1
set f2 = qunit & '-' & except & '-' & unit & '-' & 4block & '-' & map
& '-' & 6block & '-' & 7Block

I don't know where the hyphens or the forward slashes should go. I
hope this helps. These two samples might give you an idea of which
way you should go.

ja
 
Thanks John, the + works even better as I've been trying to figure out to get
rid of the -'s and /'s for blank records so that I only show the UWI or NTS
(whichever is applicable). Now, I have a problem because sometimes with NTS
there is no record in the field "Except" (exception) so of course, then the
NTS doesn't show up because that field is blank.

I have tried putting +'s through the expression and &'s around the Except
but that didn't get the result I want. Is there someway I can make this work
even if "Except" is Null?

Thanks!

John Vinson said:
Hi, I am sure I learned how to do this at one point way back when but, I
can't remember how. I have two different sets of data:
UWI is made up of the fields: LE, LSD, SEC, TWP, RGE, MER and should look
like:
100/14-30-075-06W5
NTS is made up of the fields: QUnit, Except, Unit, 4Block, Map, 6Block,
7Block and should look like: A-B-005-B/097-A-15
The reason I have the fields split out is because I needed to allow the
users to search on any one field, or multiple fields.
You will always have either a UWI or a NTS but not both. I am trying to
figure out how to make my query join all the UWI fields together into one
field and all the NTS fields together into one field (with the appropriate
-'s and /'s) so that I can put them into my reports and then set the blank
one to not be visible.

Is this possible? Can someone please point me in the right direction?
Thanks in advance - don't know what I'd do without this discussion group!
Click to expand...

You can take advantage of the fact that both the & and + operators
concatenate strings, but they handle NULL differently. & treats NULL
as a zero length string, but + "propagataes nulls" - [LE] + [LSD] +
[SEC] + [TWP] + [RGE] + [MER] will be NULL if any one of the six
fields is NULL.

You should be able to set the Control Source of a report textbox to

=[LE] + "/" + [LSD] + "-" + [SEC] + "-" + [TWP] + [RGE] + "-" + [MER]

and similarly for the NTS.

If you set the Can Grow and Can Shrink properties of the textboxes to
true, the one which contains NULL (if any of its constituent fields
are null) will disappear.

John W. Vinson[MVP]
Click to expand...
 
Actually - I had a & at the beginning of the "except" and a + at the end
which is why it wasn't working. When I change it to an & on either side and
+'s through the rest of the expression, it works. My only problem is one
pesky "-" after the "except" that shows up where the NTS should be totally
blank.

Any ideas how to get rid of this, or am I just going to have to live with it?

Thanks again - you guys are so helpful!

carriey said:
Thanks John, the + works even better as I've been trying to figure out to get
rid of the -'s and /'s for blank records so that I only show the UWI or NTS
(whichever is applicable). Now, I have a problem because sometimes with NTS
there is no record in the field "Except" (exception) so of course, then the
NTS doesn't show up because that field is blank.

I have tried putting +'s through the expression and &'s around the Except
but that didn't get the result I want. Is there someway I can make this work
even if "Except" is Null?

Thanks!

John Vinson said:
Hi, I am sure I learned how to do this at one point way back when but, I
can't remember how. I have two different sets of data:
UWI is made up of the fields: LE, LSD, SEC, TWP, RGE, MER and should look
like:
100/14-30-075-06W5
NTS is made up of the fields: QUnit, Except, Unit, 4Block, Map, 6Block,
7Block and should look like: A-B-005-B/097-A-15
The reason I have the fields split out is because I needed to allow the
users to search on any one field, or multiple fields.
You will always have either a UWI or a NTS but not both. I am trying to
figure out how to make my query join all the UWI fields together into one
field and all the NTS fields together into one field (with the appropriate
-'s and /'s) so that I can put them into my reports and then set the blank
one to not be visible.

Is this possible? Can someone please point me in the right direction?
Thanks in advance - don't know what I'd do without this discussion group!
Click to expand...

You can take advantage of the fact that both the & and + operators
concatenate strings, but they handle NULL differently. & treats NULL
as a zero length string, but + "propagataes nulls" - [LE] + [LSD] +
[SEC] + [TWP] + [RGE] + [MER] will be NULL if any one of the six
fields is NULL.

You should be able to set the Control Source of a report textbox to

=[LE] + "/" + [LSD] + "-" + [SEC] + "-" + [TWP] + [RGE] + "-" + [MER]

and similarly for the NTS.

If you set the Can Grow and Can Shrink properties of the textboxes to
true, the one which contains NULL (if any of its constituent fields
are null) will disappear.

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
You had better post your query here.

Please choose View SQL and post the SQL here.

Herbert

carriey said:
Hi, I created two new fields in my table "UWI_JOIN" and "NTS_Join" then
in
my query I have selected the UWI_JOIN field and then I have built the
expression below. Nothing comes up in that field - it is blank so I am
wondering if I am missing something?

Herbert Chan said:
Yes, you're supposed to create a new field. And if you don't need the
separate fields, you don't need them in the query.

How is it not working?

Herbert

carriey said:
That is what I mean! Gave it a try in my query but must be doing something
wrong - I have the following currently listed in Field (I tried to call the
field UWI_JOIN) but it doesn't seem to be working:

Expr1: [UWI_JOIN]=[LE] & "/" & [LSD] & "-" & [SEC] & "-" & [TWP] & "-"
&
[RGE] & "" & [MER]

Am I supposed to create a new field in my table?

Thanks

:

Do you mean having a new field UWI as LE & "/" & LSD & "-" & SEC &
"-" & TWP
& "-" & RGE & MER?

The same for NTS. Use ampersand for concatenation.

Herbert

"carriey" <[email protected]> |b?l¢FDo
?????g...
Hi, I am sure I learned how to do this at one point way back when but, I
can't remember how. I have two different sets of data:
UWI is made up of the fields: LE, LSD, SEC, TWP, RGE, MER and
should look
like:
100/14-30-075-06W5
NTS is made up of the fields: QUnit, Except, Unit, 4Block, Map, 6Block,
7Block and should look like: A-B-005-B/097-A-15
The reason I have the fields split out is because I needed to allow the
users to search on any one field, or multiple fields.
You will always have either a UWI or a NTS but not both. I am
trying to
figure out how to make my query join all the UWI fields together
into one
field and all the NTS fields together into one field (with the appropriate
-'s and /'s) so that I can put them into my reports and then set
the blank
one to not be visible.

Is this possible? Can someone please point me in the right
direction?
Thanks in advance - don't know what I'd do without this discussion group!
Click to expand...
Click to expand...
Click to expand...
 
Actually - I had a & at the beginning of the "except" and a + at the end
which is why it wasn't working. When I change it to an & on either side and
+'s through the rest of the expression, it works. My only problem is one
pesky "-" after the "except" that shows up where the NTS should be totally
blank.
Click to expand...

Please post the actual expression you're using. I can't visualize it!

You might need to use parentheses:

.... & ("-" + [NTS]) + "-")

or IIF:

+ IIF(IsNull([NTS], "", "-" & [NTS])

John W. Vinson[MVP]
 
Back
Top