Combining info from multiple records for mailing labels (from 1 ta

[Guest]

In Access 2003, I have ONE table with student information, including
'ParentName', 'Address', and other fields - one record per student. I'd like
to produce one mailing label for students with the same parent name and
address, but also include the student names on the label too (student names
use two fields: 'FirstName' and 'LastName'. Ideally, I'd like this grouped
so that one report has single-student households, and another has
multiple-student households (seeing they would require different size
labels). Is there an easy way for a novice to do this?!

The labels are set up to look like this:

ATTN: PARENT NAME
Student Name
Address
City, State Zip

 

[Guest]

There is a generic concatenate function that can combine fields from multiple
records at http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

You can create a query that groups by family and counts students. Use this
query to filter out (or in) families with more than one student.

 

[Guest]

Thank you so much for your help, Duane. From looking at your database
example, it appears that I should make a new table for Family ID and link
that to my Student table. If so, how could I set that up so that it
automates the records/entries to that table (assigning a numeric value to
each combination of 'ParentName' and 'Address' fields from my Student table)?
...Or would I need to do that manually? Also, I can't tell how you've
linked the two tables together other than stating so in the "Description"
section of the table design view. When I tried to duplicate the SQL from
your query example, it produced "Undefined function 'Concatenate' in
expression". How should I proceed?

 

[Guest]

You must include the module/function in your mdb. You can import the module
or copy and paste the code.

You don't necessarily need a "family ID". It depends on if you have unique
field values in your tables that can be used to identify a specific family.

 

[Guest]

Is there a way to populate the new FamilyID table with unique entries
obtained from the ParentName/Address combinations in my student table and
autonumber them, or do I need to manually assign each a number? I am using
both the ParentName field and the Address field to determine if the student
is from the same household.

Also, I was not familiar with modules at all. I imported your sample
module, but it looks like I have work to do so that it references my table
and field names. Also, is it in the module that the tables are linked,
seeing I don't see it elsewhere? Thank you again, for trying to help me. I
have much to learn!

 

[Guest]

What is your current significant table structure? Do you have a student table
and a parent table or is this all in a single table?

You should be able to import the module and then not touch the code. Use the
function in a query like my samples.

 

[Guest]

I have all data in 1 table with these fields (as well as others). The
FirstName and LastName fields are for the student names (one student per
record):
ParentName
Address
City
State
Zip
FirstName
LastName

Today I sorted the data by ParentName and Address (combined) and created a
new field called "FamID" in the same table. I manually assigned each
separate combination a unique number and repeated that number for students in
the same household. This was not a practical way to do that, but I didn't
know of another way. I have 333 students in the table, with 251 Family Id's
now. As I add new students it would be nice if the database could cross
check and number new households for me, or match and assign a new sibling the
same number as the other family members.

After importing the module, I was able to get a "practice database" to
concatenate, (perhaps because I had similar fields names as you did), but
another database I tried it on did not work. My student database is
maintained at work. I imported your module to that database, but so far it
is not working as desired. I will keep at it, hopefully with your guidance,
if you are willing. I am going on vacation though and will be out of touch
until next week. I am very appreciate of your help!

P.S. I am confused on how the tables in your example are linked, and also
why the module should work in someone else's database without using
identifying features relevant to their table/field names. Can you explain
this to me?

 

[Guest]

I would probably create a separate family/address table. However you can
create a query of families
-- qgrpFamilies --
SELECT ParentName & Address As FamID, ParentName, Address, City, State, Zip
FROM Table1Table
GROUP BY ParentName & Address, ParentName, Address, City, State, Zip
-- qselStudents --
SELECT ParentName & Address As FamID, FirstName, LastName
FROM Table1Table;

You now have two record sources to use in your concatenate function. The
query is based on qgrpFamilies and the Concatenate() funciton uses
SELECT qgrpFamilies.*
Concatenate("SELECT FirstName & ' ' & LastName FROM qselStudents WHERE FamID
=""" & FamID & """") As TheKids
FROM qgrpFamilies;