SQL Query, First and Last functions

G

Guest

I'm using a SQL query to household names and it looks like what I have below,
but I need a way to deal with groups of names that have 3 or more names. Is
there a "Second" or "First + 1" function? I don't know that much about SQL
so I need a little help.

This is what I have so far that deals with address groups of 1 or 2. I need
something for "Having Count = 3"

Any help would be appreciated.



SELECT First([First Name]) & " and " & Last([First Name]) AS [Both First
Names],
SD13_Delegates.[Last Name],
SD13_Delegates.[Mail Address],
SD13_Delegates.[Mail City],
SD13_Delegates.[Mail State],
SD13_Delegates.[Mail Zip],

FROM SD13_Delegates

GROUP BY SD13_Delegates.MatchField, SD13_Delegates.[Last Name],
SD13_Delegates.[Mail Address],
SD13_Delegates.[Mail City],
SD13_Delegates.[Mail State],
SD13_Delegates.[Mail Zip],

HAVING (((Count(*))=2));

UNION SELECT
First([First Name]) As [Both First Names],
SD13_Delegates.[Last Name],
SD13_Delegates.[Mail Address],
SD13_Delegates.[Mail City],
SD13_Delegates.[Mail State],
SD13_Delegates.[Mail Zip],

FROM SD13_Delegates

GROUP BY SD13_Delegates.Matchfield, SD13_Delegates.[Last Name],
SD13_Delegates.[Mail Address],
SD13_Delegates.[Mail City],
SD13_Delegates.[Mail State],
SD13_Delegates.[Mail Zip],
SD13_Delegates.[Support ID]

HAVING (((Count(*))=1));
 
T

Tom Ellison

Dear Needy:

It would help if you would tell us what you intend this to do.

It looks like you want a list of first names in one column and one row for
each LastName. Is that the case?

First() and Last() do not find the alphabetically first and last values.
Having used these functions, which are somewhat indeterminate in what row
they will find, makes it extremely difficult, if not impossible, to find the
rows NOT selected, as you would have to do to have a 3 name query:

HAVING COUNT(*) = 3

Use MIN() and MAX() to find the alphabetically first and last.

If I'm correct about your query for COUNT(*) = 2, you want to show the [Mail
Address], [Mail City], [Mail State], and [Mail Zip] for what? For the two
different people with the same last name but potentially different first
names? That's not likely to happen, is it? Are John Smith and Adam Smith
going to live at the same address?

There's so much to say about this, and I don't know what you intend. Please
explain and I'll see if there's anything I can contribute.

Tom Ellison
 
G

Guest

Thanks for replying Tom. I'll see if I can clear up some of the confusion.

I'm dealing with a list of names and addresses that have the information
below. The query uses the MatchField (a combination of their address and
name) to group the records by household. It's a union query that has one
query to deal with groups of 1 and another to deal with groups of 2.

First Name Last Name Address Zip MatchField
Jacob Leis 123 Street 80741 80741Leis123
Susan Leis 123 St. 80741 80741Leis123
Bill Williams 376 Road 80741 80741Williams376
Frank Volls 3456 Ave 80612 80612Volls345
Hank Volls 3456 Avenue 80612 80612Volls345
Cheri Volls 3456 Ave 80612 80612Volls345

Then my current query transforms the data into

Both First Names Last Name Address Zip Match Field
Jacob and Susan Leis 123 Street 80741 80741Leis123
Bill Williams 376 Road 80741
80741Williams376


But it doesn't know how to handle groups of more than two rows or "HAVING
(((Count(*))=3))", such as the "Volls" family. I'm not worried about them
being in alphabetical order but I do want them to group together in the "Both
First Names" field.

Does this clear up some of the confusion?

I want only one row per unique MatchField. My end goal is to take my large
address book of people and make sure that the same household doesn't get a
letter twice. That it only receives one for everyone at that address.


Tom Ellison said:
Dear Needy:

It would help if you would tell us what you intend this to do.

It looks like you want a list of first names in one column and one row for
each LastName. Is that the case?

First() and Last() do not find the alphabetically first and last values.
Having used these functions, which are somewhat indeterminate in what row
they will find, makes it extremely difficult, if not impossible, to find the
rows NOT selected, as you would have to do to have a 3 name query:

HAVING COUNT(*) = 3

Use MIN() and MAX() to find the alphabetically first and last.

If I'm correct about your query for COUNT(*) = 2, you want to show the [Mail
Address], [Mail City], [Mail State], and [Mail Zip] for what? For the two
different people with the same last name but potentially different first
names? That's not likely to happen, is it? Are John Smith and Adam Smith
going to live at the same address?

There's so much to say about this, and I don't know what you intend. Please
explain and I'll see if there's anything I can contribute.

Tom Ellison


In need of assistance said:
I'm using a SQL query to household names and it looks like what I have
below,
but I need a way to deal with groups of names that have 3 or more names.
Is
there a "Second" or "First + 1" function? I don't know that much about
SQL
so I need a little help.

This is what I have so far that deals with address groups of 1 or 2. I
need
something for "Having Count = 3"

Any help would be appreciated.



SELECT First([First Name]) & " and " & Last([First Name]) AS [Both First
Names],
SD13_Delegates.[Last Name],
SD13_Delegates.[Mail Address],
SD13_Delegates.[Mail City],
SD13_Delegates.[Mail State],
SD13_Delegates.[Mail Zip],

FROM SD13_Delegates

GROUP BY SD13_Delegates.MatchField, SD13_Delegates.[Last Name],
SD13_Delegates.[Mail Address],
SD13_Delegates.[Mail City],
SD13_Delegates.[Mail State],
SD13_Delegates.[Mail Zip],

HAVING (((Count(*))=2));

UNION SELECT
First([First Name]) As [Both First Names],
SD13_Delegates.[Last Name],
SD13_Delegates.[Mail Address],
SD13_Delegates.[Mail City],
SD13_Delegates.[Mail State],
SD13_Delegates.[Mail Zip],

FROM SD13_Delegates

GROUP BY SD13_Delegates.Matchfield, SD13_Delegates.[Last Name],
SD13_Delegates.[Mail Address],
SD13_Delegates.[Mail City],
SD13_Delegates.[Mail State],
SD13_Delegates.[Mail Zip],
SD13_Delegates.[Support ID]

HAVING (((Count(*))=1));
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear Needy:

I'm thinking your problem is to find and make the combination of first
names. I recommend basing this on subqueries that filter the 3 first names
using a ranking of 0, 1, and 2 alphabetically. If a first name occurs more
than once in the set, you have a problem From what you show, a DISTINCT
predicate could be used to solve this.

This approach could be extended to cases of more than 3 persons recorded at
the same address. At some point, the list of first names is going to
increase beyond what can be printed on an address label or wherever you need
this.

SELECT
(SELECT MIN(T1.[First Name])
FROM YourTableName T1
WHERE T1.MatchField = T.MatchField)
& ", " &
(SELECT [First Name]
FROM YourTableName T1
WHERE T1.MatchField = T.MatchField
AND (SELECT COUNT(*)
FROM YourTableName T2
WHERE T2.MatchField = T1.MatchFIeld
AND T2.[First Name] < T1.[First Name])
= 1)
& " and " &
(SELECT MAX(T1.[First Name])
FROM YourTableName T1
WHERE T1.MatchField = T.MatchField)
AS CombinedNames,
FIRST([Last Name]) AS LName,
FIRST(Address) AS Addr,
FIRST(Zip) AS Zp,
FIRST(MatchField) AS MF
FROM YourTableName T
WHERE MatchField IN (
SELECT MatchField
FROM YourTableName T1
GROUP BY MatchField
HAVING COUNT(*) = 3)
GROUP BY MatchField

I've tested this against the data in your sample. Does it work for you?

Tom Ellison


In need of assistance said:
Thanks for replying Tom. I'll see if I can clear up some of the
confusion.

I'm dealing with a list of names and addresses that have the information
below. The query uses the MatchField (a combination of their address and
name) to group the records by household. It's a union query that has one
query to deal with groups of 1 and another to deal with groups of 2.

First Name Last Name Address Zip MatchField
Jacob Leis 123 Street 80741 80741Leis123
Susan Leis 123 St. 80741 80741Leis123
Bill Williams 376 Road 80741
80741Williams376
Frank Volls 3456 Ave 80612 80612Volls345
Hank Volls 3456 Avenue 80612 80612Volls345
Cheri Volls 3456 Ave 80612 80612Volls345

Then my current query transforms the data into

Both First Names Last Name Address Zip Match Field
Jacob and Susan Leis 123 Street 80741 80741Leis123
Bill Williams 376 Road 80741
80741Williams376


But it doesn't know how to handle groups of more than two rows or "HAVING
(((Count(*))=3))", such as the "Volls" family. I'm not worried about them
being in alphabetical order but I do want them to group together in the
"Both
First Names" field.

Does this clear up some of the confusion?

I want only one row per unique MatchField. My end goal is to take my
large
address book of people and make sure that the same household doesn't get a
letter twice. That it only receives one for everyone at that address.


Tom Ellison said:
Dear Needy:

It would help if you would tell us what you intend this to do.

It looks like you want a list of first names in one column and one row
for
each LastName. Is that the case?

First() and Last() do not find the alphabetically first and last values.
Having used these functions, which are somewhat indeterminate in what row
they will find, makes it extremely difficult, if not impossible, to find
the
rows NOT selected, as you would have to do to have a 3 name query:

HAVING COUNT(*) = 3

Use MIN() and MAX() to find the alphabetically first and last.

If I'm correct about your query for COUNT(*) = 2, you want to show the
[Mail
Address], [Mail City], [Mail State], and [Mail Zip] for what? For the
two
different people with the same last name but potentially different first
names? That's not likely to happen, is it? Are John Smith and Adam
Smith
going to live at the same address?

There's so much to say about this, and I don't know what you intend.
Please
explain and I'll see if there's anything I can contribute.

Tom Ellison


"In need of assistance" <[email protected]>
wrote
in message news:D[email protected]...
I'm using a SQL query to household names and it looks like what I have
below,
but I need a way to deal with groups of names that have 3 or more
names.
Is
there a "Second" or "First + 1" function? I don't know that much about
SQL
so I need a little help.

This is what I have so far that deals with address groups of 1 or 2. I
need
something for "Having Count = 3"

Any help would be appreciated.



SELECT First([First Name]) & " and " & Last([First Name]) AS [Both
First
Names],
SD13_Delegates.[Last Name],
SD13_Delegates.[Mail Address],
SD13_Delegates.[Mail City],
SD13_Delegates.[Mail State],
SD13_Delegates.[Mail Zip],

FROM SD13_Delegates

GROUP BY SD13_Delegates.MatchField, SD13_Delegates.[Last Name],
SD13_Delegates.[Mail Address],
SD13_Delegates.[Mail City],
SD13_Delegates.[Mail State],
SD13_Delegates.[Mail Zip],

HAVING (((Count(*))=2));

UNION SELECT
First([First Name]) As [Both First Names],
SD13_Delegates.[Last Name],
SD13_Delegates.[Mail Address],
SD13_Delegates.[Mail City],
SD13_Delegates.[Mail State],
SD13_Delegates.[Mail Zip],

FROM SD13_Delegates

GROUP BY SD13_Delegates.Matchfield, SD13_Delegates.[Last Name],
SD13_Delegates.[Mail Address],
SD13_Delegates.[Mail City],
SD13_Delegates.[Mail State],
SD13_Delegates.[Mail Zip],
SD13_Delegates.[Support ID]

HAVING (((Count(*))=1));
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks Tom, this works great.

Tom Ellison said:
Dear Needy:

I'm thinking your problem is to find and make the combination of first
names. I recommend basing this on subqueries that filter the 3 first names
using a ranking of 0, 1, and 2 alphabetically. If a first name occurs more
than once in the set, you have a problem From what you show, a DISTINCT
predicate could be used to solve this.

This approach could be extended to cases of more than 3 persons recorded at
the same address. At some point, the list of first names is going to
increase beyond what can be printed on an address label or wherever you need
this.

SELECT
(SELECT MIN(T1.[First Name])
FROM YourTableName T1
WHERE T1.MatchField = T.MatchField)
& ", " &
(SELECT [First Name]
FROM YourTableName T1
WHERE T1.MatchField = T.MatchField
AND (SELECT COUNT(*)
FROM YourTableName T2
WHERE T2.MatchField = T1.MatchFIeld
AND T2.[First Name] < T1.[First Name])
= 1)
& " and " &
(SELECT MAX(T1.[First Name])
FROM YourTableName T1
WHERE T1.MatchField = T.MatchField)
AS CombinedNames,
FIRST([Last Name]) AS LName,
FIRST(Address) AS Addr,
FIRST(Zip) AS Zp,
FIRST(MatchField) AS MF
FROM YourTableName T
WHERE MatchField IN (
SELECT MatchField
FROM YourTableName T1
GROUP BY MatchField
HAVING COUNT(*) = 3)
GROUP BY MatchField

I've tested this against the data in your sample. Does it work for you?

Tom Ellison


In need of assistance said:
Thanks for replying Tom. I'll see if I can clear up some of the
confusion.

I'm dealing with a list of names and addresses that have the information
below. The query uses the MatchField (a combination of their address and
name) to group the records by household. It's a union query that has one
query to deal with groups of 1 and another to deal with groups of 2.

First Name Last Name Address Zip MatchField
Jacob Leis 123 Street 80741 80741Leis123
Susan Leis 123 St. 80741 80741Leis123
Bill Williams 376 Road 80741
80741Williams376
Frank Volls 3456 Ave 80612 80612Volls345
Hank Volls 3456 Avenue 80612 80612Volls345
Cheri Volls 3456 Ave 80612 80612Volls345

Then my current query transforms the data into

Both First Names Last Name Address Zip Match Field
Jacob and Susan Leis 123 Street 80741 80741Leis123
Bill Williams 376 Road 80741
80741Williams376


But it doesn't know how to handle groups of more than two rows or "HAVING
(((Count(*))=3))", such as the "Volls" family. I'm not worried about them
being in alphabetical order but I do want them to group together in the
"Both
First Names" field.

Does this clear up some of the confusion?

I want only one row per unique MatchField. My end goal is to take my
large
address book of people and make sure that the same household doesn't get a
letter twice. That it only receives one for everyone at that address.


Tom Ellison said:
Dear Needy:

It would help if you would tell us what you intend this to do.

It looks like you want a list of first names in one column and one row
for
each LastName. Is that the case?

First() and Last() do not find the alphabetically first and last values.
Having used these functions, which are somewhat indeterminate in what row
they will find, makes it extremely difficult, if not impossible, to find
the
rows NOT selected, as you would have to do to have a 3 name query:

HAVING COUNT(*) = 3

Use MIN() and MAX() to find the alphabetically first and last.

If I'm correct about your query for COUNT(*) = 2, you want to show the
[Mail
Address], [Mail City], [Mail State], and [Mail Zip] for what? For the
two
different people with the same last name but potentially different first
names? That's not likely to happen, is it? Are John Smith and Adam
Smith
going to live at the same address?

There's so much to say about this, and I don't know what you intend.
Please
explain and I'll see if there's anything I can contribute.

Tom Ellison


"In need of assistance" <[email protected]>
wrote
in message I'm using a SQL query to household names and it looks like what I have
below,
but I need a way to deal with groups of names that have 3 or more
names.
Is
there a "Second" or "First + 1" function? I don't know that much about
SQL
so I need a little help.

This is what I have so far that deals with address groups of 1 or 2. I
need
something for "Having Count = 3"

Any help would be appreciated.



SELECT First([First Name]) & " and " & Last([First Name]) AS [Both
First
Names],
SD13_Delegates.[Last Name],
SD13_Delegates.[Mail Address],
SD13_Delegates.[Mail City],
SD13_Delegates.[Mail State],
SD13_Delegates.[Mail Zip],

FROM SD13_Delegates

GROUP BY SD13_Delegates.MatchField, SD13_Delegates.[Last Name],
SD13_Delegates.[Mail Address],
SD13_Delegates.[Mail City],
SD13_Delegates.[Mail State],
SD13_Delegates.[Mail Zip],

HAVING (((Count(*))=2));

UNION SELECT
First([First Name]) As [Both First Names],
SD13_Delegates.[Last Name],
SD13_Delegates.[Mail Address],
SD13_Delegates.[Mail City],
SD13_Delegates.[Mail State],
SD13_Delegates.[Mail Zip],

FROM SD13_Delegates

GROUP BY SD13_Delegates.Matchfield, SD13_Delegates.[Last Name],
SD13_Delegates.[Mail Address],
SD13_Delegates.[Mail City],
SD13_Delegates.[Mail State],
SD13_Delegates.[Mail Zip],
SD13_Delegates.[Support ID]

HAVING (((Count(*))=1));
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can I do this? 4
Frustration with my query 4
Goup query 3
Update Query 6
Problems with COUNT in Query 2
Inner join with some null values 5
very interesting query 13
Query needed pls urgent 2

Top