Change data arrangement

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

Guest

Is there a query or another way that I can change the way my data is arranged?
This is how the table is now:

Address, Name
105W 200N, Paul
203N 500E, Joe
1212S 100W, Rick
113S 200N, Bill
105W 200N, Sue
1212S 100W, Ron
105W 200N, Bill

This is how I would like it arranged so I can use it to label a map in a GIS
application. I need to label the map with all occupants residing at the
specific address. The GIS application can only join the spatial table and
data table in a one-to-one relationship.

Address, Name1, Name2, Name3
105W 200N, Paul, Sue, Bill
203N 500E, Joe
1212S 100W, Rick, Ron
113S 200N, Bill
 
Thanks, I think this will work for me if I can figure out how to make the
"concatenate" module work between two text fields. My link fields between
the two tables are text fields (address).

AllMembers: Concatenate("SELECT LName FROM tblMembers WHERE Address =" &
[Address]).

I get an error because Address on the many side is a text field, I do not
have ID fields to link on between the two tables, only the house address.

Do you know what I need to do to get this to work?

Thanks
 
Try:
AllMembers: Concatenate("SELECT LName FROM tblMembers WHERE Address =""" &
[Address] & """").

--
Duane Hookom
MS Access MVP
--

abajo said:
Thanks, I think this will work for me if I can figure out how to make the
"concatenate" module work between two text fields. My link fields between
the two tables are text fields (address).

AllMembers: Concatenate("SELECT LName FROM tblMembers WHERE Address =" &
[Address]).

I get an error because Address on the many side is a text field, I do not
have ID fields to link on between the two tables, only the house address.

Do you know what I need to do to get this to work?

Thanks

Duane Hookom said:
There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
Click to expand...
Click to expand...
 
Thanks, that did the trick!

Duane Hookom said:
Try:
AllMembers: Concatenate("SELECT LName FROM tblMembers WHERE Address =""" &
[Address] & """").

--
Duane Hookom
MS Access MVP
--

abajo said:
Thanks, I think this will work for me if I can figure out how to make the
"concatenate" module work between two text fields. My link fields between
the two tables are text fields (address).

AllMembers: Concatenate("SELECT LName FROM tblMembers WHERE Address =" &
[Address]).

I get an error because Address on the many side is a text field, I do not
have ID fields to link on between the two tables, only the house address.

Do you know what I need to do to get this to work?

Thanks

Duane Hookom said:
There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP
--

Is there a query or another way that I can change the way my data is
arranged?
This is how the table is now:

Address, Name
105W 200N, Paul
203N 500E, Joe
1212S 100W, Rick
113S 200N, Bill
105W 200N, Sue
1212S 100W, Ron
105W 200N, Bill

This is how I would like it arranged so I can use it to label a map in
a
GIS
application. I need to label the map with all occupants residing at
the
specific address. The GIS application can only join the spatial table
and
data table in a one-to-one relationship.

Address, Name1, Name2, Name3
105W 200N, Paul, Sue, Bill
203N 500E, Joe
1212S 100W, Rick, Ron
113S 200N, Bill
Click to expand...
Click to expand...
Click to expand...
 
Back
Top