Access SQL Question

[Newsgroup_User]

Say I have two fields, animal and part and each animal is listed three times
with three parts.
Animal Part
Bee Wings
Bee Eyes
Bee Legs
Cat Nose
Cat Fur
Cat Tail

I want an output like
Animal - Bee, Part 1=Wings, Part 2=Eyes, Part3=Legs
Animal - Cat, Part 1=Nose, Part2=Fur, Part3=Tail

Any suggestions?
Thanks
Jason

[Newsgroup_User]

To be honest, you are having trouble getting this right because your table
design is seriously flawed. If I were you, instead of trying to accomplish
this with what could turn out to be quite tricky SQL, I would re-design your
database as this will make things much easier for you going forward.

Read up on normal forms (particularly 1-3) and think how you can design
your database correctly using this method. Believe me, it will be woth your
while.

Pat.


"Jason Cook" <jason@gcstulsa.com> wrote in message
news:fa57r1$sgt$1@forums.macromedia.com...
> Say I have two fields, animal and part and each animal is listed three
> times with three parts.
> Animal Part
> Bee Wings
> Bee Eyes
> Bee Legs
> Cat Nose
> Cat Fur
> Cat Tail
>
> I want an output like
> Animal - Bee, Part 1=Wings, Part 2=Eyes, Part3=Legs
> Animal - Cat, Part 1=Nose, Part2=Fur, Part3=Tail
>
> Any suggestions?
> Thanks
> Jason
>

[Newsgroup_User]

I understand. What I'm actually showing you is the result of a query.
I actually have three tables...people, conference location, and a linking
table. Each person has a unique id in its table and each conference location
(thirty locations) has a unique id. Each person has three conference
location choices assigned in the linking table, so Johnny is recID 7 and my
three locations are locID 12, 18, and 24. My linking table (in this case)
would then have three records 7 with 12, 7 with 18, and 7 with 24. I need to
get a list of each person and their three locations.
Am I doing this the wrong way? Please, any help is appreciated.



"Pat Shaw" <pat@nomail.com> wrote in message
news:fa58eq$t53$1@forums.macromedia.com...
> To be honest, you are having trouble getting this right because your table
> design is seriously flawed. If I were you, instead of trying to accomplish
> this with what could turn out to be quite tricky SQL, I would re-design
> your database as this will make things much easier for you going forward.
>
> Read up on normal forms (particularly 1-3) and think how you can design
> your database correctly using this method. Believe me, it will be woth
> your while.
>
> Pat.
>
>
> "Jason Cook" <jason@gcstulsa.com> wrote in message
> news:fa57r1$sgt$1@forums.macromedia.com...
>> Say I have two fields, animal and part and each animal is listed three
>> times with three parts.
>> Animal Part
>> Bee Wings
>> Bee Eyes
>> Bee Legs
>> Cat Nose
>> Cat Fur
>> Cat Tail
>>
>> I want an output like
>> Animal - Bee, Part 1=Wings, Part 2=Eyes, Part3=Legs
>> Animal - Cat, Part 1=Nose, Part2=Fur, Part3=Tail
>>
>> Any suggestions?
>> Thanks
>> Jason
>>
>
>

[Newsgroup_User]

With Access, you'd be better of doing this in your presentation level. You
could save the person's name to a variable and write ", location" until the
name changes, then write "name - location" and keep going from there. The
necessary query to get that kind of output directly is not available in
Access to the best of my knowledge.


"Jason Cook" <jason@gcstulsa.com> wrote in message
news:fa5htt$98d$1@forums.macromedia.com...
>I understand. What I'm actually showing you is the result of a query.
> I actually have three tables...people, conference location, and a linking
> table. Each person has a unique id in its table and each conference
> location (thirty locations) has a unique id. Each person has three
> conference location choices assigned in the linking table, so Johnny is
> recID 7 and my three locations are locID 12, 18, and 24. My linking table
> (in this case) would then have three records 7 with 12, 7 with 18, and 7
> with 24. I need to get a list of each person and their three locations.
> Am I doing this the wrong way? Please, any help is appreciated.
>
>
>
> "Pat Shaw" <pat@nomail.com> wrote in message
> news:fa58eq$t53$1@forums.macromedia.com...
>> To be honest, you are having trouble getting this right because your
>> table design is seriously flawed. If I were you, instead of trying to
>> accomplish this with what could turn out to be quite tricky SQL, I would
>> re-design your database as this will make things much easier for you
>> going forward.
>>
>> Read up on normal forms (particularly 1-3) and think how you can design
>> your database correctly using this method. Believe me, it will be woth
>> your while.
>>
>> Pat.
>>
>>
>> "Jason Cook" <jason@gcstulsa.com> wrote in message
>> news:fa57r1$sgt$1@forums.macromedia.com...
>>> Say I have two fields, animal and part and each animal is listed three
>>> times with three parts.
>>> Animal Part
>>> Bee Wings
>>> Bee Eyes
>>> Bee Legs
>>> Cat Nose
>>> Cat Fur
>>> Cat Tail
>>>
>>> I want an output like
>>> Animal - Bee, Part 1=Wings, Part 2=Eyes, Part3=Legs
>>> Animal - Cat, Part 1=Nose, Part2=Fur, Part3=Tail
>>>
>>> Any suggestions?
>>> Thanks
>>> Jason
>>>
>>
>>
>
>