Consolidate Multiple Records Query

[Anthony]

Good Afternoon,
I have been pulling my hair out with this trying to get it to work on Excel
and Access, but I just don't have enough knowledge of Access to know if it
can work. I have two fields in a table. In field one I have a list of
variables, call them City, County, State. In field 2 I have the potential
values for each variable. They are listed vertically, so Record 1 shows the
value City in Field 1 and Apache Junction in Field 2. Record 2 shows a blank
cell in Field 1 and Buckeye in Field 2. Record three shows a blank in Field
1 and Carefree in Field 2, etc.

I would like to find an non-manual way of consolidating the records so
Record 1 will show City in Field 1, and in Field 2 it will list Apache
Junction, Buckeye, Carefree. Record 2 will show County in Field 1, and in
Field 2 it will show Cochise, Maricopa, Navajo, Pima, Pinal.

Note that the number of records representing each variable in Field 1 is
different. City may have 20 records, County may have 8, and State may have
51.

I have approximately 500 such variables to do, thus the desire to avoid
manual manipulation. Thank you for any help you can provide!!

 

[John W. Vinson]

Good Afternoon,
I have been pulling my hair out with this trying to get it to work on Excel
and Access, but I just don't have enough knowledge of Access to know if it
can work. I have two fields in a table. In field one I have a list of
variables, call them City, County, State. In field 2 I have the potential
values for each variable. They are listed vertically, so Record 1 shows the
value City in Field 1 and Apache Junction in Field 2. Record 2 shows a blank
cell in Field 1 and Buckeye in Field 2. Record three shows a blank in Field
1 and Carefree in Field 2, etc.

I would like to find an non-manual way of consolidating the records so
Record 1 will show City in Field 1, and in Field 2 it will list Apache
Junction, Buckeye, Carefree. Record 2 will show County in Field 1, and in
Field 2 it will show Cochise, Maricopa, Navajo, Pima, Pinal.

Note that the number of records representing each variable in Field 1 is
different. City may have 20 records, County may have 8, and State may have
51.

I have approximately 500 such variables to do, thus the desire to avoid
manual manipulation. Thank you for any help you can provide!!

I'm sorry, but this isn't making much sense.

A field should have only one value. City, County and State should be three
different fields in your table. You can concatenate them in a query for
display purposes, and you can certainly create a Form with combo boxes or
listboxes showing multiple values on the screen.... but I'm not at all sure
what you're trying to do or in what context.