Blank Spaces

[Guest]

Hello,

I'm trying to get my query from looking like this :

Dog.....Cat.....Cow.....Rat
___.....___.....___.....FE2
___.....___.....___.....JSS
___.....___.....ID2.....___
___.....J3D.....___.....___
___.....A3F.....___.....___
___.....JD9.....___.....___
___.....8DM.....___.....___
8DJ.....ID2.....___.....
9SM.....___.....___.....___

To looking like this :

Dog.....Cat.....Cow.....Rat
8DJ.....J3D.....ID2.....FE2
9SM.....A3F.....___.....JSS
___.....JD9.....___.....___
___.....8DM.....___.....___
___.....ID2.....___.....___

I tried to get the same result from a cross tab query, but all it did was
give me only one value instead of a list. So a regular query got me the
first example above. Is there a way to take out those spaces?

Thanks!

 

[Gary Walter]

I'm trying to get my query from looking like this :

Dog.....Cat.....Cow.....Rat
___.....___.....___.....FE2
___.....___.....___.....JSS
___.....___.....ID2.....___
___.....J3D.....___.....___
___.....A3F.....___.....___
___.....JD9.....___.....___
___.....8DM.....___.....___
8DJ.....ID2.....___.....
9SM.....___.....___.....___

To looking like this :

Dog.....Cat.....Cow.....Rat
8DJ.....J3D.....ID2.....FE2
9SM.....A3F.....___.....JSS
___.....JD9.....___.....___
___.....8DM.....___.....___
___.....ID2.....___.....___

I tried to get the same result from a cross tab query, but all it did was
give me only one value instead of a list. So a regular query got me the
first example above. Is there a way to take out those spaces?

Hi Senexis,

I might start with (structure of) original data
and either design and/or query to get something
like:

Animal AnimalNum Rank
Rat FE2 1
Rat JSS 2
Cow ID2 1
Cat J3D 1
Cat A3F 2
Cat JD9 3
Cat 8DM 4
Dog 8DJ 1
Cat ID2 5
Dog 9SM 2

then you could do a crosstab
pivot on "Animal IN ('Dog', 'Cat', 'Cow', 'Rat')"
group by "Rank"
transform on "any aggregate", i.e.,
Max([AnimalNum])
or Min([AnimalNum])
or First([AnimalNum])

good luck,

gary