Need help aggregating strings

[Jay]

I have multiple records for the same person, some records have the email
field, others do not. If I'm aggregating up how do I make sure I capture the
email address. If I use SELECT FIRST(EMAIL) GROUP BY NAME and the email
address is the first record then I'm fine (same with LAST). What if the
records look like...

NAME, EMAIL
John Doe,
John Doe, (e-mail address removed)
John Doe,

....where the middle record has the email address, but the first and last
records do not.

Does the FIRST query command capture this? If not, how do I make sure I get
everyone's email address?

Thanks.

 

[scubadiver]

If you have MANY records for ONE person then maybe you should put the email
address in the primary table? Or is your database not normalised?

 

[Jay]

Not sure what normalized is (sorry...I'm kind of a rookie at this). So, no,
it's not normalized. What is "the primary table"? The data I have was given
to me in a .csv file and it is a series of records from contacts with
customers. The email address might or might not have been entered during the
contact, which determines whether or not the information is in the field or
not.

Thanks for your quick response.

 

[scubadiver]

To solve your problem, I was thinking along the lines of using either
"distinct records" or "distinct values". If you right click in the top half
of the query design grid and go to properties you will see these two settings
near the top. Set either one to "yes" and try both in turn. See what happens
when you view the records.

You may have split out the information into separate fields to get the
information you need. like using left() or mid(). There are some threads on
this.

If you intend to set this up permanently I suggest you learn about
normalisation and data integrity. But as a starter, I guess each person has
many records. So the person goes into one table and all the related records
go into another table. A 1-to-many relationship is established between the
two tables that will connect the records. I won't tell you more because I
taught myself and it is the best way to learn.

 

[Jay]

Thank you! Yes, I agree, I am usually a very good learner by working out
problems like this and I'm certain figuring this out with your guidance will
help me out in the long run. So thank you for taking that approach...your
post is extremely helpful. I will try out your suggestion, I'm confident it
will eventually be the answer (yes, there are many records for each person).

As this is an ongoing project I will definitely read up on normalizing and
data integrity...while there are certain short term goals I am tackling, I
need to keep the big picture in mind moving forward.

Thank you again for your help (and for responding so quickly!!!).

Jay

 

[John Spencer]

Believe it or not Max and Min work on text fields. So instead of First or
Last (which may return nulls) try using Max or Min (which will ignore null
values)

SELECT YourTable.Name, Max(Yourtable.Email) as Email
FROM YourTable
GROUP BY YourTable.Name


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

 

[Jay]

John,

I actually had tried that and didn't think it was working. Perhaps I should
look again.

Thank you for your help.

Jay