combining multiple entries into one result

[Guest]

This may already be answered elsewhere, but I couldn't seem to come up with
the right search words to locate it.

I have a database for season ticket holders. I have one table
(TicketHolderData)that holds the personal data for the ticket holder (name,
address, etc.) which has primary key CustID. I have another table
(TicketHolderSeats) that has field CustID linked to the first, and then
contains the seats in fields Section, Row, and Seats. Some ticket holders
have multiple rows in this table depending on how many sets of seats they
hold. I am trying to create a query that will hold all of the personal data,
plus the concatenated data on each set of tickets. I currently have a query
showing me each row from TicketHolderSeats on a separate line with the
correct personal data on each line. Is there a way to make the query combine
them into one field on one line (i.e. "Section A, Row C, Seats 9-14; Section
D, Row Q, Seats 18-19")? Thanks for any assistance!

 

[John W. Vinson]

This may already be answered elsewhere, but I couldn't seem to come up with
the right search words to locate it.

I have a database for season ticket holders. I have one table
(TicketHolderData)that holds the personal data for the ticket holder (name,
address, etc.) which has primary key CustID. I have another table
(TicketHolderSeats) that has field CustID linked to the first, and then
contains the seats in fields Section, Row, and Seats. Some ticket holders
have multiple rows in this table depending on how many sets of seats they
hold. I am trying to create a query that will hold all of the personal data,
plus the concatenated data on each set of tickets. I currently have a query
showing me each row from TicketHolderSeats on a separate line with the
correct personal data on each line. Is there a way to make the query combine
them into one field on one line (i.e. "Section A, Row C, Seats 9-14; Section
D, Row Q, Seats 18-19")? Thanks for any assistance!

You'll need a Query using some VBA code, adapting the code at

http://www.mvps.org/access/modules/mdl0004.htm


John W. Vinson [MVP]

 

[Guest]

Thank you! I should have looked at Northwind before posting, but I'm never
sure if I'm catching all the underlying code when I don't have instructions.

 

[Guest]

Okay, it was the perfect answer, but I'm very much a beginner at using VB in
Access. I have pasted the SQL into a new query and adjusted for my tables as
per the directions and I thought I had the new module set up correctly (I
pasted in directly into a new module and saved it as "fConcatChild").
However, when I try to switch to the datasheet view in the query, it returns
error "Undefined function 'fConcatChild' in expression." I even tried it in
the Northwind database, thinking perhaps I had lost something in the
translation, but I get the same error. Am I missing a step? Thank you for
your patience!

 

[John Spencer]

DO NOT save the module with the same name as any function. Modules and
functions and subs share the same name space.
Rename the module to modConcatChild and try again. DO NOT rename the
function.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Good to know, thank you! That solved the problem in Northwind, but I now get
a syntax error ad infinitum in my own DB. The initial function declaration
is highlighted in yellow, and the following line is plainly highlighted:
"db As DAO.Database"

I've tried discussing DAO with Google, but I don't see anything that
explains where I should have perhaps had another file there. I've also
repasted the code from the original site, in case I'd perhaps deleted a
character by mistake.

So, I'm assuming I've done something wrong in my SQL. My one side table is
"TicketHolderData" with primary key "ID" (a long integer). My many side
table is "TicketHolderSeats" with field "Row" that I am using at the moment
for what needs to be concatenated. My SQL is as follows:

SELECT TicketHolderData.*,
fConcatChild("TicketHolderSeats","ID","Row","Long",[ID]) AS SubFormValues
FROM TicketHolderData;

Thank you!

 

[John Spencer]

Then you probably don't have the DAO library as a resource.

Type Control-G to get to the VBA window.
-- Select Tools: References
-- Find Microsoft DAO 3.6 Object Library (or later version if you see one)
and check it
-- Click ok
-- Select Debug: Compile ... from the menu and see if you get any errors.
If so, you will need to fix the errors.



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Good to know, thank you! That solved the problem in Northwind, but I now
get
a syntax error ad infinitum in my own DB. The initial function
declaration
is highlighted in yellow, and the following line is plainly highlighted:
"db As DAO.Database"

I've tried discussing DAO with Google, but I don't see anything that
explains where I should have perhaps had another file there. I've also
repasted the code from the original site, in case I'd perhaps deleted a
character by mistake.

So, I'm assuming I've done something wrong in my SQL. My one side table
is
"TicketHolderData" with primary key "ID" (a long integer). My many side
table is "TicketHolderSeats" with field "Row" that I am using at the
moment
for what needs to be concatenated. My SQL is as follows:

SELECT TicketHolderData.*,
fConcatChild("TicketHolderSeats","ID","Row","Long",[ID]) AS SubFormValues
FROM TicketHolderData;

Thank you!

DO NOT save the module with the same name as any function. Modules and
functions and subs share the same name space.
Rename the module to modConcatChild and try again. DO NOT rename the
function.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..