Frustration with my query

G

Guest

I've written this query a dozen times before but tonight it just isn't
working. Can someone trouble shoot with me?

I'm trying to get it to group records together based on the matchfield and
then combine the [First Name] field of the combinations into the [Both]
field. I've done it before, but tonight it just seems to ignore the matches.
It won't group a record.

Below is my SQL.

SELECT First([First Name]) & " and " & Last([First Name]) AS [Both],
[AbsenteesWithTier3-4RSD13_CAPPED].[Weld Co ID],
[AbsenteesWithTier3-4RSD13_CAPPED].[Last Name],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 1],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 2],
[AbsenteesWithTier3-4RSD13_CAPPED].City,
[AbsenteesWithTier3-4RSD13_CAPPED].State,
[AbsenteesWithTier3-4RSD13_CAPPED].Zip,
[AbsenteesWithTier3-4RSD13_CAPPED].Party,
[AbsenteesWithTier3-4RSD13_CAPPED].Precinct,
[AbsenteesWithTier3-4RSD13_CAPPED].Absentee,
[AbsenteesWithTier3-4RSD13_CAPPED].Tier,
[AbsenteesWithTier3-4RSD13_CAPPED].MatchField

FROM [AbsenteesWithTier3-4RSD13_CAPPED]

GROUP BY [AbsenteesWithTier3-4RSD13_CAPPED].[Matchfield],
[AbsenteesWithTier3-4RSD13_CAPPED].[Weld Co ID],
[AbsenteesWithTier3-4RSD13_CAPPED].[Last Name],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 1],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 2],
[AbsenteesWithTier3-4RSD13_CAPPED].City,
[AbsenteesWithTier3-4RSD13_CAPPED].State,
[AbsenteesWithTier3-4RSD13_CAPPED].Zip,
[AbsenteesWithTier3-4RSD13_CAPPED].Party,
[AbsenteesWithTier3-4RSD13_CAPPED].Precinct,
[AbsenteesWithTier3-4RSD13_CAPPED].Absentee,
[AbsenteesWithTier3-4RSD13_CAPPED].Tier,
[AbsenteesWithTier3-4RSD13_CAPPED].MatchField



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

UNION SELECT
First([First Name]) As Both,
[AbsenteesWithTier3-4RSD13_CAPPED].[Weld Co ID],
[AbsenteesWithTier3-4RSD13_CAPPED].[Last Name],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 1],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 2],
[AbsenteesWithTier3-4RSD13_CAPPED].City,
[AbsenteesWithTier3-4RSD13_CAPPED].State,
[AbsenteesWithTier3-4RSD13_CAPPED].Zip,
[AbsenteesWithTier3-4RSD13_CAPPED].Party,
[AbsenteesWithTier3-4RSD13_CAPPED].Precinct,
[AbsenteesWithTier3-4RSD13_CAPPED].Absentee,
[AbsenteesWithTier3-4RSD13_CAPPED].Tier,
[AbsenteesWithTier3-4RSD13_CAPPED].MatchField

FROM [AbsenteesWithTier3-4RSD13_CAPPED]



GROUP BY [AbsenteesWithTier3-4RSD13_CAPPED].[Matchfield],
[AbsenteesWithTier3-4RSD13_CAPPED].[Weld Co ID],
[AbsenteesWithTier3-4RSD13_CAPPED].[Last Name],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 1],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 2],
[AbsenteesWithTier3-4RSD13_CAPPED].City,
[AbsenteesWithTier3-4RSD13_CAPPED].State,
[AbsenteesWithTier3-4RSD13_CAPPED].Zip,
[AbsenteesWithTier3-4RSD13_CAPPED].Party,
[AbsenteesWithTier3-4RSD13_CAPPED].Precinct,
[AbsenteesWithTier3-4RSD13_CAPPED].Absentee,
[AbsenteesWithTier3-4RSD13_CAPPED].Tier,
[AbsenteesWithTier3-4RSD13_CAPPED].MatchField


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

Duane Hookom

I would use Min() rather than First() and Max() rather than Last().

--
Duane Hookom
MS Access MVP

In need of assistance said:
I've written this query a dozen times before but tonight it just isn't
working. Can someone trouble shoot with me?

I'm trying to get it to group records together based on the matchfield and
then combine the [First Name] field of the combinations into the [Both]
field. I've done it before, but tonight it just seems to ignore the
matches.
It won't group a record.

Below is my SQL.

SELECT First([First Name]) & " and " & Last([First Name]) AS [Both],
[AbsenteesWithTier3-4RSD13_CAPPED].[Weld Co ID],
[AbsenteesWithTier3-4RSD13_CAPPED].[Last Name],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 1],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 2],
[AbsenteesWithTier3-4RSD13_CAPPED].City,
[AbsenteesWithTier3-4RSD13_CAPPED].State,
[AbsenteesWithTier3-4RSD13_CAPPED].Zip,
[AbsenteesWithTier3-4RSD13_CAPPED].Party,
[AbsenteesWithTier3-4RSD13_CAPPED].Precinct,
[AbsenteesWithTier3-4RSD13_CAPPED].Absentee,
[AbsenteesWithTier3-4RSD13_CAPPED].Tier,
[AbsenteesWithTier3-4RSD13_CAPPED].MatchField

FROM [AbsenteesWithTier3-4RSD13_CAPPED]

GROUP BY [AbsenteesWithTier3-4RSD13_CAPPED].[Matchfield],
[AbsenteesWithTier3-4RSD13_CAPPED].[Weld Co ID],
[AbsenteesWithTier3-4RSD13_CAPPED].[Last Name],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 1],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 2],
[AbsenteesWithTier3-4RSD13_CAPPED].City,
[AbsenteesWithTier3-4RSD13_CAPPED].State,
[AbsenteesWithTier3-4RSD13_CAPPED].Zip,
[AbsenteesWithTier3-4RSD13_CAPPED].Party,
[AbsenteesWithTier3-4RSD13_CAPPED].Precinct,
[AbsenteesWithTier3-4RSD13_CAPPED].Absentee,
[AbsenteesWithTier3-4RSD13_CAPPED].Tier,
[AbsenteesWithTier3-4RSD13_CAPPED].MatchField



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

UNION SELECT
First([First Name]) As Both,
[AbsenteesWithTier3-4RSD13_CAPPED].[Weld Co ID],
[AbsenteesWithTier3-4RSD13_CAPPED].[Last Name],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 1],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 2],
[AbsenteesWithTier3-4RSD13_CAPPED].City,
[AbsenteesWithTier3-4RSD13_CAPPED].State,
[AbsenteesWithTier3-4RSD13_CAPPED].Zip,
[AbsenteesWithTier3-4RSD13_CAPPED].Party,
[AbsenteesWithTier3-4RSD13_CAPPED].Precinct,
[AbsenteesWithTier3-4RSD13_CAPPED].Absentee,
[AbsenteesWithTier3-4RSD13_CAPPED].Tier,
[AbsenteesWithTier3-4RSD13_CAPPED].MatchField

FROM [AbsenteesWithTier3-4RSD13_CAPPED]



GROUP BY [AbsenteesWithTier3-4RSD13_CAPPED].[Matchfield],
[AbsenteesWithTier3-4RSD13_CAPPED].[Weld Co ID],
[AbsenteesWithTier3-4RSD13_CAPPED].[Last Name],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 1],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 2],
[AbsenteesWithTier3-4RSD13_CAPPED].City,
[AbsenteesWithTier3-4RSD13_CAPPED].State,
[AbsenteesWithTier3-4RSD13_CAPPED].Zip,
[AbsenteesWithTier3-4RSD13_CAPPED].Party,
[AbsenteesWithTier3-4RSD13_CAPPED].Precinct,
[AbsenteesWithTier3-4RSD13_CAPPED].Absentee,
[AbsenteesWithTier3-4RSD13_CAPPED].Tier,
[AbsenteesWithTier3-4RSD13_CAPPED].MatchField


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

Guest

Thanks, I appreciate the response and I'll give that a shot, but I've had
good luck with First() and Last() in the past. I feel like I'm just missing
a comma or I'm asking for something it doesn't want to do for some reason.

Can you think of any other reasons this wouldn't want to work?


Duane Hookom said:
I would use Min() rather than First() and Max() rather than Last().

--
Duane Hookom
MS Access MVP

In need of assistance said:
I've written this query a dozen times before but tonight it just isn't
working. Can someone trouble shoot with me?

I'm trying to get it to group records together based on the matchfield and
then combine the [First Name] field of the combinations into the [Both]
field. I've done it before, but tonight it just seems to ignore the
matches.
It won't group a record.

Below is my SQL.

SELECT First([First Name]) & " and " & Last([First Name]) AS [Both],
[AbsenteesWithTier3-4RSD13_CAPPED].[Weld Co ID],
[AbsenteesWithTier3-4RSD13_CAPPED].[Last Name],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 1],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 2],
[AbsenteesWithTier3-4RSD13_CAPPED].City,
[AbsenteesWithTier3-4RSD13_CAPPED].State,
[AbsenteesWithTier3-4RSD13_CAPPED].Zip,
[AbsenteesWithTier3-4RSD13_CAPPED].Party,
[AbsenteesWithTier3-4RSD13_CAPPED].Precinct,
[AbsenteesWithTier3-4RSD13_CAPPED].Absentee,
[AbsenteesWithTier3-4RSD13_CAPPED].Tier,
[AbsenteesWithTier3-4RSD13_CAPPED].MatchField

FROM [AbsenteesWithTier3-4RSD13_CAPPED]

GROUP BY [AbsenteesWithTier3-4RSD13_CAPPED].[Matchfield],
[AbsenteesWithTier3-4RSD13_CAPPED].[Weld Co ID],
[AbsenteesWithTier3-4RSD13_CAPPED].[Last Name],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 1],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 2],
[AbsenteesWithTier3-4RSD13_CAPPED].City,
[AbsenteesWithTier3-4RSD13_CAPPED].State,
[AbsenteesWithTier3-4RSD13_CAPPED].Zip,
[AbsenteesWithTier3-4RSD13_CAPPED].Party,
[AbsenteesWithTier3-4RSD13_CAPPED].Precinct,
[AbsenteesWithTier3-4RSD13_CAPPED].Absentee,
[AbsenteesWithTier3-4RSD13_CAPPED].Tier,
[AbsenteesWithTier3-4RSD13_CAPPED].MatchField



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

UNION SELECT
First([First Name]) As Both,
[AbsenteesWithTier3-4RSD13_CAPPED].[Weld Co ID],
[AbsenteesWithTier3-4RSD13_CAPPED].[Last Name],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 1],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 2],
[AbsenteesWithTier3-4RSD13_CAPPED].City,
[AbsenteesWithTier3-4RSD13_CAPPED].State,
[AbsenteesWithTier3-4RSD13_CAPPED].Zip,
[AbsenteesWithTier3-4RSD13_CAPPED].Party,
[AbsenteesWithTier3-4RSD13_CAPPED].Precinct,
[AbsenteesWithTier3-4RSD13_CAPPED].Absentee,
[AbsenteesWithTier3-4RSD13_CAPPED].Tier,
[AbsenteesWithTier3-4RSD13_CAPPED].MatchField

FROM [AbsenteesWithTier3-4RSD13_CAPPED]



GROUP BY [AbsenteesWithTier3-4RSD13_CAPPED].[Matchfield],
[AbsenteesWithTier3-4RSD13_CAPPED].[Weld Co ID],
[AbsenteesWithTier3-4RSD13_CAPPED].[Last Name],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 1],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 2],
[AbsenteesWithTier3-4RSD13_CAPPED].City,
[AbsenteesWithTier3-4RSD13_CAPPED].State,
[AbsenteesWithTier3-4RSD13_CAPPED].Zip,
[AbsenteesWithTier3-4RSD13_CAPPED].Party,
[AbsenteesWithTier3-4RSD13_CAPPED].Precinct,
[AbsenteesWithTier3-4RSD13_CAPPED].Absentee,
[AbsenteesWithTier3-4RSD13_CAPPED].Tier,
[AbsenteesWithTier3-4RSD13_CAPPED].MatchField


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

Marshall Barton

In said:
I've written this query a dozen times before but tonight it just isn't
working. Can someone trouble shoot with me?

I'm trying to get it to group records together based on the matchfield and
then combine the [First Name] field of the combinations into the [Both]
field. I've done it before, but tonight it just seems to ignore the matches.
It won't group a record.

Below is my SQL.

SELECT First([First Name]) & " and " & Last([First Name]) AS [Both],
[AbsenteesWithTier3-4RSD13_CAPPED].[Weld Co ID],
[AbsenteesWithTier3-4RSD13_CAPPED].[Last Name],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 1],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 2],
[AbsenteesWithTier3-4RSD13_CAPPED].City,
[AbsenteesWithTier3-4RSD13_CAPPED].State,
[AbsenteesWithTier3-4RSD13_CAPPED].Zip,
[AbsenteesWithTier3-4RSD13_CAPPED].Party,
[AbsenteesWithTier3-4RSD13_CAPPED].Precinct,
[AbsenteesWithTier3-4RSD13_CAPPED].Absentee,
[AbsenteesWithTier3-4RSD13_CAPPED].Tier,
[AbsenteesWithTier3-4RSD13_CAPPED].MatchField

FROM [AbsenteesWithTier3-4RSD13_CAPPED]

GROUP BY [AbsenteesWithTier3-4RSD13_CAPPED].[Matchfield],
[AbsenteesWithTier3-4RSD13_CAPPED].[Weld Co ID],
[AbsenteesWithTier3-4RSD13_CAPPED].[Last Name],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 1],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 2],
[AbsenteesWithTier3-4RSD13_CAPPED].City,
[AbsenteesWithTier3-4RSD13_CAPPED].State,
[AbsenteesWithTier3-4RSD13_CAPPED].Zip,
[AbsenteesWithTier3-4RSD13_CAPPED].Party,
[AbsenteesWithTier3-4RSD13_CAPPED].Precinct,
[AbsenteesWithTier3-4RSD13_CAPPED].Absentee,
[AbsenteesWithTier3-4RSD13_CAPPED].Tier,
[AbsenteesWithTier3-4RSD13_CAPPED].MatchField



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

UNION SELECT
First([First Name]) As Both,
[AbsenteesWithTier3-4RSD13_CAPPED].[Weld Co ID],
[AbsenteesWithTier3-4RSD13_CAPPED].[Last Name],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 1],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 2],
[AbsenteesWithTier3-4RSD13_CAPPED].City,
[AbsenteesWithTier3-4RSD13_CAPPED].State,
[AbsenteesWithTier3-4RSD13_CAPPED].Zip,
[AbsenteesWithTier3-4RSD13_CAPPED].Party,
[AbsenteesWithTier3-4RSD13_CAPPED].Precinct,
[AbsenteesWithTier3-4RSD13_CAPPED].Absentee,
[AbsenteesWithTier3-4RSD13_CAPPED].Tier,
[AbsenteesWithTier3-4RSD13_CAPPED].MatchField

FROM [AbsenteesWithTier3-4RSD13_CAPPED]



GROUP BY [AbsenteesWithTier3-4RSD13_CAPPED].[Matchfield],
[AbsenteesWithTier3-4RSD13_CAPPED].[Weld Co ID],
[AbsenteesWithTier3-4RSD13_CAPPED].[Last Name],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 1],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 2],
[AbsenteesWithTier3-4RSD13_CAPPED].City,
[AbsenteesWithTier3-4RSD13_CAPPED].State,
[AbsenteesWithTier3-4RSD13_CAPPED].Zip,
[AbsenteesWithTier3-4RSD13_CAPPED].Party,
[AbsenteesWithTier3-4RSD13_CAPPED].Precinct,
[AbsenteesWithTier3-4RSD13_CAPPED].Absentee,
[AbsenteesWithTier3-4RSD13_CAPPED].Tier,
[AbsenteesWithTier3-4RSD13_CAPPED].MatchField


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


I'm pretty sure the problem is that you are grouping on way
too many fields. Try eliminating all but MatchField.

If the query produces valid results that waym then you need
to think about what the other fields mean in this situation.
 
G

Guest

I fixed it about 5 mins before you posted. :) And yeah you were spot on
with that diagnosis. I discovered (I don't know why I didn't think about it
to begin with), that I can't have fields that are unique in the grouped
records or they won't group. Duh! Namely the Weld Co ID field.

I swear, it's always the obvious ones that trip you up. At anyrate, I
trimmed down the query to just the bare essentials and then linked it another
query to give me the rest of the information.

Works great now. Thanks for the help.



Marshall Barton said:
In said:
I've written this query a dozen times before but tonight it just isn't
working. Can someone trouble shoot with me?

I'm trying to get it to group records together based on the matchfield and
then combine the [First Name] field of the combinations into the [Both]
field. I've done it before, but tonight it just seems to ignore the matches.
It won't group a record.

Below is my SQL.

SELECT First([First Name]) & " and " & Last([First Name]) AS [Both],
[AbsenteesWithTier3-4RSD13_CAPPED].[Weld Co ID],
[AbsenteesWithTier3-4RSD13_CAPPED].[Last Name],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 1],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 2],
[AbsenteesWithTier3-4RSD13_CAPPED].City,
[AbsenteesWithTier3-4RSD13_CAPPED].State,
[AbsenteesWithTier3-4RSD13_CAPPED].Zip,
[AbsenteesWithTier3-4RSD13_CAPPED].Party,
[AbsenteesWithTier3-4RSD13_CAPPED].Precinct,
[AbsenteesWithTier3-4RSD13_CAPPED].Absentee,
[AbsenteesWithTier3-4RSD13_CAPPED].Tier,
[AbsenteesWithTier3-4RSD13_CAPPED].MatchField

FROM [AbsenteesWithTier3-4RSD13_CAPPED]

GROUP BY [AbsenteesWithTier3-4RSD13_CAPPED].[Matchfield],
[AbsenteesWithTier3-4RSD13_CAPPED].[Weld Co ID],
[AbsenteesWithTier3-4RSD13_CAPPED].[Last Name],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 1],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 2],
[AbsenteesWithTier3-4RSD13_CAPPED].City,
[AbsenteesWithTier3-4RSD13_CAPPED].State,
[AbsenteesWithTier3-4RSD13_CAPPED].Zip,
[AbsenteesWithTier3-4RSD13_CAPPED].Party,
[AbsenteesWithTier3-4RSD13_CAPPED].Precinct,
[AbsenteesWithTier3-4RSD13_CAPPED].Absentee,
[AbsenteesWithTier3-4RSD13_CAPPED].Tier,
[AbsenteesWithTier3-4RSD13_CAPPED].MatchField



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

UNION SELECT
First([First Name]) As Both,
[AbsenteesWithTier3-4RSD13_CAPPED].[Weld Co ID],
[AbsenteesWithTier3-4RSD13_CAPPED].[Last Name],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 1],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 2],
[AbsenteesWithTier3-4RSD13_CAPPED].City,
[AbsenteesWithTier3-4RSD13_CAPPED].State,
[AbsenteesWithTier3-4RSD13_CAPPED].Zip,
[AbsenteesWithTier3-4RSD13_CAPPED].Party,
[AbsenteesWithTier3-4RSD13_CAPPED].Precinct,
[AbsenteesWithTier3-4RSD13_CAPPED].Absentee,
[AbsenteesWithTier3-4RSD13_CAPPED].Tier,
[AbsenteesWithTier3-4RSD13_CAPPED].MatchField

FROM [AbsenteesWithTier3-4RSD13_CAPPED]



GROUP BY [AbsenteesWithTier3-4RSD13_CAPPED].[Matchfield],
[AbsenteesWithTier3-4RSD13_CAPPED].[Weld Co ID],
[AbsenteesWithTier3-4RSD13_CAPPED].[Last Name],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 1],
[AbsenteesWithTier3-4RSD13_CAPPED].[Address 2],
[AbsenteesWithTier3-4RSD13_CAPPED].City,
[AbsenteesWithTier3-4RSD13_CAPPED].State,
[AbsenteesWithTier3-4RSD13_CAPPED].Zip,
[AbsenteesWithTier3-4RSD13_CAPPED].Party,
[AbsenteesWithTier3-4RSD13_CAPPED].Precinct,
[AbsenteesWithTier3-4RSD13_CAPPED].Absentee,
[AbsenteesWithTier3-4RSD13_CAPPED].Tier,
[AbsenteesWithTier3-4RSD13_CAPPED].MatchField


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


I'm pretty sure the problem is that you are grouping on way
too many fields. Try eliminating all but MatchField.

If the query produces valid results that waym then you need
to think about what the other fields mean in this situation.
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

SQL Syntax Bug 2
SQL Query, First and Last functions 4
Problems with COUNT in Query 2
Please help with a query! 4
Help with dups in query 1
query for complex report 1
very interesting query 13
Query needed pls urgent 2

Top