RE: Duplicate entries in a query
- From: Ken Sheridan <KenSheridan@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sun, 6 Jul 2008 05:10:03 -0700
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?
.
- References:
- Duplicate entries in a query
- From: Oldsfan
- Duplicate entries in a query
- Prev by Date: Re: Query Too Complex
- Next by Date: Re: Challenging Date Query help needed
- Previous by thread: RE: Duplicate entries in a query
- Next by thread: Re: Expression Builder
- Index(es):
Relevant Pages
|
