As Klatuu requested, example records from your Table, the SQL from your
Query, and a more detailed example of the output you'd like from the
Query (presumably different from what you got) would be helpful.
However, I have guessed at what you want, and here is my suggestion.
I added numbers in front of the animals' names, as otherwise I could not
see an easy way to sort them into an order where "Dog" is between "Cat"
and "Animals".
Suppose your Table looks like this:
[Animals] Table Datasheet View:
Animals_ID Key Key Key Key Key Key
word1 word2 word3 word4 word5 word6
----------- --------- ----- ----- ----- ----- -----
-1058446758 1_Cat 6_Pig
-337435181 2_Dog 1_Cat 5_Ram
547960856 3_Animals 4_Cow 6_Pig
As you may have guessed, this organization is very difficult to use,
since you have several fields in each record that contain approximately
the same kinds of information. For example, the two "1_Cat" values
cannot easily be compared, since they are in different fields.
To combine those similar "Keyword" fields into one, you might use a
Query similar to this one. I know, it's kind of hairy, but unless you
want to redesign your Table (which I think would be a very good thing to
do to it, unless it belongs to someone else and you don't have control
over it), you may be kind of stuck.
[Q_Animals] SQL:
SELECT Animals_ID, 1 as Field,
Keyword1 as Keyword FROM Animals
WHERE (((Animals.Keyword1) Is Not Null))
UNION
SELECT Animals_ID, 2, Keyword2 FROM Animals
WHERE (((Animals.Keyword2) Is Not Null))
UNION
SELECT Animals_ID, 3, Keyword3 FROM Animals
WHERE (((Animals.Keyword3) Is Not Null))
UNION
SELECT Animals_ID, 4, Keyword4 FROM Animals
WHERE (((Animals.Keyword4) Is Not Null))
UNION
SELECT Animals_ID, 5, Keyword5 FROM Animals
WHERE (((Animals.Keyword5) Is Not Null))
UNION
SELECT Animals_ID, 6, Keyword6 FROM Animals
WHERE (((Animals.Keyword6) Is Not Null))
ORDER BY Keyword, Animals_ID;
Running this Query gives the following results, which are closer to what
I think your Table should look like, with all the animal names in just
one [Keyword] field, and a [Field] field to tell you which column each
one came from. You may not need the [Field] column; I put it there in
case you care about it.
[Q_Animals] Query Datasheet View:
Animals_ID Field Keyword
----------- ------ ----------
-1058446758 1 1_Cat
-1058446758 6 6_Pig
-337435181 1 2_Dog
-337435181 2 1_Cat
-337435181 4 5_Ram
547960856 1 3_Animals
547960856 3 4_Cow
547960856 5 6_Pig
Based on this list, you can now get a list of the values, with no
duplications, via a Query like this one:
[Q_UniqueAnimals] SQL:
SELECT DISTINCT [Keyword] AS [Unique Keyword]
FROM Q_Animals
ORDER BY [Keyword];
[Q_UniqueAnimals] Query Datasheet View:
Unique Keyword
--------------
1_Cat
2_Dog
3_Animals
4_Cow
5_Ram
6_Pig
Actually, there are other ways to pull apart a Table like yours and
reassemble it into an easier-to-use form (such as what the [Q_Animals]
Query displays), such as copying the data one field at a time, or
re-entering everything from the keyboard, but I think a Union Query is
probably the easiest to use, once you've managed to construct it.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
Please post back with the SQL string from your query.
Open the query in SQL view, copy and paste it to this post.
Also, if you could post the layout for the tables(s) the query is based on,
field names, data types.
I will be happy to look at it.
:
It did not creat a The only field and lined up all the records from the
list. insted it lined them up all together by record by record,
I want the end result look like his
CAT
DOG
Animals.
The way it showing is
Cat (piked from keyword1)
Cat,(picked from keyword2)
Dog ( Only once, since all the records were inserted in keyword1)
Pl adivsie.
create one field in the query. Then give it an Alias. It would look like
this:
BigGlob: [field1] & [field2] & [field3] & [field4] & [field5] & field[6]
If you want a space after each just add:
BigGlob: [field1] & " " & [field2] & " " & [field3] & " " & [field4] & " " &
[field5] & " " & field[6]
Make it a Totals query, and all rows that are identical will collapse into
one.
If you have criteria for the six fields, but don't want them to show,
unclick the Show check box
:
In my query that picks 6 fileds ( keyword1, keyword2......
Keyword6,) Now
I
also, want to merge them into a singlefiled when I run the same query,
and
show them in on line coulum with no duplication.
thanks for your hlep.
Mike
