|
|
|
date: Fri, 4 Jul 2008 11:41:00 -0700,
group: microsoft.public.access.queries
back
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?
date: Fri, 4 Jul 2008 11:41:00 -0700
author: Oldsfan
Re: Duplicate entries in a query
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?
date: Fri, 04 Jul 2008 16:14:59 -0400
author: John Spencer
RE: Duplicate entries in a query
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?
date: Sun, 6 Jul 2008 05:10:03 -0700
author: Ken Sheridan
|
|
