combining fields

I'm trying to group a number of fields from one table into one separate field.
Basically, the table lists address information: state, zip, city, street. I would like to display these four fields as one field in a query with the listed as subfields. Now, they need to be subfields, not just concatination of strings. The resulting query is then exported into an XML document and sent on to others for import into another database. (I'm trying to comply with their schema) I can get to a point where it will list the addresses, but all of the addresses in the table, not the one specific to the record which is what I need.
Any thoughts?
Thanks!

Not sure I understand what you want.

You can either pull the three fields to a query as separate fields, or you
can merge them and put them in one field. Not sure what a "subfield" is.
That is not an Access term.

To combine them, use something like...

FullAddress: [City] & ", " & [State] & " " & [Zip]



Rick B


"cutthroatjess" wrote in message
...
I'm trying to group a number of fields from one table into one separate
field.
Basically, the table lists address information: state, zip, city, street. I
would like to display these four fields as one field in a query with the
listed as subfields. Now, they need to be subfields, not just concatination
of strings. The resulting query is then exported into an XML document and
sent on to others for import into another database. (I'm trying to comply
with their schema) I can get to a point where it will list the addresses,
but all of the addresses in the table, not the one specific to the record
which is what I need.
Any thoughts?
Thanks!

Sorry, I know I wasn't very clear.
I understand your suggestion, but that isn't what is required of me. For example, here is the sample grouping from the XML schema:
CollectionAddress
CollectionAddr1XXXXXX/CollectionAddr1
CollectionAddr2XXXXXX/CollectionAddr2
CollectionCityXXXXXX/CollectionCity
CollectionStateXXXXXX/CollectionState
CollectionZipXXXXXX/CollectionZip
/CollectionAddress
Somehow, CollectionAddr1, CollectionAddr2, CollectionCity, CollectionState, and CollectionZip must appear as separate elements under the element CollectionAddress. When I export from Access to an XML file using your suggestion, it doesn't group those 5 elements as CollectionAddress, but each individually to look like:
CollectionAddr1XXXXXX/CollectionAddr1
CollectionAddr2XXXXXX/CollectionAddr2
CollectionCityXXXXXX/CollectionCity
CollectionStateXXXXXX/CollectionState
CollectionZipXXXXXX/CollectionZip

I've tried using joins and such, but it just won't populate the query right.


"Rick B" wrote:

Not sure I understand what you want.

You can either pull the three fields to a query as separate fields, or you
can merge them and put them in one field. Not sure what a "subfield" is.
That is not an Access term.

To combine them, use something like...

FullAddress: [City] & ", " & [State] & " " & [Zip]



Rick B


"cutthroatjess" wrote in message
...
I'm trying to group a number of fields from one table into one separate
field.
Basically, the table lists address information: state, zip, city, street. I
would like to display these four fields as one field in a query with the
listed as subfields. Now, they need to be subfields, not just concatination
of strings. The resulting query is then exported into an XML document and
sent on to others for import into another database. (I'm trying to comply
with their schema) I can get to a point where it will list the addresses,
but all of the addresses in the table, not the one specific to the record
which is what I need.
Any thoughts?
Thanks!

I would think you would need to build a report. You can sort and group. In
your sort you could create a group header and put in the collection
address, then in the details, put the other fields.

After viewing the report, I think there are ways to export it. I know you
can "Analyze it With Excel"

Hope that is closer to what you want.


Rick B


"cutthroatjess" wrote in message
news Sorry, I know I wasn't very clear.
I understand your suggestion, but that isn't what is required of me. For
example, here is the sample grouping from the XML schema:
CollectionAddress
CollectionAddr1XXXXXX/CollectionAddr1
CollectionAddr2XXXXXX/CollectionAddr2
CollectionCityXXXXXX/CollectionCity
CollectionStateXXXXXX/CollectionState
CollectionZipXXXXXX/CollectionZip
/CollectionAddress
Somehow, CollectionAddr1, CollectionAddr2, CollectionCity, CollectionState,
and CollectionZip must appear as separate elements under the element
CollectionAddress. When I export from Access to an XML file using your
suggestion, it doesn't group those 5 elements as CollectionAddress, but each
individually to look like:
CollectionAddr1XXXXXX/CollectionAddr1
CollectionAddr2XXXXXX/CollectionAddr2
CollectionCityXXXXXX/CollectionCity
CollectionStateXXXXXX/CollectionState
CollectionZipXXXXXX/CollectionZip

I've tried using joins and such, but it just won't populate the query right.


"Rick B" wrote:

Not sure I understand what you want.

You can either pull the three fields to a query as separate fields, or you
can merge them and put them in one field. Not sure what a "subfield" is.
That is not an Access term.

To combine them, use something like...

FullAddress: [City] & ", " & [State] & " " & [Zip]



Rick B


"cutthroatjess" wrote in message
...
I'm trying to group a number of fields from one table into one separate
field.
Basically, the table lists address information: state, zip, city, street.
I
would like to display these four fields as one field in a query with the
listed as subfields. Now, they need to be subfields, not just
concatination
of strings. The resulting query is then exported into an XML document and
sent on to others for import into another database. (I'm trying to comply
with their schema) I can get to a point where it will list the addresses,
but all of the addresses in the table, not the one specific to the record
which is what I need.
Any thoughts?
Thanks!

Good idea, I'll try it, thanks!
I may have been making this way too hard.

"Rick B" wrote:

I would think you would need to build a report. You can sort and group. In
your sort you could create a group header and put in the collection
address, then in the details, put the other fields.

After viewing the report, I think there are ways to export it. I know you
can "Analyze it With Excel"

Hope that is closer to what you want.


Rick B


"cutthroatjess" wrote in message
news Sorry, I know I wasn't very clear.
I understand your suggestion, but that isn't what is required of me. For
example, here is the sample grouping from the XML schema:
CollectionAddress
CollectionAddr1XXXXXX/CollectionAddr1
CollectionAddr2XXXXXX/CollectionAddr2
CollectionCityXXXXXX/CollectionCity
CollectionStateXXXXXX/CollectionState
CollectionZipXXXXXX/CollectionZip
/CollectionAddress
Somehow, CollectionAddr1, CollectionAddr2, CollectionCity, CollectionState,
and CollectionZip must appear as separate elements under the element
CollectionAddress. When I export from Access to an XML file using your
suggestion, it doesn't group those 5 elements as CollectionAddress, but each
individually to look like:
CollectionAddr1XXXXXX/CollectionAddr1
CollectionAddr2XXXXXX/CollectionAddr2
CollectionCityXXXXXX/CollectionCity
CollectionStateXXXXXX/CollectionState
CollectionZipXXXXXX/CollectionZip

I've tried using joins and such, but it just won't populate the query right.


"Rick B" wrote:

Not sure I understand what you want.

You can either pull the three fields to a query as separate fields, or you
can merge them and put them in one field. Not sure what a "subfield" is.
That is not an Access term.

To combine them, use something like...

FullAddress: [City] & ", " & [State] & " " & [Zip]



Rick B


"cutthroatjess" wrote in message
...
I'm trying to group a number of fields from one table into one separate
field.
Basically, the table lists address information: state, zip, city, street.
I
would like to display these four fields as one field in a query with the
listed as subfields. Now, they need to be subfields, not just
concatination
of strings. The resulting query is then exported into an XML document and
sent on to others for import into another database. (I'm trying to comply
with their schema) I can get to a point where it will list the addresses,
but all of the addresses in the table, not the one specific to the record
which is what I need.
Any thoughts?
Thanks!

OK, so that didn't work.
Upon looking at things further I think I can rephrase my question.
What I'm trying to do is create a root element. I guess the root elements are used for "proper" xml formats. I'm unsure how to create the root element. Essentially it should look like this in xml:
CollectionAddress
CollectionAddr1XXX/CollectionAddr1
CollectionCityXXX/CollectionCity
CollectionStateXXX/CollectionState
CollectionZipXXX/CollectionZip
/CollectionAddress
where CollectionAddress is the root element.
Some of the knowledgebase hints at using a XLST transform file.
I'm really looking for the quick easy way....can I add the xml tags in the sql code?
Thanks!
"cutthroatjess" wrote:

Good idea, I'll try it, thanks!
I may have been making this way too hard.

"Rick B" wrote:

I would think you would need to build a report. You can sort and group. In
your sort you could create a group header and put in the collection
address, then in the details, put the other fields.

After viewing the report, I think there are ways to export it. I know you
can "Analyze it With Excel"

Hope that is closer to what you want.


Rick B


"cutthroatjess" wrote in message
news Sorry, I know I wasn't very clear.
I understand your suggestion, but that isn't what is required of me. For
example, here is the sample grouping from the XML schema:
CollectionAddress
CollectionAddr1XXXXXX/CollectionAddr1
CollectionAddr2XXXXXX/CollectionAddr2
CollectionCityXXXXXX/CollectionCity
CollectionStateXXXXXX/CollectionState
CollectionZipXXXXXX/CollectionZip
/CollectionAddress
Somehow, CollectionAddr1, CollectionAddr2, CollectionCity, CollectionState,
and CollectionZip must appear as separate elements under the element
CollectionAddress. When I export from Access to an XML file using your
suggestion, it doesn't group those 5 elements as CollectionAddress, but each
individually to look like:
CollectionAddr1XXXXXX/CollectionAddr1
CollectionAddr2XXXXXX/CollectionAddr2
CollectionCityXXXXXX/CollectionCity
CollectionStateXXXXXX/CollectionState
CollectionZipXXXXXX/CollectionZip

I've tried using joins and such, but it just won't populate the query right.


"Rick B" wrote:

Not sure I understand what you want.

You can either pull the three fields to a query as separate fields, or you
can merge them and put them in one field. Not sure what a "subfield" is.
That is not an Access term.

To combine them, use something like...

FullAddress: [City] & ", " & [State] & " " & [Zip]



Rick B


"cutthroatjess" wrote in message
...
I'm trying to group a number of fields from one table into one separate
field.
Basically, the table lists address information: state, zip, city, street.
I
would like to display these four fields as one field in a query with the
listed as subfields. Now, they need to be subfields, not just
concatination
of strings. The resulting query is then exported into an XML document and
sent on to others for import into another database. (I'm trying to comply
with their schema) I can get to a point where it will list the addresses,
but all of the addresses in the table, not the one specific to the record
which is what I need.
Any thoughts?
Thanks!

Pardon my intrusion...

It sounds a little like you want to have both the individual elements AND
the concatenated string. You can do this in a query, and export the query.

Or have I misunderstood...?

--
Good luck

Jeff Boyce
Access MVP