query help needed for combining fields for with one-to-many relationship

query help needed for combining fields for with one-to-many relationship

Post by pbr » Wed, 27 Sep 2000 04:00:00



Hi,

I have 3 tables like so:

tblItems
===============
itemId    itemName
------    ---------
1            knife
2            tent
3            can*

tblPeople
===============
peopleId    firstName    lastName
---------    ----------    ----------
1                John            Smith
2                David          Jones
3                Jane            Brown

tblPeopleItems
===============
itemId    peopleId
1            2
1            3
3            2
3            1
2            3

How can i create the following output?
itemName    personName
----------    -------------------------
knife            David Jones, Jane Brown
tent              Jane Brown
can*        David Jones, John Smith

I know how to merge fields like firstName + ' ' + lastName AS personName. My
problem is, I don't know how to then merge those results into a field
listing all the people associated with each item. Any help appreciated.

Thanks

Paul

 
 
 

query help needed for combining fields for with one-to-many relationship

Post by try.. » Thu, 28 Sep 2000 10:44:09


For server 7+ crosstabs and string manipulations check out RAC
(replacement for Access crosstab query).Includes transposing,splitting
strings and concatenating strings among other functions.

www.angelfire.com/ny4/rac

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

1. Need Help: search/replace data in one field/one record, by queries or script: PDOX5

I am trying to change all the data in one field and move it on the
left.

I am giving you a practical example.

I am classifying the files on one of my hard drive.
The file is called   GUYTEST.ZIP
What I am trying to do is to remove that dot between GUYTEST and
ZIp and to rewrite it like this GUYTEST ZIP.

Look simple but this is not easy.

I have looked at IMPORTING/EXPORTING and I have spent hours.


or indicate where I can find some on line documentation

Any help appreciated

Thanks  

2. Paradox tables over the network

3. Combining releated fields into one field...

4. Urgent !!! Btrieve 94 error code pls help

5. Combining 3 fields to be one searchable field

6. BRIO 5.5.2 Server Issues

7. Combining Several Fields into one field

8. Retrieving the actions from a local cube

9. Need help: One to many relationships

10. MDI help needed with one-many-relationship form

11. Need help on combining queries...

12. Help ! Need to combine order and return queries

13. Help: Creating a one-to-one Relationship