Duplicate entries in a query

Hi. I don't know if I can explain what I'm trying to do. Maybe I'm over my
head.

I've got a car club database. One table is name/address information. Another
table is year/make/model information. The third is the joining table, putting
members and cars together. Some members have no cars. Some members have more
than one.

I am trying to print a roster in Word, listing name/address/cars owned.

When I run my "directory" query, I get, for myself as an example:

Paul H, address, phone, 55 Olds
Paul H, address, phone, 62 Olds

When I pull this over to word, it looks like this:

Paul H
address
phone
55 Olds

Paul H
address
phone
62 Olds

Is there a way to get it to look like this?:

Paul H
address
phone
55 Olds
62 Olds

I tried running my "Directory" query with a "Vehicles Owned" subdatasheet.
My query results look like this:

Paul H, address, phone
+55 Olds
+62 Olds

But Word won't pick up the data from the subdatasheet.

I end up having to manually deleting all the duplicate name/address
information in Word. This is time consuming - some people have a dozen or
more cars!

Or should I be addressing this on the Word forums?

One solution would be to use Duane Hookom's concatenate function to
string the cars together in one field
62 Olds, 55 Olds, 1923 Ford Model-T

Google Access Groups for Hookom + Concatenate to get a URL reference to
Duane's sample database.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


Oldsfan wrote:
Hi. I don't know if I can explain what I'm trying to do. Maybe I'm over my
head.

I've got a car club database. One table is name/address information. Another
table is year/make/model information. The third is the joining table, putting
members and cars together. Some members have no cars. Some members have more
than one.

I am trying to print a roster in Word, listing name/address/cars owned.

When I run my "directory" query, I get, for myself as an example:

Paul H, address, phone, 55 Olds
Paul H, address, phone, 62 Olds

When I pull this over to word, it looks like this:

Paul H
address
phone
55 Olds

Paul H
address
phone
62 Olds

Is there a way to get it to look like this?:

Paul H
address
phone
55 Olds
62 Olds

I tried running my "Directory" query with a "Vehicles Owned" subdatasheet.
My query results look like this:

Paul H, address, phone
+55 Olds
+62 Olds

But Word won't pick up the data from the subdatasheet.

I end up having to manually deleting all the duplicate name/address
information in Word. This is time consuming - some people have a dozen or
more cars!

Or should I be addressing this on the Word forums?

Another possibility is to use an Access report, with a subreport in each
detail section bound to the car table, with the owner ID as the linking field
--
TedMi

An Access report rather than a Word document is the simplest method. You
don't need a subreport. Base the report on your query and group the report
by member. Put all the member data in a group header and the car data in the
detail section.

Tip: If you do use a report group the report first by the member's name and
then by the unique MemberID (or whatever) primary key column. Give the
MemberID group a group header, not the member name group, and put the
member's name, address etc in this group header. This will separate any
members who have the same name, but still order the report by name.

To do it via Word John's suggestion is the simplest way. However, you want
to list the cars one per line rather than concatenating them into a single
line, so you'd need to insert carriage returns/line feeds rather than
commas/spaces. A Function like this should do it; paste it into a standard
module and then change the table and field names to your own as necessary:

Public Function ListCars(lngMemberID As Long) As String

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strCarList As String

strSQL = "SELECT YearManufactured, Make, Model, Description " & _
"FROM Cars INNER JOIN Ownership " & _
"ON Cars.CarID = OwnerShip.CarID " & _
"WHERE MemberID = " & lngmemberID

Set rst = New ADODB.Recordset

With rst
.ActiveConnection = CurrentProject.Connection
.Open _
Source:=strSQL, _
CursorType:=adOpenForwardOnly

Do While Not .EOF
strCarList = strCarList & vbNewLine & _
.Fields("YearManufactured") & ", "_
.Fields("Make") & ", " & _
.Fields("Model") & ", " & _
.Fields("Description")
.MoveNext
Loop
.Close
' remove leading cr/lf
strCarList = Mid$(strCarList, 3)
End With

ListCars = strCarList

End Function

You can then call the function in a query such as this:

SELECT FirsName, Lastname, Address, Phone,
ListCars(MemberID) As CarsOwned
FROM Members
ORDER BY LastName, FirstName;

If you have any problems post back with the exact names of your tables and
their fields and data types.

Ken Sheridan
Stafford, England

"Oldsfan" wrote:

Hi. I don't know if I can explain what I'm trying to do. Maybe I'm over my
head.

I've got a car club database. One table is name/address information. Another
table is year/make/model information. The third is the joining table, putting
members and cars together. Some members have no cars. Some members have more
than one.

I am trying to print a roster in Word, listing name/address/cars owned.

When I run my "directory" query, I get, for myself as an example:

Paul H, address, phone, 55 Olds
Paul H, address, phone, 62 Olds

When I pull this over to word, it looks like this:

Paul H
address
phone
55 Olds

Paul H
address
phone
62 Olds

Is there a way to get it to look like this?:

Paul H
address
phone
55 Olds
62 Olds

I tried running my "Directory" query with a "Vehicles Owned" subdatasheet.
My query results look like this:

Paul H, address, phone
+55 Olds
+62 Olds

But Word won't pick up the data from the subdatasheet.

I end up having to manually deleting all the duplicate name/address
information in Word. This is time consuming - some people have a dozen or
more cars!

Or should I be addressing this on the Word forums?