Totals Query question

[HLCruz via AccessMonster.com]

SELECT tblDesignators.ProfileCode, tblContacts.ContactID, tblContacts.
MailingID, tblContacts.FirstName, tblContacts.LastName, [FirstName] & " " &
[LastName] AS Name, tblContacts.Email
FROM tblDesignators LEFT JOIN tblContacts ON tblDesignators.ContactID =
tblContacts.ContactID
WHERE (((tblDesignators.ProfileCode)="ShiningStar"));

I have a client management database that uses one table (tblMailingList) for
all household addresses and another table (tblContacts) for all the household
members and their personal information. They each have unique primary key
fields, MailingID and ContactID, respectively.

The above query selects Contact records that are members of a specific group.
In many cases there are mulitple family members that are members.

I'd like to use a Totals Query to group this query by household (MailingID),
but I'd also like to be able to see the all the names of the group members ...
is there a way, in SQL or VBA that I could string those multiple family
members together in another field? In my mind I'm thinking the query results
would be something like:

MailingID ShiningStars
12345 Joe & Nancy

Thank you,
Heather

 

[Duane Hookom]

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4&SID=36b57bc9b8b4c169ba1b47a7f5513z52.

 

[HLCruz via AccessMonster.com]

Thank you Duane - this is very helpful and I've been able to get exactly what
I wanted.

One more question: is there I way that I can use this query as a part of
other relational queries? My first try gave me a Debug error and highlighted
the following code:

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Thank you again

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4&SID=36b57bc9b8b4c169ba1b47a7f5513z52.

SELECT tblDesignators.ProfileCode, tblContacts.ContactID, tblContacts.
MailingID, tblContacts.FirstName, tblContacts.LastName, [FirstName] & " " &

[quoted text clipped - 22 lines]

Thank you,
Heather

 

[HLCruz via AccessMonster.com]

I'll clarify one more thing - I can build another query off this one as long
as it's an INNER JOIN, if I use a RIGHT or LEFT JOIN then I get the error.
Thanks

Thank you Duane - this is very helpful and I've been able to get exactly what
I wanted.

One more question: is there I way that I can use this query as a part of
other relational queries? My first try gave me a Debug error and highlighted
the following code:

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Thank you again

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4&SID=36b57bc9b8b4c169ba1b47a7f5513z52.

[quoted text clipped - 4 lines]

 

[Duane Hookom]

I expect your RIGHT or LEFT joins are creating Null values being sent to the
function. If you provide the SQL view, I think someone could help you fix the
problem.
--
Duane Hookom
Microsoft Access MVP

I'll clarify one more thing - I can build another query off this one as long
as it's an INNER JOIN, if I use a RIGHT or LEFT JOIN then I get the error.
Thanks

Thank you Duane - this is very helpful and I've been able to get exactly what
I wanted.

One more question: is there I way that I can use this query as a part of
other relational queries? My first try gave me a Debug error and highlighted
the following code:

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Thank you again

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4&SID=36b57bc9b8b4c169ba1b47a7f5513z52.

[quoted text clipped - 4 lines]

Thank you,
Heather

 

[HLCruz via AccessMonster.com]

SELECT sysqryRegistrationsByHousehold.CountYouth,
sysqryRegistrationsByHousehold.CountYouthWaive,
sysqryRegistrationsByHousehold.CountAdult, sysqryRegistrationsByHousehold.
CountAdultWaive, sysqryRegistrationsByHousehold.TotalCount,
sysqryRegistrationsByHousehold.AmountDue, sysqryRegistrationsByHousehold.
AmountPaid, ([AmountDue]-[AmountPaid]) AS BalanceDue,
sysqryRegistrationsByHousehold.PaymentMethod,
sysqryShiningStarHouseholdsWithChildNames.FirstNames
FROM sysqryShiningStarHouseholdsWithChildNames RIGHT JOIN
sysqryRegistrationsByHousehold ON sysqryShiningStarHouseholdsWithChildNames.
MailingID = sysqryRegistrationsByHousehold.MailID;

Here is the SQL of my query, the error I get is:

Run-time error
Syntax error (missing operator) in query expression 'MailingID =".

If I Debug it, it high lights this code:
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Thank you!!

I expect your RIGHT or LEFT joins are creating Null values being sent to the
function. If you provide the SQL view, I think someone could help you fix the
problem.

I'll clarify one more thing - I can build another query off this one as long
as it's an INNER JOIN, if I use a RIGHT or LEFT JOIN then I get the error.

[quoted text clipped - 17 lines]

 

[Duane Hookom]

Apparently MailingID might be null. You might need to add criteria or
NZ(MailingID,-999).

If you can't figure this out, reply with the full SQL view of the query
using the Concatenate function.

--
Duane Hookom
Microsoft Access MVP

SELECT sysqryRegistrationsByHousehold.CountYouth,
sysqryRegistrationsByHousehold.CountYouthWaive,
sysqryRegistrationsByHousehold.CountAdult, sysqryRegistrationsByHousehold.
CountAdultWaive, sysqryRegistrationsByHousehold.TotalCount,
sysqryRegistrationsByHousehold.AmountDue, sysqryRegistrationsByHousehold.
AmountPaid, ([AmountDue]-[AmountPaid]) AS BalanceDue,
sysqryRegistrationsByHousehold.PaymentMethod,
sysqryShiningStarHouseholdsWithChildNames.FirstNames
FROM sysqryShiningStarHouseholdsWithChildNames RIGHT JOIN
sysqryRegistrationsByHousehold ON sysqryShiningStarHouseholdsWithChildNames.
MailingID = sysqryRegistrationsByHousehold.MailID;

Here is the SQL of my query, the error I get is:

Run-time error
Syntax error (missing operator) in query expression 'MailingID =".

If I Debug it, it high lights this code:
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Thank you!!

I expect your RIGHT or LEFT joins are creating Null values being sent to the
function. If you provide the SQL view, I think someone could help you fix the
problem.

I'll clarify one more thing - I can build another query off this one as long
as it's an INNER JOIN, if I use a RIGHT or LEFT JOIN then I get the error.

[quoted text clipped - 17 lines]

Thank you,
Heather