Let me explain a little better.....
I have three tables
tblSubscribers
Primary Key is MailinglistID
Otherfields Title
Surname
Forname
Address
City
Postcode etc etc
tblCatType
Primary Key CatId
Cattypes
CatCost
tblSubscriptions
Primary Key SubscriptionID
CatTypes
Foriegn Key MailinglistID
Each subscriber can subscribe to 1 or more catalogues. I have setup a form
with a multiselect list box which sends the selection to a query this all
works fine but I am unable to set it so that I do NOT get three labels for 1
person because they subscribed to 3 catalogues. I want to be able group the
3 together so that it produces 1 label. I have tried the distinct and Group
by clauses without success here is the code which I am using with me
multiselect list box....
Private Sub Command14_Click()
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("NewQuery16")
' Loop through the selected items in the list box and build a text string
For Each varItem In Me!List8.ItemsSelected
strCriteria = strCriteria & ",'" & Me!List8.ItemData(varItem) & "'"
Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
' Build the new SQL statement incorporating the string
strSQL = "SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename, "
& _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode, *" & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " & _
"WHERE tblsubscriptions.cattypes IN(" & strCriteria & ") " & _
"ORDER BY tblsubscribers.surname; "
Debug.Print strSQL
' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "NewQuery16", acViewPreview
' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub
Thank you for your help I've been working on this all day and its driving me
batty!
Simon
Rob Oldfield said:
I'm still not sure exactly what you're after... but try this...
Set up two tables...
Subscribers (SubscriberID - autonumber pk, Name - text)
Subscriptions (SubsciberID - number, long integer, Mail - text)
...and add some sample data.
Hopefully that's a cut down version of what you have.
Now add a new field onto subscribers - AllSubscriptions - text. I'm going
to use that to store all the 'Mail' data belonging to that subscriber.
The query...
UPDATE Subscribers INNER JOIN Subscriptions ON Subscribers.SubscriberID =
Subscriptions.SubscriberID SET Subscribers.AllSubscriptions =
[AllSubscriptions] & [Mail] & ",";
Any good?
Simonglencross said:
I have a tblsubscribers, tblCatTypes and tblSubscriptions.
Each subscriber can have many subscriptions.
What I am trying to achieve is rather than sending 6 seperate
envelopes
out
th the same subscriber is to group each subscribers subscriptions together
and send them out in the same envelope. Sounds really easy but I can
not
get
it to work at all. I have tried using Select Distinct but this does
not
give
me the correct result and I have also tried Group By .. Do you have any
suggestions?
Thank you
Simon
What is the link between the two MailingListID fields? A straight
one
to
many?
If the same tblsubscriptions.MailingListID links to two distinct
tblSubscribers.MailingListIDs then your question doesn't make sense, and
if
they link to the same tblSubscribers.MailingListID then a group query
should
do the job.
I have the following statment and I have tried most of the day to get
what
I
want and I just cant I'm about to start pulling my hair out. Below is
the
Statement....
strSQL = "SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
"
&
_
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode, *" & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " & _
"WHERE tblsubscriptions.cattypes IN(" & strCriteria & ") " & _
"ORDER BY tblsubscribers.surname; "
It works ok but I can not get the Final result I want which is if there
are
2 MailinglistID's the same I want in to group them together, I have
tried
group by but this does not work does anyone else have any suggestions?
Thank you
Simon
