Access 2003 query question

Dave K. · Mar 11, 2009

I have a query that produces the results:

PorS AgencyName MailAddress
P Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Affiliated-Stone Agency PO Box 909
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent 161 Mountain Rd
P Anderson-Krause, Inc. 238 E Main St
S Anderson-Krause, Inc. 238 E Main St
P Anderson-Meyer Ins., Inc. PO Box 977
S Anderson-Meyer Ins., Inc. PO Box 977
S Archambault Ins. Assoc. PO Box 153

As you can see I have Multiple [AgencyName] with the same address. The P =
Primary and S = Secondary for [PorS]. I need a query to only show the
Primary and only the secondary with a different address. The only exception
to this would be if the Primary and Secondary [Agencyname] are different.

KARL DEWEY · Mar 11, 2009

Try these two queries --
DaveK_1 ---
SELECT DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK
GROUP BY DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
HAVING (((DaveK.PorS)="P"));

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyName, DaveK.MailAddress;

Dave K. · Mar 12, 2009

Karl, This seems top work fine except every time I add additional fields to
the query the number of records gets bigger. I need to add the following:

AgencyCode
SortName
MailCity
MailState
MailZip

Thanks Dave

--
Dave K.

KARL DEWEY said:
Try these two queries --
DaveK_1 ---
SELECT DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK
GROUP BY DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
HAVING (((DaveK.PorS)="P"));

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyName, DaveK.MailAddress;

--
KARL DEWEY
Build a little - Test a little

Dave K. said:

I have a query that produces the results:

PorS AgencyName MailAddress
P Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Affiliated-Stone Agency PO Box 909
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent 161 Mountain Rd
P Anderson-Krause, Inc. 238 E Main St
S Anderson-Krause, Inc. 238 E Main St
P Anderson-Meyer Ins., Inc. PO Box 977
S Anderson-Meyer Ins., Inc. PO Box 977
S Archambault Ins. Assoc. PO Box 153

As you can see I have Multiple [AgencyName] with the same address. The P =
Primary and S = Secondary for [PorS]. I need a query to only show the
Primary and only the secondary with a different address. The only exception
to this would be if the Primary and Secondary [Agencyname] are different.

Click to expand...

Dave K. · Mar 12, 2009

Karl, I have this query set up, but every time I add [AgencyCode] to the mix
it shows all the records instead of the specific ones that were requested.

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
ORDER BY DaveK.SortName;

--
Dave K.

KARL DEWEY said:
Try these two queries --
DaveK_1 ---
SELECT DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK
GROUP BY DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
HAVING (((DaveK.PorS)="P"));

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyName, DaveK.MailAddress;

--
KARL DEWEY
Build a little - Test a little

Dave K. said:

I have a query that produces the results:

PorS AgencyName MailAddress
P Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Affiliated-Stone Agency PO Box 909
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent 161 Mountain Rd
P Anderson-Krause, Inc. 238 E Main St
S Anderson-Krause, Inc. 238 E Main St
P Anderson-Meyer Ins., Inc. PO Box 977
S Anderson-Meyer Ins., Inc. PO Box 977
S Archambault Ins. Assoc. PO Box 153

As you can see I have Multiple [AgencyName] with the same address. The P =
Primary and S = Secondary for [PorS]. I need a query to only show the
Primary and only the secondary with a different address. The only exception
to this would be if the Primary and Secondary [Agencyname] are different.

Click to expand...

KARL DEWEY · Mar 12, 2009

<<every time I add [AgencyCode]
I do not see [AgencyCode] in your query.

--
KARL DEWEY
Build a little - Test a little

Dave K. said:
Karl, I have this query set up, but every time I add [AgencyCode] to the mix
it shows all the records instead of the specific ones that were requested.

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
ORDER BY DaveK.SortName;

--
Dave K.

KARL DEWEY said:

Try these two queries --
DaveK_1 ---
SELECT DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK
GROUP BY DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
HAVING (((DaveK.PorS)="P"));

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyName, DaveK.MailAddress;

--
KARL DEWEY
Build a little - Test a little

Dave K. said:

I have a query that produces the results:

PorS AgencyName MailAddress
P Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Affiliated-Stone Agency PO Box 909
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent 161 Mountain Rd
P Anderson-Krause, Inc. 238 E Main St
S Anderson-Krause, Inc. 238 E Main St
P Anderson-Meyer Ins., Inc. PO Box 977
S Anderson-Meyer Ins., Inc. PO Box 977
S Archambault Ins. Assoc. PO Box 153

As you can see I have Multiple [AgencyName] with the same address. The P =
Primary and S = Secondary for [PorS]. I need a query to only show the
Primary and only the secondary with a different address. The only exception
to this would be if the Primary and Secondary [Agencyname] are different.

Click to expand...

Click to expand...

Dave K. · Mar 12, 2009

It is not in there because it does not work. I have no idea where to place
it. [agencyCode] appears in queries DaveK and DaveK_1.
--
Dave K.

KARL DEWEY said:
<<every time I add [AgencyCode]
I do not see [AgencyCode] in your query.

--
KARL DEWEY
Build a little - Test a little

Dave K. said:

Karl, I have this query set up, but every time I add [AgencyCode] to the mix
it shows all the records instead of the specific ones that were requested.

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
ORDER BY DaveK.SortName;

--
Dave K.

KARL DEWEY said:

Try these two queries --
DaveK_1 ---
SELECT DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK
GROUP BY DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
HAVING (((DaveK.PorS)="P"));

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyName, DaveK.MailAddress;

--
KARL DEWEY
Build a little - Test a little

:

I have a query that produces the results:

PorS AgencyName MailAddress
P Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Affiliated-Stone Agency PO Box 909
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent 161 Mountain Rd
P Anderson-Krause, Inc. 238 E Main St
S Anderson-Krause, Inc. 238 E Main St
P Anderson-Meyer Ins., Inc. PO Box 977
S Anderson-Meyer Ins., Inc. PO Box 977
S Archambault Ins. Assoc. PO Box 153

As you can see I have Multiple [AgencyName] with the same address. The P =
Primary and S = Secondary for [PorS]. I need a query to only show the
Primary and only the secondary with a different address. The only exception
to this would be if the Primary and Secondary [Agencyname] are different.

Click to expand...

Click to expand...

KARL DEWEY · Mar 12, 2009

<<I need to add the following:
AgencyCode
SortName
MailCity
MailState
MailZip

If those other fields have different data then I would expect the list to
grow.
--
KARL DEWEY
Build a little - Test a little

Dave K. said:
It is not in there because it does not work. I have no idea where to place
it. [agencyCode] appears in queries DaveK and DaveK_1.
--
Dave K.

KARL DEWEY said:

<<every time I add [AgencyCode]
I do not see [AgencyCode] in your query.

--
KARL DEWEY
Build a little - Test a little

Dave K. said:

Karl, I have this query set up, but every time I add [AgencyCode] to the mix
it shows all the records instead of the specific ones that were requested.

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
ORDER BY DaveK.SortName;

--
Dave K.

:

Try these two queries --
DaveK_1 ---
SELECT DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK
GROUP BY DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
HAVING (((DaveK.PorS)="P"));

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyName, DaveK.MailAddress;

--
KARL DEWEY
Build a little - Test a little

:

I have a query that produces the results:

PorS AgencyName MailAddress
P Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Affiliated-Stone Agency PO Box 909
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent 161 Mountain Rd
P Anderson-Krause, Inc. 238 E Main St
S Anderson-Krause, Inc. 238 E Main St
P Anderson-Meyer Ins., Inc. PO Box 977
S Anderson-Meyer Ins., Inc. PO Box 977
S Archambault Ins. Assoc. PO Box 153

As you can see I have Multiple [AgencyName] with the same address. The P =
Primary and S = Secondary for [PorS]. I need a query to only show the
Primary and only the secondary with a different address. The only exception
to this would be if the Primary and Secondary [Agencyname] are different.

Click to expand...

Click to expand...

Dave K. · Mar 12, 2009

When I add SortName, MailCity, MailState and MailZip the Query stays the
same. Only when I add the AgencyCode does it include all the records from
DaveK Query.

Without AgecnyCode:

P_or_S /SortName/ AgencyName /MailAddress /MailCity/ MailState/ MailZip

P / Adam / Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe
/CT /06468
S / Affi/ Affiliated-Stone Agency/ PO Box 909
/Meriden /CT/ 06450
P / Ahre / Ahrens, Fuller, St. John & Vincent, Inc./161 Mountain
Rd /Suffield/ CT/ 06078
P /Ande /Anderson-Krause, Inc./238 E Main St/ Branford /CT /06405
P /Ande /Anderson-Meyer Ins., Inc. /PO Box 977 /Glastonbury /CT /06033
P /Arch /Archambault Ins. Assoc./ 136 Main St Ste 104 / Danielson
/CT/ 06239

With AgencyCode:

P_or_S /AgencyCode /SortName /AgencyName /MailAddress /MailCity /MailState
/MailZip

P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468
S /74394 /Affi /Affiliated-Stone Agency /PO Box 909 / Meriden /CT /06450
P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
Dave K.

KARL DEWEY said:
<<I need to add the following:
AgencyCode
SortName
MailCity
MailState
MailZip

If those other fields have different data then I would expect the list to
grow.
--
KARL DEWEY
Build a little - Test a little

Dave K. said:

It is not in there because it does not work. I have no idea where to place
it. [agencyCode] appears in queries DaveK and DaveK_1.
--
Dave K.

KARL DEWEY said:

<<every time I add [AgencyCode]
I do not see [AgencyCode] in your query.

--
KARL DEWEY
Build a little - Test a little

:

Karl, I have this query set up, but every time I add [AgencyCode] to the mix
it shows all the records instead of the specific ones that were requested.

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
ORDER BY DaveK.SortName;

--
Dave K.

:

Try these two queries --
DaveK_1 ---
SELECT DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK
GROUP BY DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
HAVING (((DaveK.PorS)="P"));

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyName, DaveK.MailAddress;

--
KARL DEWEY
Build a little - Test a little

:

I have a query that produces the results:

PorS AgencyName MailAddress
P Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Affiliated-Stone Agency PO Box 909
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent 161 Mountain Rd
P Anderson-Krause, Inc. 238 E Main St
S Anderson-Krause, Inc. 238 E Main St
P Anderson-Meyer Ins., Inc. PO Box 977
S Anderson-Meyer Ins., Inc. PO Box 977
S Archambault Ins. Assoc. PO Box 153

As you can see I have Multiple [AgencyName] with the same address. The P =
Primary and S = Secondary for [PorS]. I need a query to only show the
Primary and only the secondary with a different address. The only exception
to this would be if the Primary and Secondary [Agencyname] are different.

Click to expand...

Click to expand...

KARL DEWEY · Mar 12, 2009

P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
is not the same -- 73000 vs 70300 AND LLC / 227 vs LLC
227
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468

P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
70019 vs 70350
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
KARL DEWEY
Build a little - Test a little

Dave K. said:
When I add SortName, MailCity, MailState and MailZip the Query stays the
same. Only when I add the AgencyCode does it include all the records from
DaveK Query.

Without AgecnyCode:

P_or_S /SortName/ AgencyName /MailAddress /MailCity/ MailState/ MailZip

P / Adam / Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe
/CT /06468
S / Affi/ Affiliated-Stone Agency/ PO Box 909
/Meriden /CT/ 06450
P / Ahre / Ahrens, Fuller, St. John & Vincent, Inc./161 Mountain
Rd /Suffield/ CT/ 06078
P /Ande /Anderson-Krause, Inc./238 E Main St/ Branford /CT /06405
P /Ande /Anderson-Meyer Ins., Inc. /PO Box 977 /Glastonbury /CT /06033
P /Arch /Archambault Ins. Assoc./ 136 Main St Ste 104 / Danielson
/CT/ 06239

With AgencyCode:

P_or_S /AgencyCode /SortName /AgencyName /MailAddress /MailCity /MailState
/MailZip

P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468
S /74394 /Affi /Affiliated-Stone Agency /PO Box 909 / Meriden /CT /06450
P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
Dave K.

KARL DEWEY said:

<<I need to add the following:
AgencyCode
SortName
MailCity
MailState
MailZip

If those other fields have different data then I would expect the list to
grow.
--
KARL DEWEY
Build a little - Test a little

Dave K. said:

It is not in there because it does not work. I have no idea where to place
it. [agencyCode] appears in queries DaveK and DaveK_1.
--
Dave K.

:

<<every time I add [AgencyCode]
I do not see [AgencyCode] in your query.

--
KARL DEWEY
Build a little - Test a little

:

Karl, I have this query set up, but every time I add [AgencyCode] to the mix
it shows all the records instead of the specific ones that were requested.

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
ORDER BY DaveK.SortName;

--
Dave K.

:

Try these two queries --
DaveK_1 ---
SELECT DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK
GROUP BY DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
HAVING (((DaveK.PorS)="P"));

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyName, DaveK.MailAddress;

--
KARL DEWEY
Build a little - Test a little

:

I have a query that produces the results:

PorS AgencyName MailAddress
P Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Affiliated-Stone Agency PO Box 909
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent 161 Mountain Rd
P Anderson-Krause, Inc. 238 E Main St
S Anderson-Krause, Inc. 238 E Main St
P Anderson-Meyer Ins., Inc. PO Box 977
S Anderson-Meyer Ins., Inc. PO Box 977
S Archambault Ins. Assoc. PO Box 153

As you can see I have Multiple [AgencyName] with the same address. The P =
Primary and S = Secondary for [PorS]. I need a query to only show the
Primary and only the secondary with a different address. The only exception
to this would be if the Primary and Secondary [Agencyname] are different.

Click to expand...

Click to expand...

Dave K. · Mar 13, 2009

Please Help. I do not know enough about SQL to have the following results.
You have been great so far and I really appreciate your help. I know that
the AgencyCode's are different which makes the records unique. I am only
concerned with the AcencyAddress and AgencyName being different. I only want
to return the matching AgencyCodes where only the AgencyName or AgencyAddress
are different. Example below:

Do not want this:
P_or_S Code Name Address City St zip
P 0001 name1 address1 City1 OH 11111
S 0002 name1 address1 City1 OH 11111 (do not
want this one)
P 0003 name1 address2 City1 OH 11111
S 0004 name1 address1 City2 OH 11111

I do want this:
P_or_S Code Name Address City St zip
P 0001 name1 address1 City1 OH 11111
P 0003 name1 address2 City1 OH 11111
S 0004 name1 address1 City2 OH 11111
Dave K.

KARL DEWEY said:
P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
is not the same -- 73000 vs 70300 AND LLC / 227 vs LLC
227
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468

P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
70019 vs 70350
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
KARL DEWEY
Build a little - Test a little

Dave K. said:

When I add SortName, MailCity, MailState and MailZip the Query stays the
same. Only when I add the AgencyCode does it include all the records from
DaveK Query.

Without AgecnyCode:

P_or_S /SortName/ AgencyName /MailAddress /MailCity/ MailState/ MailZip

P / Adam / Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe
/CT /06468
S / Affi/ Affiliated-Stone Agency/ PO Box 909
/Meriden /CT/ 06450
P / Ahre / Ahrens, Fuller, St. John & Vincent, Inc./161 Mountain
Rd /Suffield/ CT/ 06078
P /Ande /Anderson-Krause, Inc./238 E Main St/ Branford /CT /06405
P /Ande /Anderson-Meyer Ins., Inc. /PO Box 977 /Glastonbury /CT /06033
P /Arch /Archambault Ins. Assoc./ 136 Main St Ste 104 / Danielson
/CT/ 06239

With AgencyCode:

P_or_S /AgencyCode /SortName /AgencyName /MailAddress /MailCity /MailState
/MailZip

P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468
S /74394 /Affi /Affiliated-Stone Agency /PO Box 909 / Meriden /CT /06450
P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
Dave K.

KARL DEWEY said:

<<I need to add the following:
AgencyCode
SortName
MailCity
MailState
MailZip

If those other fields have different data then I would expect the list to
grow.
--
KARL DEWEY
Build a little - Test a little

:

It is not in there because it does not work. I have no idea where to place
it. [agencyCode] appears in queries DaveK and DaveK_1.
--
Dave K.

:

<<every time I add [AgencyCode]
I do not see [AgencyCode] in your query.

--
KARL DEWEY
Build a little - Test a little

:

Karl, I have this query set up, but every time I add [AgencyCode] to the mix
it shows all the records instead of the specific ones that were requested.

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
ORDER BY DaveK.SortName;

--
Dave K.

:

Try these two queries --
DaveK_1 ---
SELECT DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK
GROUP BY DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
HAVING (((DaveK.PorS)="P"));

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyName, DaveK.MailAddress;

--
KARL DEWEY
Build a little - Test a little

:

I have a query that produces the results:

PorS AgencyName MailAddress
P Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Affiliated-Stone Agency PO Box 909
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent 161 Mountain Rd
P Anderson-Krause, Inc. 238 E Main St
S Anderson-Krause, Inc. 238 E Main St
P Anderson-Meyer Ins., Inc. PO Box 977
S Anderson-Meyer Ins., Inc. PO Box 977
S Archambault Ins. Assoc. PO Box 153

As you can see I have Multiple [AgencyName] with the same address. The P =
Primary and S = Secondary for [PorS]. I need a query to only show the
Primary and only the secondary with a different address. The only exception
to this would be if the Primary and Secondary [Agencyname] are different.

Click to expand...

Click to expand...

Dave K. · Mar 13, 2009

Karl I have come up with the following, but there are still a couple records
that I do not need.. Query as follows: Then example of data. and what I
want. Thanks

Query:

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyCode, DaveK.SortName, DaveK.AgencyName,
DaveK.MailAddress, DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.AgencyName = DaveK_1.AgencyName) AND
(DaveK.MailAddress = DaveK_1.MailAddress)
WHERE (((DaveK.AgencyCode)=IIf([DaveK_1].[AgencyName] Is Null Or
[DaveK_1].[MailAddress] Is Null,[DaveK]![AgencyCode],[DaveK_1]![AgencyCode])))
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyCode, DaveK.SortName, DaveK.AgencyName,
DaveK.MailAddress, DaveK.MailCity, DaveK.MailState, DaveK.MailZip
HAVING (((DaveK.MailState)=[What State]))
ORDER BY DaveK.SortName;

This returns the data I want except I have multiples of some data Example
Below:

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
S 70601 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
S 73300 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
S 70600 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
P 70120 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492
P 70140 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492

I only want to return just one of these it does not matter which.

This is what I want Below:

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
P 70120 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492
S 70601 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
--
Dave K.

KARL DEWEY said:
P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
is not the same -- 73000 vs 70300 AND LLC / 227 vs LLC
227
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468

P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
70019 vs 70350
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
KARL DEWEY
Build a little - Test a little

Dave K. said:

When I add SortName, MailCity, MailState and MailZip the Query stays the
same. Only when I add the AgencyCode does it include all the records from
DaveK Query.

Without AgecnyCode:

P_or_S /SortName/ AgencyName /MailAddress /MailCity/ MailState/ MailZip

P / Adam / Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe
/CT /06468
S / Affi/ Affiliated-Stone Agency/ PO Box 909
/Meriden /CT/ 06450
P / Ahre / Ahrens, Fuller, St. John & Vincent, Inc./161 Mountain
Rd /Suffield/ CT/ 06078
P /Ande /Anderson-Krause, Inc./238 E Main St/ Branford /CT /06405
P /Ande /Anderson-Meyer Ins., Inc. /PO Box 977 /Glastonbury /CT /06033
P /Arch /Archambault Ins. Assoc./ 136 Main St Ste 104 / Danielson
/CT/ 06239

With AgencyCode:

P_or_S /AgencyCode /SortName /AgencyName /MailAddress /MailCity /MailState
/MailZip

P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468
S /74394 /Affi /Affiliated-Stone Agency /PO Box 909 / Meriden /CT /06450
P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
Dave K.

KARL DEWEY said:

<<I need to add the following:
AgencyCode
SortName
MailCity
MailState
MailZip

If those other fields have different data then I would expect the list to
grow.
--
KARL DEWEY
Build a little - Test a little

:

It is not in there because it does not work. I have no idea where to place
it. [agencyCode] appears in queries DaveK and DaveK_1.
--
Dave K.

:

<<every time I add [AgencyCode]
I do not see [AgencyCode] in your query.

--
KARL DEWEY
Build a little - Test a little

:

Karl, I have this query set up, but every time I add [AgencyCode] to the mix
it shows all the records instead of the specific ones that were requested.

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
ORDER BY DaveK.SortName;

--
Dave K.

:

Try these two queries --
DaveK_1 ---
SELECT DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK
GROUP BY DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
HAVING (((DaveK.PorS)="P"));

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyName, DaveK.MailAddress;

--
KARL DEWEY
Build a little - Test a little

:

I have a query that produces the results:

PorS AgencyName MailAddress
P Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Affiliated-Stone Agency PO Box 909
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent 161 Mountain Rd
P Anderson-Krause, Inc. 238 E Main St
S Anderson-Krause, Inc. 238 E Main St
P Anderson-Meyer Ins., Inc. PO Box 977
S Anderson-Meyer Ins., Inc. PO Box 977
S Archambault Ins. Assoc. PO Box 153

As you can see I have Multiple [AgencyName] with the same address. The P =
Primary and S = Secondary for [PorS]. I need a query to only show the
Primary and only the secondary with a different address. The only exception
to this would be if the Primary and Secondary [Agencyname] are different.

Click to expand...

Click to expand...

KARL DEWEY · Mar 13, 2009

Use the DaveK_1 query then these two --
DaveK_2
SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, Q.AgencyName, Q.MailAddress, Q.AgencyCode,
Q.SortName, Q.MailCity, Q.[Mail State], Q.MailZip
FROM DaveK AS Q LEFT JOIN DaveK_1 ON (Q.MailAddress = DaveK_1.MailAddress)
AND (Q.AgencyName = DaveK_1.AgencyName)
WHERE ((((SELECT COUNT(*) FROM [DaveK] Q1
WHERE Q1.[AgencyName] = Q.[AgencyName]
AND Q1.[MailAddress] = Q.[MailAddress]
AND (Q1.AgencyCode & Q1.SortName & Q1.MailCity & Q1.[Mail State] &
Q1.MailZip) < (Q.AgencyCode & Q.SortName & Q.MailCity & Q.[Mail State] &
Q.MailZip))+1)<2))
ORDER BY Q.AgencyName, Q.MailAddress, (Q.AgencyCode & Q.SortName &
Q.MailCity & Q.[Mail State] & Q.MailZip) DESC;

SELECT DaveK_2.P_or_S, DaveK_2.AgencyName, DaveK_2.MailAddress,
DaveK_2.AgencyCode, DaveK_2.SortName, DaveK_2.MailCity, DaveK_2.[Mail State],
DaveK_2.MailZip
FROM DaveK_2
GROUP BY DaveK_2.P_or_S, DaveK_2.AgencyName, DaveK_2.MailAddress,
DaveK_2.AgencyCode, DaveK_2.SortName, DaveK_2.MailCity, DaveK_2.[Mail State],
DaveK_2.MailZip;

--
KARL DEWEY
Build a little - Test a little

Dave K. said:
Karl I have come up with the following, but there are still a couple records
that I do not need.. Query as follows: Then example of data. and what I
want. Thanks

Query:

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyCode, DaveK.SortName, DaveK.AgencyName,
DaveK.MailAddress, DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.AgencyName = DaveK_1.AgencyName) AND
(DaveK.MailAddress = DaveK_1.MailAddress)
WHERE (((DaveK.AgencyCode)=IIf([DaveK_1].[AgencyName] Is Null Or
[DaveK_1].[MailAddress] Is Null,[DaveK]![AgencyCode],[DaveK_1]![AgencyCode])))
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyCode, DaveK.SortName, DaveK.AgencyName,
DaveK.MailAddress, DaveK.MailCity, DaveK.MailState, DaveK.MailZip
HAVING (((DaveK.MailState)=[What State]))
ORDER BY DaveK.SortName;

This returns the data I want except I have multiples of some data Example
Below:

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
S 70601 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
S 73300 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
S 70600 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
P 70120 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492
P 70140 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492

I only want to return just one of these it does not matter which.

This is what I want Below:

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
P 70120 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492
S 70601 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
--
Dave K.

KARL DEWEY said:

P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
is not the same -- 73000 vs 70300 AND LLC / 227 vs LLC
227
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468

P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
70019 vs 70350
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
KARL DEWEY
Build a little - Test a little

Dave K. said:

When I add SortName, MailCity, MailState and MailZip the Query stays the
same. Only when I add the AgencyCode does it include all the records from
DaveK Query.

Without AgecnyCode:

P_or_S /SortName/ AgencyName /MailAddress /MailCity/ MailState/ MailZip

P / Adam / Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe
/CT /06468
S / Affi/ Affiliated-Stone Agency/ PO Box 909
/Meriden /CT/ 06450
P / Ahre / Ahrens, Fuller, St. John & Vincent, Inc./161 Mountain
Rd /Suffield/ CT/ 06078
P /Ande /Anderson-Krause, Inc./238 E Main St/ Branford /CT /06405
P /Ande /Anderson-Meyer Ins., Inc. /PO Box 977 /Glastonbury /CT /06033
P /Arch /Archambault Ins. Assoc./ 136 Main St Ste 104 / Danielson
/CT/ 06239

With AgencyCode:

P_or_S /AgencyCode /SortName /AgencyName /MailAddress /MailCity /MailState
/MailZip

P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468
S /74394 /Affi /Affiliated-Stone Agency /PO Box 909 / Meriden /CT /06450
P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
Dave K.

:

<<I need to add the following:
AgencyCode
SortName
MailCity
MailState
MailZip

If those other fields have different data then I would expect the list to
grow.
--
KARL DEWEY
Build a little - Test a little

:

It is not in there because it does not work. I have no idea where to place
it. [agencyCode] appears in queries DaveK and DaveK_1.
--
Dave K.

:

<<every time I add [AgencyCode]
I do not see [AgencyCode] in your query.

--
KARL DEWEY
Build a little - Test a little

:

Karl, I have this query set up, but every time I add [AgencyCode] to the mix
it shows all the records instead of the specific ones that were requested.

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
ORDER BY DaveK.SortName;

--
Dave K.

:

Try these two queries --
DaveK_1 ---
SELECT DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK
GROUP BY DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
HAVING (((DaveK.PorS)="P"));

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyName, DaveK.MailAddress;

--
KARL DEWEY
Build a little - Test a little

:

I have a query that produces the results:

PorS AgencyName MailAddress
P Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Affiliated-Stone Agency PO Box 909
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent 161 Mountain Rd
P Anderson-Krause, Inc. 238 E Main St
S Anderson-Krause, Inc. 238 E Main St
P Anderson-Meyer Ins., Inc. PO Box 977
S Anderson-Meyer Ins., Inc. PO Box 977
S Archambault Ins. Assoc. PO Box 153

As you can see I have Multiple [AgencyName] with the same address. The P =
Primary and S = Secondary for [PorS]. I need a query to only show the
Primary and only the secondary with a different address. The only exception
to this would be if the Primary and Secondary [Agencyname] are different.

Click to expand...

Click to expand...

Dave K. · Mar 13, 2009

I tried this and it freezes up my computer and returns no results.

What are Q and Q1?
--
Dave K.

KARL DEWEY said:
Use the DaveK_1 query then these two --
DaveK_2
SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, Q.AgencyName, Q.MailAddress, Q.AgencyCode,
Q.SortName, Q.MailCity, Q.[Mail State], Q.MailZip
FROM DaveK AS Q LEFT JOIN DaveK_1 ON (Q.MailAddress = DaveK_1.MailAddress)
AND (Q.AgencyName = DaveK_1.AgencyName)
WHERE ((((SELECT COUNT(*) FROM [DaveK] Q1
WHERE Q1.[AgencyName] = Q.[AgencyName]
AND Q1.[MailAddress] = Q.[MailAddress]
AND (Q1.AgencyCode & Q1.SortName & Q1.MailCity & Q1.[Mail State] &
Q1.MailZip) < (Q.AgencyCode & Q.SortName & Q.MailCity & Q.[Mail State] &
Q.MailZip))+1)<2))
ORDER BY Q.AgencyName, Q.MailAddress, (Q.AgencyCode & Q.SortName &
Q.MailCity & Q.[Mail State] & Q.MailZip) DESC;

SELECT DaveK_2.P_or_S, DaveK_2.AgencyName, DaveK_2.MailAddress,
DaveK_2.AgencyCode, DaveK_2.SortName, DaveK_2.MailCity, DaveK_2.[Mail State],
DaveK_2.MailZip
FROM DaveK_2
GROUP BY DaveK_2.P_or_S, DaveK_2.AgencyName, DaveK_2.MailAddress,
DaveK_2.AgencyCode, DaveK_2.SortName, DaveK_2.MailCity, DaveK_2.[Mail State],
DaveK_2.MailZip;

--
KARL DEWEY
Build a little - Test a little

Dave K. said:

Karl I have come up with the following, but there are still a couple records
that I do not need.. Query as follows: Then example of data. and what I
want. Thanks

Query:

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyCode, DaveK.SortName, DaveK.AgencyName,
DaveK.MailAddress, DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.AgencyName = DaveK_1.AgencyName) AND
(DaveK.MailAddress = DaveK_1.MailAddress)
WHERE (((DaveK.AgencyCode)=IIf([DaveK_1].[AgencyName] Is Null Or
[DaveK_1].[MailAddress] Is Null,[DaveK]![AgencyCode],[DaveK_1]![AgencyCode])))
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyCode, DaveK.SortName, DaveK.AgencyName,
DaveK.MailAddress, DaveK.MailCity, DaveK.MailState, DaveK.MailZip
HAVING (((DaveK.MailState)=[What State]))
ORDER BY DaveK.SortName;

This returns the data I want except I have multiples of some data Example
Below:

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
S 70601 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
S 73300 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
S 70600 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
P 70120 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492
P 70140 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492

I only want to return just one of these it does not matter which.

This is what I want Below:

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
P 70120 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492
S 70601 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
--
Dave K.

KARL DEWEY said:

P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
is not the same -- 73000 vs 70300 AND LLC / 227 vs LLC
227
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468

P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
70019 vs 70350
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
KARL DEWEY
Build a little - Test a little

:

When I add SortName, MailCity, MailState and MailZip the Query stays the
same. Only when I add the AgencyCode does it include all the records from
DaveK Query.

Without AgecnyCode:

P_or_S /SortName/ AgencyName /MailAddress /MailCity/ MailState/ MailZip

P / Adam / Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe
/CT /06468
S / Affi/ Affiliated-Stone Agency/ PO Box 909
/Meriden /CT/ 06450
P / Ahre / Ahrens, Fuller, St. John & Vincent, Inc./161 Mountain
Rd /Suffield/ CT/ 06078
P /Ande /Anderson-Krause, Inc./238 E Main St/ Branford /CT /06405
P /Ande /Anderson-Meyer Ins., Inc. /PO Box 977 /Glastonbury /CT /06033
P /Arch /Archambault Ins. Assoc./ 136 Main St Ste 104 / Danielson
/CT/ 06239

With AgencyCode:

P_or_S /AgencyCode /SortName /AgencyName /MailAddress /MailCity /MailState
/MailZip

P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468
S /74394 /Affi /Affiliated-Stone Agency /PO Box 909 / Meriden /CT /06450
P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
Dave K.

:

<<I need to add the following:
AgencyCode
SortName
MailCity
MailState
MailZip

If those other fields have different data then I would expect the list to
grow.
--
KARL DEWEY
Build a little - Test a little

:

It is not in there because it does not work. I have no idea where to place
it. [agencyCode] appears in queries DaveK and DaveK_1.
--
Dave K.

:

<<every time I add [AgencyCode]
I do not see [AgencyCode] in your query.

--
KARL DEWEY
Build a little - Test a little

:

Karl, I have this query set up, but every time I add [AgencyCode] to the mix
it shows all the records instead of the specific ones that were requested.

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
ORDER BY DaveK.SortName;

--
Dave K.

:

Try these two queries --
DaveK_1 ---
SELECT DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK
GROUP BY DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
HAVING (((DaveK.PorS)="P"));

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyName, DaveK.MailAddress;

--
KARL DEWEY
Build a little - Test a little

:

I have a query that produces the results:

PorS AgencyName MailAddress
P Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Affiliated-Stone Agency PO Box 909
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent 161 Mountain Rd
P Anderson-Krause, Inc. 238 E Main St
S Anderson-Krause, Inc. 238 E Main St
P Anderson-Meyer Ins., Inc. PO Box 977
S Anderson-Meyer Ins., Inc. PO Box 977
S Archambault Ins. Assoc. PO Box 153

As you can see I have Multiple [AgencyName] with the same address. The P =
Primary and S = Secondary for [PorS]. I need a query to only show the
Primary and only the secondary with a different address. The only exception
to this would be if the Primary and Secondary [Agencyname] are different.

Click to expand...

Click to expand...

KARL DEWEY · Mar 13, 2009

What are Q and Q1?
They are aliases of DaveK --- FROM DaveK AS Q

Does DaveK_2 run or is it the last query causing the freeze?
--
KARL DEWEY
Build a little - Test a little

Dave K. said:
I tried this and it freezes up my computer and returns no results.

What are Q and Q1?
--
Dave K.

KARL DEWEY said:

Use the DaveK_1 query then these two --
DaveK_2
SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, Q.AgencyName, Q.MailAddress, Q.AgencyCode,
Q.SortName, Q.MailCity, Q.[Mail State], Q.MailZip
FROM DaveK AS Q LEFT JOIN DaveK_1 ON (Q.MailAddress = DaveK_1.MailAddress)
AND (Q.AgencyName = DaveK_1.AgencyName)
WHERE ((((SELECT COUNT(*) FROM [DaveK] Q1
WHERE Q1.[AgencyName] = Q.[AgencyName]
AND Q1.[MailAddress] = Q.[MailAddress]
AND (Q1.AgencyCode & Q1.SortName & Q1.MailCity & Q1.[Mail State] &
Q1.MailZip) < (Q.AgencyCode & Q.SortName & Q.MailCity & Q.[Mail State] &
Q.MailZip))+1)<2))
ORDER BY Q.AgencyName, Q.MailAddress, (Q.AgencyCode & Q.SortName &
Q.MailCity & Q.[Mail State] & Q.MailZip) DESC;

SELECT DaveK_2.P_or_S, DaveK_2.AgencyName, DaveK_2.MailAddress,
DaveK_2.AgencyCode, DaveK_2.SortName, DaveK_2.MailCity, DaveK_2.[Mail State],
DaveK_2.MailZip
FROM DaveK_2
GROUP BY DaveK_2.P_or_S, DaveK_2.AgencyName, DaveK_2.MailAddress,
DaveK_2.AgencyCode, DaveK_2.SortName, DaveK_2.MailCity, DaveK_2.[Mail State],
DaveK_2.MailZip;

--
KARL DEWEY
Build a little - Test a little

Dave K. said:

Karl I have come up with the following, but there are still a couple records
that I do not need.. Query as follows: Then example of data. and what I
want. Thanks

Query:

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyCode, DaveK.SortName, DaveK.AgencyName,
DaveK.MailAddress, DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.AgencyName = DaveK_1.AgencyName) AND
(DaveK.MailAddress = DaveK_1.MailAddress)
WHERE (((DaveK.AgencyCode)=IIf([DaveK_1].[AgencyName] Is Null Or
[DaveK_1].[MailAddress] Is Null,[DaveK]![AgencyCode],[DaveK_1]![AgencyCode])))
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyCode, DaveK.SortName, DaveK.AgencyName,
DaveK.MailAddress, DaveK.MailCity, DaveK.MailState, DaveK.MailZip
HAVING (((DaveK.MailState)=[What State]))
ORDER BY DaveK.SortName;

This returns the data I want except I have multiples of some data Example
Below:

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
S 70601 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
S 73300 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
S 70600 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
P 70120 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492
P 70140 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492

I only want to return just one of these it does not matter which.

This is what I want Below:

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
P 70120 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492
S 70601 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
--
Dave K.

:

P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
is not the same -- 73000 vs 70300 AND LLC / 227 vs LLC
227
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468

P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
70019 vs 70350
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
KARL DEWEY
Build a little - Test a little

:

When I add SortName, MailCity, MailState and MailZip the Query stays the
same. Only when I add the AgencyCode does it include all the records from
DaveK Query.

Without AgecnyCode:

P_or_S /SortName/ AgencyName /MailAddress /MailCity/ MailState/ MailZip

P / Adam / Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe
/CT /06468
S / Affi/ Affiliated-Stone Agency/ PO Box 909
/Meriden /CT/ 06450
P / Ahre / Ahrens, Fuller, St. John & Vincent, Inc./161 Mountain
Rd /Suffield/ CT/ 06078
P /Ande /Anderson-Krause, Inc./238 E Main St/ Branford /CT /06405
P /Ande /Anderson-Meyer Ins., Inc. /PO Box 977 /Glastonbury /CT /06033
P /Arch /Archambault Ins. Assoc./ 136 Main St Ste 104 / Danielson
/CT/ 06239

With AgencyCode:

P_or_S /AgencyCode /SortName /AgencyName /MailAddress /MailCity /MailState
/MailZip

P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468
S /74394 /Affi /Affiliated-Stone Agency /PO Box 909 / Meriden /CT /06450
P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
Dave K.

:

<<I need to add the following:
AgencyCode
SortName
MailCity
MailState
MailZip

If those other fields have different data then I would expect the list to
grow.
--
KARL DEWEY
Build a little - Test a little

:

It is not in there because it does not work. I have no idea where to place
it. [agencyCode] appears in queries DaveK and DaveK_1.
--
Dave K.

:

<<every time I add [AgencyCode]
I do not see [AgencyCode] in your query.

--
KARL DEWEY
Build a little - Test a little

:

Karl, I have this query set up, but every time I add [AgencyCode] to the mix
it shows all the records instead of the specific ones that were requested.

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
ORDER BY DaveK.SortName;

--
Dave K.

:

Try these two queries --
DaveK_1 ---
SELECT DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK
GROUP BY DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
HAVING (((DaveK.PorS)="P"));

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyName, DaveK.MailAddress;

--
KARL DEWEY
Build a little - Test a little

:

I have a query that produces the results:

PorS AgencyName MailAddress
P Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Affiliated-Stone Agency PO Box 909
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent 161 Mountain Rd
P Anderson-Krause, Inc. 238 E Main St
S Anderson-Krause, Inc. 238 E Main St
P Anderson-Meyer Ins., Inc. PO Box 977
S Anderson-Meyer Ins., Inc. PO Box 977
S Archambault Ins. Assoc. PO Box 153

As you can see I have Multiple [AgencyName] with the same address. The P =
Primary and S = Secondary for [PorS]. I need a query to only show the
Primary and only the secondary with a different address. The only exception
to this would be if the Primary and Secondary [Agencyname] are different.

Click to expand...

Click to expand...

Dave K. · Mar 13, 2009

DaveK_2 does not run
--
Dave K.

KARL DEWEY said:
What are Q and Q1?

Click to expand...

They are aliases of DaveK --- FROM DaveK AS Q

Does DaveK_2 run or is it the last query causing the freeze?
--
KARL DEWEY
Build a little - Test a little

Dave K. said:

I tried this and it freezes up my computer and returns no results.

What are Q and Q1?
--
Dave K.

KARL DEWEY said:

Use the DaveK_1 query then these two --
DaveK_2
SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, Q.AgencyName, Q.MailAddress, Q.AgencyCode,
Q.SortName, Q.MailCity, Q.[Mail State], Q.MailZip
FROM DaveK AS Q LEFT JOIN DaveK_1 ON (Q.MailAddress = DaveK_1.MailAddress)
AND (Q.AgencyName = DaveK_1.AgencyName)
WHERE ((((SELECT COUNT(*) FROM [DaveK] Q1
WHERE Q1.[AgencyName] = Q.[AgencyName]
AND Q1.[MailAddress] = Q.[MailAddress]
AND (Q1.AgencyCode & Q1.SortName & Q1.MailCity & Q1.[Mail State] &
Q1.MailZip) < (Q.AgencyCode & Q.SortName & Q.MailCity & Q.[Mail State] &
Q.MailZip))+1)<2))
ORDER BY Q.AgencyName, Q.MailAddress, (Q.AgencyCode & Q.SortName &
Q.MailCity & Q.[Mail State] & Q.MailZip) DESC;

SELECT DaveK_2.P_or_S, DaveK_2.AgencyName, DaveK_2.MailAddress,
DaveK_2.AgencyCode, DaveK_2.SortName, DaveK_2.MailCity, DaveK_2.[Mail State],
DaveK_2.MailZip
FROM DaveK_2
GROUP BY DaveK_2.P_or_S, DaveK_2.AgencyName, DaveK_2.MailAddress,
DaveK_2.AgencyCode, DaveK_2.SortName, DaveK_2.MailCity, DaveK_2.[Mail State],
DaveK_2.MailZip;

--
KARL DEWEY
Build a little - Test a little

:

Karl I have come up with the following, but there are still a couple records
that I do not need.. Query as follows: Then example of data. and what I
want. Thanks

Query:

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyCode, DaveK.SortName, DaveK.AgencyName,
DaveK.MailAddress, DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.AgencyName = DaveK_1.AgencyName) AND
(DaveK.MailAddress = DaveK_1.MailAddress)
WHERE (((DaveK.AgencyCode)=IIf([DaveK_1].[AgencyName] Is Null Or
[DaveK_1].[MailAddress] Is Null,[DaveK]![AgencyCode],[DaveK_1]![AgencyCode])))
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyCode, DaveK.SortName, DaveK.AgencyName,
DaveK.MailAddress, DaveK.MailCity, DaveK.MailState, DaveK.MailZip
HAVING (((DaveK.MailState)=[What State]))
ORDER BY DaveK.SortName;

This returns the data I want except I have multiples of some data Example
Below:

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
S 70601 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
S 73300 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
S 70600 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
P 70120 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492
P 70140 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492

I only want to return just one of these it does not matter which.

This is what I want Below:

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
P 70120 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492
S 70601 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
--
Dave K.

:

P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
is not the same -- 73000 vs 70300 AND LLC / 227 vs LLC
227
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468

P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
70019 vs 70350
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
KARL DEWEY
Build a little - Test a little

:

When I add SortName, MailCity, MailState and MailZip the Query stays the
same. Only when I add the AgencyCode does it include all the records from
DaveK Query.

Without AgecnyCode:

P_or_S /SortName/ AgencyName /MailAddress /MailCity/ MailState/ MailZip

P / Adam / Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe
/CT /06468
S / Affi/ Affiliated-Stone Agency/ PO Box 909
/Meriden /CT/ 06450
P / Ahre / Ahrens, Fuller, St. John & Vincent, Inc./161 Mountain
Rd /Suffield/ CT/ 06078
P /Ande /Anderson-Krause, Inc./238 E Main St/ Branford /CT /06405
P /Ande /Anderson-Meyer Ins., Inc. /PO Box 977 /Glastonbury /CT /06033
P /Arch /Archambault Ins. Assoc./ 136 Main St Ste 104 / Danielson
/CT/ 06239

With AgencyCode:

P_or_S /AgencyCode /SortName /AgencyName /MailAddress /MailCity /MailState
/MailZip

P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468
S /74394 /Affi /Affiliated-Stone Agency /PO Box 909 / Meriden /CT /06450
P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
Dave K.

:

<<I need to add the following:
AgencyCode
SortName
MailCity
MailState
MailZip

If those other fields have different data then I would expect the list to
grow.
--
KARL DEWEY
Build a little - Test a little

:

It is not in there because it does not work. I have no idea where to place
it. [agencyCode] appears in queries DaveK and DaveK_1.
--
Dave K.

:

<<every time I add [AgencyCode]
I do not see [AgencyCode] in your query.

--
KARL DEWEY
Build a little - Test a little

:

Karl, I have this query set up, but every time I add [AgencyCode] to the mix
it shows all the records instead of the specific ones that were requested.

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
ORDER BY DaveK.SortName;

--
Dave K.

:

Try these two queries --
DaveK_1 ---
SELECT DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK
GROUP BY DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
HAVING (((DaveK.PorS)="P"));

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyName, DaveK.MailAddress;

--
KARL DEWEY
Build a little - Test a little

:

I have a query that produces the results:

PorS AgencyName MailAddress
P Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Affiliated-Stone Agency PO Box 909
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent 161 Mountain Rd
P Anderson-Krause, Inc. 238 E Main St
S Anderson-Krause, Inc. 238 E Main St
P Anderson-Meyer Ins., Inc. PO Box 977
S Anderson-Meyer Ins., Inc. PO Box 977
S Archambault Ins. Assoc. PO Box 153

As you can see I have Multiple [AgencyName] with the same address. The P =
Primary and S = Secondary for [PorS]. I need a query to only show the
Primary and only the secondary with a different address. The only exception
to this would be if the Primary and Secondary [Agencyname] are different.

Click to expand...

Click to expand...

KARL DEWEY · Mar 13, 2009

Did you make sure to edit out any hard returns that copying and pasting may
have added to the post?
--
KARL DEWEY
Build a little - Test a little

Dave K. said:
DaveK_2 does not run
--
Dave K.

KARL DEWEY said:

What are Q and Q1?

Click to expand...

They are aliases of DaveK --- FROM DaveK AS Q

Does DaveK_2 run or is it the last query causing the freeze?
--
KARL DEWEY
Build a little - Test a little

Dave K. said:

I tried this and it freezes up my computer and returns no results.

What are Q and Q1?
--
Dave K.

:

Use the DaveK_1 query then these two --
DaveK_2
SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, Q.AgencyName, Q.MailAddress, Q.AgencyCode,
Q.SortName, Q.MailCity, Q.[Mail State], Q.MailZip
FROM DaveK AS Q LEFT JOIN DaveK_1 ON (Q.MailAddress = DaveK_1.MailAddress)
AND (Q.AgencyName = DaveK_1.AgencyName)
WHERE ((((SELECT COUNT(*) FROM [DaveK] Q1
WHERE Q1.[AgencyName] = Q.[AgencyName]
AND Q1.[MailAddress] = Q.[MailAddress]
AND (Q1.AgencyCode & Q1.SortName & Q1.MailCity & Q1.[Mail State] &
Q1.MailZip) < (Q.AgencyCode & Q.SortName & Q.MailCity & Q.[Mail State] &
Q.MailZip))+1)<2))
ORDER BY Q.AgencyName, Q.MailAddress, (Q.AgencyCode & Q.SortName &
Q.MailCity & Q.[Mail State] & Q.MailZip) DESC;

SELECT DaveK_2.P_or_S, DaveK_2.AgencyName, DaveK_2.MailAddress,
DaveK_2.AgencyCode, DaveK_2.SortName, DaveK_2.MailCity, DaveK_2.[Mail State],
DaveK_2.MailZip
FROM DaveK_2
GROUP BY DaveK_2.P_or_S, DaveK_2.AgencyName, DaveK_2.MailAddress,
DaveK_2.AgencyCode, DaveK_2.SortName, DaveK_2.MailCity, DaveK_2.[Mail State],
DaveK_2.MailZip;

--
KARL DEWEY
Build a little - Test a little

:

Karl I have come up with the following, but there are still a couple records
that I do not need.. Query as follows: Then example of data. and what I
want. Thanks

Query:

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyCode, DaveK.SortName, DaveK.AgencyName,
DaveK.MailAddress, DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.AgencyName = DaveK_1.AgencyName) AND
(DaveK.MailAddress = DaveK_1.MailAddress)
WHERE (((DaveK.AgencyCode)=IIf([DaveK_1].[AgencyName] Is Null Or
[DaveK_1].[MailAddress] Is Null,[DaveK]![AgencyCode],[DaveK_1]![AgencyCode])))
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyCode, DaveK.SortName, DaveK.AgencyName,
DaveK.MailAddress, DaveK.MailCity, DaveK.MailState, DaveK.MailZip
HAVING (((DaveK.MailState)=[What State]))
ORDER BY DaveK.SortName;

This returns the data I want except I have multiples of some data Example
Below:

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
S 70601 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
S 73300 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
S 70600 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
P 70120 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492
P 70140 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492

I only want to return just one of these it does not matter which.

This is what I want Below:

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
P 70120 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492
S 70601 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
--
Dave K.

:

P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
is not the same -- 73000 vs 70300 AND LLC / 227 vs LLC
227
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468

P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
70019 vs 70350
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
KARL DEWEY
Build a little - Test a little

:

When I add SortName, MailCity, MailState and MailZip the Query stays the
same. Only when I add the AgencyCode does it include all the records from
DaveK Query.

Without AgecnyCode:

P_or_S /SortName/ AgencyName /MailAddress /MailCity/ MailState/ MailZip

P / Adam / Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe
/CT /06468
S / Affi/ Affiliated-Stone Agency/ PO Box 909
/Meriden /CT/ 06450
P / Ahre / Ahrens, Fuller, St. John & Vincent, Inc./161 Mountain
Rd /Suffield/ CT/ 06078
P /Ande /Anderson-Krause, Inc./238 E Main St/ Branford /CT /06405
P /Ande /Anderson-Meyer Ins., Inc. /PO Box 977 /Glastonbury /CT /06033
P /Arch /Archambault Ins. Assoc./ 136 Main St Ste 104 / Danielson
/CT/ 06239

With AgencyCode:

P_or_S /AgencyCode /SortName /AgencyName /MailAddress /MailCity /MailState
/MailZip

P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468
S /74394 /Affi /Affiliated-Stone Agency /PO Box 909 / Meriden /CT /06450
P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
Dave K.

:

<<I need to add the following:
AgencyCode
SortName
MailCity
MailState
MailZip

If those other fields have different data then I would expect the list to
grow.
--
KARL DEWEY
Build a little - Test a little

:

It is not in there because it does not work. I have no idea where to place
it. [agencyCode] appears in queries DaveK and DaveK_1.
--
Dave K.

:

<<every time I add [AgencyCode]
I do not see [AgencyCode] in your query.

--
KARL DEWEY
Build a little - Test a little

:

Karl, I have this query set up, but every time I add [AgencyCode] to the mix
it shows all the records instead of the specific ones that were requested.

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
ORDER BY DaveK.SortName;

--
Dave K.

:

Try these two queries --
DaveK_1 ---
SELECT DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK
GROUP BY DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
HAVING (((DaveK.PorS)="P"));

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyName, DaveK.MailAddress;

--
KARL DEWEY
Build a little - Test a little

:

I have a query that produces the results:

PorS AgencyName MailAddress
P Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Affiliated-Stone Agency PO Box 909
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent 161 Mountain Rd
P Anderson-Krause, Inc. 238 E Main St
S Anderson-Krause, Inc. 238 E Main St
P Anderson-Meyer Ins., Inc. PO Box 977
S Anderson-Meyer Ins., Inc. PO Box 977
S Archambault Ins. Assoc. PO Box 153

As you can see I have Multiple [AgencyName] with the same address. The P =
Primary and S = Secondary for [PorS]. I need a query to only show the
Primary and only the secondary with a different address. The only exception
to this would be if the Primary and Secondary [Agencyname] are different.

Click to expand...

Click to expand...

Dave K. · Mar 16, 2009

Yes I did. It just sits there and I have to do end task to get out of it.
--
Dave K.

KARL DEWEY said:
Did you make sure to edit out any hard returns that copying and pasting may
have added to the post?
--
KARL DEWEY
Build a little - Test a little

Dave K. said:

DaveK_2 does not run
--
Dave K.

KARL DEWEY said:

What are Q and Q1?
They are aliases of DaveK --- FROM DaveK AS Q

Does DaveK_2 run or is it the last query causing the freeze?
--
KARL DEWEY
Build a little - Test a little

:

I tried this and it freezes up my computer and returns no results.

What are Q and Q1?
--
Dave K.

:

Use the DaveK_1 query then these two --
DaveK_2
SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, Q.AgencyName, Q.MailAddress, Q.AgencyCode,
Q.SortName, Q.MailCity, Q.[Mail State], Q.MailZip
FROM DaveK AS Q LEFT JOIN DaveK_1 ON (Q.MailAddress = DaveK_1.MailAddress)
AND (Q.AgencyName = DaveK_1.AgencyName)
WHERE ((((SELECT COUNT(*) FROM [DaveK] Q1
WHERE Q1.[AgencyName] = Q.[AgencyName]
AND Q1.[MailAddress] = Q.[MailAddress]
AND (Q1.AgencyCode & Q1.SortName & Q1.MailCity & Q1.[Mail State] &
Q1.MailZip) < (Q.AgencyCode & Q.SortName & Q.MailCity & Q.[Mail State] &
Q.MailZip))+1)<2))
ORDER BY Q.AgencyName, Q.MailAddress, (Q.AgencyCode & Q.SortName &
Q.MailCity & Q.[Mail State] & Q.MailZip) DESC;

SELECT DaveK_2.P_or_S, DaveK_2.AgencyName, DaveK_2.MailAddress,
DaveK_2.AgencyCode, DaveK_2.SortName, DaveK_2.MailCity, DaveK_2.[Mail State],
DaveK_2.MailZip
FROM DaveK_2
GROUP BY DaveK_2.P_or_S, DaveK_2.AgencyName, DaveK_2.MailAddress,
DaveK_2.AgencyCode, DaveK_2.SortName, DaveK_2.MailCity, DaveK_2.[Mail State],
DaveK_2.MailZip;

--
KARL DEWEY
Build a little - Test a little

:

Karl I have come up with the following, but there are still a couple records
that I do not need.. Query as follows: Then example of data. and what I
want. Thanks

Query:

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyCode, DaveK.SortName, DaveK.AgencyName,
DaveK.MailAddress, DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.AgencyName = DaveK_1.AgencyName) AND
(DaveK.MailAddress = DaveK_1.MailAddress)
WHERE (((DaveK.AgencyCode)=IIf([DaveK_1].[AgencyName] Is Null Or
[DaveK_1].[MailAddress] Is Null,[DaveK]![AgencyCode],[DaveK_1]![AgencyCode])))
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyCode, DaveK.SortName, DaveK.AgencyName,
DaveK.MailAddress, DaveK.MailCity, DaveK.MailState, DaveK.MailZip
HAVING (((DaveK.MailState)=[What State]))
ORDER BY DaveK.SortName;

This returns the data I want except I have multiples of some data Example
Below:

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
S 70601 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
S 73300 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
S 70600 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
P 70120 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492
P 70140 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492

I only want to return just one of these it does not matter which.

This is what I want Below:

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
P 70120 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492
S 70601 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
--
Dave K.

:

P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
is not the same -- 73000 vs 70300 AND LLC / 227 vs LLC
227
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468

P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
70019 vs 70350
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
KARL DEWEY
Build a little - Test a little

:

When I add SortName, MailCity, MailState and MailZip the Query stays the
same. Only when I add the AgencyCode does it include all the records from
DaveK Query.

Without AgecnyCode:

P_or_S /SortName/ AgencyName /MailAddress /MailCity/ MailState/ MailZip

P / Adam / Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe
/CT /06468
S / Affi/ Affiliated-Stone Agency/ PO Box 909
/Meriden /CT/ 06450
P / Ahre / Ahrens, Fuller, St. John & Vincent, Inc./161 Mountain
Rd /Suffield/ CT/ 06078
P /Ande /Anderson-Krause, Inc./238 E Main St/ Branford /CT /06405
P /Ande /Anderson-Meyer Ins., Inc. /PO Box 977 /Glastonbury /CT /06033
P /Arch /Archambault Ins. Assoc./ 136 Main St Ste 104 / Danielson
/CT/ 06239

With AgencyCode:

P_or_S /AgencyCode /SortName /AgencyName /MailAddress /MailCity /MailState
/MailZip

P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468
S /74394 /Affi /Affiliated-Stone Agency /PO Box 909 / Meriden /CT /06450
P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
Dave K.

:

<<I need to add the following:
AgencyCode
SortName
MailCity
MailState
MailZip

If those other fields have different data then I would expect the list to
grow.
--
KARL DEWEY
Build a little - Test a little

:

It is not in there because it does not work. I have no idea where to place
it. [agencyCode] appears in queries DaveK and DaveK_1.
--
Dave K.

:

<<every time I add [AgencyCode]
I do not see [AgencyCode] in your query.

--
KARL DEWEY
Build a little - Test a little

:

Karl, I have this query set up, but every time I add [AgencyCode] to the mix
it shows all the records instead of the specific ones that were requested.

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
ORDER BY DaveK.SortName;

--
Dave K.

:

Try these two queries --
DaveK_1 ---
SELECT DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK
GROUP BY DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
HAVING (((DaveK.PorS)="P"));

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyName, DaveK.MailAddress;

--
KARL DEWEY
Build a little - Test a little

:

I have a query that produces the results:

PorS AgencyName MailAddress
P Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Affiliated-Stone Agency PO Box 909
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent 161 Mountain Rd
P Anderson-Krause, Inc. 238 E Main St
S Anderson-Krause, Inc. 238 E Main St
P Anderson-Meyer Ins., Inc. PO Box 977
S Anderson-Meyer Ins., Inc. PO Box 977
S Archambault Ins. Assoc. PO Box 153

As you can see I have Multiple [AgencyName] with the same address. The P =
Primary and S = Secondary for [PorS]. I need a query to only show the
Primary and only the secondary with a different address. The only exception
to this would be if the Primary and Secondary [Agencyname] are different.

Click to expand...

Click to expand...

KARL DEWEY · Mar 16, 2009

Post back what you have for DaveK_2.
--
KARL DEWEY
Build a little - Test a little

Dave K. said:
Yes I did. It just sits there and I have to do end task to get out of it.
--
Dave K.

KARL DEWEY said:

Did you make sure to edit out any hard returns that copying and pasting may
have added to the post?
--
KARL DEWEY
Build a little - Test a little

Dave K. said:

DaveK_2 does not run
--
Dave K.

:

What are Q and Q1?
They are aliases of DaveK --- FROM DaveK AS Q

Does DaveK_2 run or is it the last query causing the freeze?
--
KARL DEWEY
Build a little - Test a little

:

I tried this and it freezes up my computer and returns no results.

What are Q and Q1?
--
Dave K.

:

Use the DaveK_1 query then these two --
DaveK_2
SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, Q.AgencyName, Q.MailAddress, Q.AgencyCode,
Q.SortName, Q.MailCity, Q.[Mail State], Q.MailZip
FROM DaveK AS Q LEFT JOIN DaveK_1 ON (Q.MailAddress = DaveK_1.MailAddress)
AND (Q.AgencyName = DaveK_1.AgencyName)
WHERE ((((SELECT COUNT(*) FROM [DaveK] Q1
WHERE Q1.[AgencyName] = Q.[AgencyName]
AND Q1.[MailAddress] = Q.[MailAddress]
AND (Q1.AgencyCode & Q1.SortName & Q1.MailCity & Q1.[Mail State] &
Q1.MailZip) < (Q.AgencyCode & Q.SortName & Q.MailCity & Q.[Mail State] &
Q.MailZip))+1)<2))
ORDER BY Q.AgencyName, Q.MailAddress, (Q.AgencyCode & Q.SortName &
Q.MailCity & Q.[Mail State] & Q.MailZip) DESC;

SELECT DaveK_2.P_or_S, DaveK_2.AgencyName, DaveK_2.MailAddress,
DaveK_2.AgencyCode, DaveK_2.SortName, DaveK_2.MailCity, DaveK_2.[Mail State],
DaveK_2.MailZip
FROM DaveK_2
GROUP BY DaveK_2.P_or_S, DaveK_2.AgencyName, DaveK_2.MailAddress,
DaveK_2.AgencyCode, DaveK_2.SortName, DaveK_2.MailCity, DaveK_2.[Mail State],
DaveK_2.MailZip;

--
KARL DEWEY
Build a little - Test a little

:

Karl I have come up with the following, but there are still a couple records
that I do not need.. Query as follows: Then example of data. and what I
want. Thanks

Query:

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyCode, DaveK.SortName, DaveK.AgencyName,
DaveK.MailAddress, DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.AgencyName = DaveK_1.AgencyName) AND
(DaveK.MailAddress = DaveK_1.MailAddress)
WHERE (((DaveK.AgencyCode)=IIf([DaveK_1].[AgencyName] Is Null Or
[DaveK_1].[MailAddress] Is Null,[DaveK]![AgencyCode],[DaveK_1]![AgencyCode])))
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyCode, DaveK.SortName, DaveK.AgencyName,
DaveK.MailAddress, DaveK.MailCity, DaveK.MailState, DaveK.MailZip
HAVING (((DaveK.MailState)=[What State]))
ORDER BY DaveK.SortName;

This returns the data I want except I have multiples of some data Example
Below:

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
S 70601 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
S 73300 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
S 70600 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
P 70120 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492
P 70140 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492

I only want to return just one of these it does not matter which.

This is what I want Below:

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
P 70120 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492
S 70601 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
--
Dave K.

:

P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
is not the same -- 73000 vs 70300 AND LLC / 227 vs LLC
227
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468

P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
70019 vs 70350
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
KARL DEWEY
Build a little - Test a little

:

When I add SortName, MailCity, MailState and MailZip the Query stays the
same. Only when I add the AgencyCode does it include all the records from
DaveK Query.

Without AgecnyCode:

P_or_S /SortName/ AgencyName /MailAddress /MailCity/ MailState/ MailZip

P / Adam / Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe
/CT /06468
S / Affi/ Affiliated-Stone Agency/ PO Box 909
/Meriden /CT/ 06450
P / Ahre / Ahrens, Fuller, St. John & Vincent, Inc./161 Mountain
Rd /Suffield/ CT/ 06078
P /Ande /Anderson-Krause, Inc./238 E Main St/ Branford /CT /06405
P /Ande /Anderson-Meyer Ins., Inc. /PO Box 977 /Glastonbury /CT /06033
P /Arch /Archambault Ins. Assoc./ 136 Main St Ste 104 / Danielson
/CT/ 06239

With AgencyCode:

P_or_S /AgencyCode /SortName /AgencyName /MailAddress /MailCity /MailState
/MailZip

P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468
S /74394 /Affi /Affiliated-Stone Agency /PO Box 909 / Meriden /CT /06450
P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
Dave K.

:

<<I need to add the following:
AgencyCode
SortName
MailCity
MailState
MailZip

If those other fields have different data then I would expect the list to
grow.
--
KARL DEWEY
Build a little - Test a little

:

It is not in there because it does not work. I have no idea where to place
it. [agencyCode] appears in queries DaveK and DaveK_1.
--
Dave K.

:

<<every time I add [AgencyCode]
I do not see [AgencyCode] in your query.

--
KARL DEWEY
Build a little - Test a little

:

Karl, I have this query set up, but every time I add [AgencyCode] to the mix
it shows all the records instead of the specific ones that were requested.

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
ORDER BY DaveK.SortName;

--
Dave K.

:

Try these two queries --
DaveK_1 ---
SELECT DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK
GROUP BY DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
HAVING (((DaveK.PorS)="P"));

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyName, DaveK.MailAddress;

--
KARL DEWEY
Build a little - Test a little

:

I have a query that produces the results:

PorS AgencyName MailAddress
P Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Affiliated-Stone Agency PO Box 909
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent 161 Mountain Rd
P Anderson-Krause, Inc. 238 E Main St
S Anderson-Krause, Inc. 238 E Main St
P Anderson-Meyer Ins., Inc. PO Box 977
S Anderson-Meyer Ins., Inc. PO Box 977
S Archambault Ins. Assoc. PO Box 153

As you can see I have Multiple [AgencyName] with the same address. The P =
Primary and S = Secondary for [PorS]. I need a query to only show the
Primary and only the secondary with a different address. The only exception
to this would be if the Primary and Secondary [Agencyname] are different.

Click to expand...

Click to expand...

Dave K. · Mar 16, 2009

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, Q.AgencyName, Q.MailAddress, Q.AgencyCode,
Q.SortName, Q.MailCity, Q.[Mail State] AS Expr1, Q.MailZip
FROM DaveK AS Q LEFT JOIN DaveK_1 ON (Q.AgencyName = DaveK_1.AgencyName) AND
(Q.MailAddress = DaveK_1.MailAddress)
WHERE ((((SELECT COUNT(*) FROM [DaveK] Q1
WHERE Q1.[AgencyName] = Q.[AgencyName]
AND Q1.[MailAddress] = Q.[MailAddress]
AND (Q1.AgencyCode & Q1.SortName & Q1.MailCity & Q1.[Mail State] &
Q1.MailZip) < (Q.AgencyCode & Q.SortName & Q.MailCity & Q.[Mail State] &
Q.MailZip))+1)<2))
ORDER BY Q.AgencyName, Q.MailAddress, (Q.AgencyCode & Q.SortName &
Q.MailCity & Q.[Mail State] & Q.MailZip) DESC;

--
Dave K.

KARL DEWEY said:
Post back what you have for DaveK_2.
--
KARL DEWEY
Build a little - Test a little

Dave K. said:

Yes I did. It just sits there and I have to do end task to get out of it.
--
Dave K.

KARL DEWEY said:

Did you make sure to edit out any hard returns that copying and pasting may
have added to the post?
--
KARL DEWEY
Build a little - Test a little

:

DaveK_2 does not run
--
Dave K.

:

What are Q and Q1?
They are aliases of DaveK --- FROM DaveK AS Q

Does DaveK_2 run or is it the last query causing the freeze?
--
KARL DEWEY
Build a little - Test a little

:

I tried this and it freezes up my computer and returns no results.

What are Q and Q1?
--
Dave K.

:

Use the DaveK_1 query then these two --
DaveK_2
SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, Q.AgencyName, Q.MailAddress, Q.AgencyCode,
Q.SortName, Q.MailCity, Q.[Mail State], Q.MailZip
FROM DaveK AS Q LEFT JOIN DaveK_1 ON (Q.MailAddress = DaveK_1.MailAddress)
AND (Q.AgencyName = DaveK_1.AgencyName)
WHERE ((((SELECT COUNT(*) FROM [DaveK] Q1
WHERE Q1.[AgencyName] = Q.[AgencyName]
AND Q1.[MailAddress] = Q.[MailAddress]
AND (Q1.AgencyCode & Q1.SortName & Q1.MailCity & Q1.[Mail State] &
Q1.MailZip) < (Q.AgencyCode & Q.SortName & Q.MailCity & Q.[Mail State] &
Q.MailZip))+1)<2))
ORDER BY Q.AgencyName, Q.MailAddress, (Q.AgencyCode & Q.SortName &
Q.MailCity & Q.[Mail State] & Q.MailZip) DESC;

SELECT DaveK_2.P_or_S, DaveK_2.AgencyName, DaveK_2.MailAddress,
DaveK_2.AgencyCode, DaveK_2.SortName, DaveK_2.MailCity, DaveK_2.[Mail State],
DaveK_2.MailZip
FROM DaveK_2
GROUP BY DaveK_2.P_or_S, DaveK_2.AgencyName, DaveK_2.MailAddress,
DaveK_2.AgencyCode, DaveK_2.SortName, DaveK_2.MailCity, DaveK_2.[Mail State],
DaveK_2.MailZip;

--
KARL DEWEY
Build a little - Test a little

:

Karl I have come up with the following, but there are still a couple records
that I do not need.. Query as follows: Then example of data. and what I
want. Thanks

Query:

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyCode, DaveK.SortName, DaveK.AgencyName,
DaveK.MailAddress, DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.AgencyName = DaveK_1.AgencyName) AND
(DaveK.MailAddress = DaveK_1.MailAddress)
WHERE (((DaveK.AgencyCode)=IIf([DaveK_1].[AgencyName] Is Null Or
[DaveK_1].[MailAddress] Is Null,[DaveK]![AgencyCode],[DaveK_1]![AgencyCode])))
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyCode, DaveK.SortName, DaveK.AgencyName,
DaveK.MailAddress, DaveK.MailCity, DaveK.MailState, DaveK.MailZip
HAVING (((DaveK.MailState)=[What State]))
ORDER BY DaveK.SortName;

This returns the data I want except I have multiples of some data Example
Below:

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
S 70601 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
S 73300 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
S 70600 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
P 70120 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492
P 70140 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492

I only want to return just one of these it does not matter which.

This is what I want Below:

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
P 70120 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492
S 70601 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
--
Dave K.

:

P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
is not the same -- 73000 vs 70300 AND LLC / 227 vs LLC
227
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468

P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
70019 vs 70350
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
KARL DEWEY
Build a little - Test a little

:

When I add SortName, MailCity, MailState and MailZip the Query stays the
same. Only when I add the AgencyCode does it include all the records from
DaveK Query.

Without AgecnyCode:

P_or_S /SortName/ AgencyName /MailAddress /MailCity/ MailState/ MailZip

P / Adam / Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe
/CT /06468
S / Affi/ Affiliated-Stone Agency/ PO Box 909
/Meriden /CT/ 06450
P / Ahre / Ahrens, Fuller, St. John & Vincent, Inc./161 Mountain
Rd /Suffield/ CT/ 06078
P /Ande /Anderson-Krause, Inc./238 E Main St/ Branford /CT /06405
P /Ande /Anderson-Meyer Ins., Inc. /PO Box 977 /Glastonbury /CT /06033
P /Arch /Archambault Ins. Assoc./ 136 Main St Ste 104 / Danielson
/CT/ 06239

With AgencyCode:

P_or_S /AgencyCode /SortName /AgencyName /MailAddress /MailCity /MailState
/MailZip

P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468
S /74394 /Affi /Affiliated-Stone Agency /PO Box 909 / Meriden /CT /06450
P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
Dave K.

:

<<I need to add the following:
AgencyCode
SortName
MailCity
MailState
MailZip

If those other fields have different data then I would expect the list to
grow.
--
KARL DEWEY
Build a little - Test a little

:

It is not in there because it does not work. I have no idea where to place
it. [agencyCode] appears in queries DaveK and DaveK_1.
--
Dave K.

:

<<every time I add [AgencyCode]
I do not see [AgencyCode] in your query.

--
KARL DEWEY
Build a little - Test a little

:

Karl, I have this query set up, but every time I add [AgencyCode] to the mix
it shows all the records instead of the specific ones that were requested.

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
ORDER BY DaveK.SortName;

--
Dave K.

:

Try these two queries --
DaveK_1 ---
SELECT DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK
GROUP BY DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
HAVING (((DaveK.PorS)="P"));

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyName, DaveK.MailAddress;

--
KARL DEWEY
Build a little - Test a little

:

I have a query that produces the results:

PorS AgencyName MailAddress
P Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Affiliated-Stone Agency PO Box 909
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
P Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S Ahrens, Fuller, St. John & Vincent 161 Mountain Rd
P Anderson-Krause, Inc. 238 E Main St
S Anderson-Krause, Inc. 238 E Main St
P Anderson-Meyer Ins., Inc. PO Box 977
S Anderson-Meyer Ins., Inc. PO Box 977
S Archambault Ins. Assoc. PO Box 153

As you can see I have Multiple [AgencyName] with the same address. The P =
Primary and S = Secondary for [PorS]. I need a query to only show the
Primary and only the secondary with a different address. The only exception
to this would be if the Primary and Secondary [Agencyname] are different.

Click to expand...

Click to expand...

KARL DEWEY · Mar 16, 2009

It works for me but I do not have a fully populated table of data, just this --
PorS AgencyCode SortName AgencyName MailAddress MailCity Mail State MailZip
P 1 Adam Miller Agency, LLC 227 Monroe Tpke Ste 2
S 1 Adam Miller Agency, LLC 227 Monroe Tpke Ste 2
S 2 Affiliated-Stone Agency PO Box 909
P 3 Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
P 4 Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S 4 Ahrens, Fuller, St. John & Vincent, Inc. 161 Mountain Rd
S 5 Ahrens, Fuller, St. John & Vincent 161 Mountain Rd
P 6 Anderson-Krause, Inc. 238 E Main St
S 6 Anderson-Krause, Inc. 238 E Main St
P 7 Anderson-Meyer Ins., Inc. PO Box 977
S 7 Anderson-Meyer Ins., Inc. PO Box 977
S 8 Archambault Ins. Assoc. PO Box 153
I noted that what you sent back had Q.[Mail State] AS Expr1, not as I sent
it.

--
KARL DEWEY
Build a little - Test a little

Dave K. said:
SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, Q.AgencyName, Q.MailAddress, Q.AgencyCode,
Q.SortName, Q.MailCity, Q.[Mail State] AS Expr1, Q.MailZip
FROM DaveK AS Q LEFT JOIN DaveK_1 ON (Q.AgencyName = DaveK_1.AgencyName) AND
(Q.MailAddress = DaveK_1.MailAddress)
WHERE ((((SELECT COUNT(*) FROM [DaveK] Q1
WHERE Q1.[AgencyName] = Q.[AgencyName]
AND Q1.[MailAddress] = Q.[MailAddress]
AND (Q1.AgencyCode & Q1.SortName & Q1.MailCity & Q1.[Mail State] &
Q1.MailZip) < (Q.AgencyCode & Q.SortName & Q.MailCity & Q.[Mail State] &
Q.MailZip))+1)<2))
ORDER BY Q.AgencyName, Q.MailAddress, (Q.AgencyCode & Q.SortName &
Q.MailCity & Q.[Mail State] & Q.MailZip) DESC;

--
Dave K.

KARL DEWEY said:

Post back what you have for DaveK_2.
--
KARL DEWEY
Build a little - Test a little

Dave K. said:

Yes I did. It just sits there and I have to do end task to get out of it.
--
Dave K.

:

Did you make sure to edit out any hard returns that copying and pasting may
have added to the post?
--
KARL DEWEY
Build a little - Test a little

:

DaveK_2 does not run
--
Dave K.

:

What are Q and Q1?
They are aliases of DaveK --- FROM DaveK AS Q

Does DaveK_2 run or is it the last query causing the freeze?
--
KARL DEWEY
Build a little - Test a little

:

I tried this and it freezes up my computer and returns no results.

What are Q and Q1?
--
Dave K.

:

Use the DaveK_1 query then these two --
DaveK_2
SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, Q.AgencyName, Q.MailAddress, Q.AgencyCode,
Q.SortName, Q.MailCity, Q.[Mail State], Q.MailZip
FROM DaveK AS Q LEFT JOIN DaveK_1 ON (Q.MailAddress = DaveK_1.MailAddress)
AND (Q.AgencyName = DaveK_1.AgencyName)
WHERE ((((SELECT COUNT(*) FROM [DaveK] Q1
WHERE Q1.[AgencyName] = Q.[AgencyName]
AND Q1.[MailAddress] = Q.[MailAddress]
AND (Q1.AgencyCode & Q1.SortName & Q1.MailCity & Q1.[Mail State] &
Q1.MailZip) < (Q.AgencyCode & Q.SortName & Q.MailCity & Q.[Mail State] &
Q.MailZip))+1)<2))
ORDER BY Q.AgencyName, Q.MailAddress, (Q.AgencyCode & Q.SortName &
Q.MailCity & Q.[Mail State] & Q.MailZip) DESC;

SELECT DaveK_2.P_or_S, DaveK_2.AgencyName, DaveK_2.MailAddress,
DaveK_2.AgencyCode, DaveK_2.SortName, DaveK_2.MailCity, DaveK_2.[Mail State],
DaveK_2.MailZip
FROM DaveK_2
GROUP BY DaveK_2.P_or_S, DaveK_2.AgencyName, DaveK_2.MailAddress,
DaveK_2.AgencyCode, DaveK_2.SortName, DaveK_2.MailCity, DaveK_2.[Mail State],
DaveK_2.MailZip;

--
KARL DEWEY
Build a little - Test a little

:

Karl I have come up with the following, but there are still a couple records
that I do not need.. Query as follows: Then example of data. and what I
want. Thanks

Query:

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyCode, DaveK.SortName, DaveK.AgencyName,
DaveK.MailAddress, DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.AgencyName = DaveK_1.AgencyName) AND
(DaveK.MailAddress = DaveK_1.MailAddress)
WHERE (((DaveK.AgencyCode)=IIf([DaveK_1].[AgencyName] Is Null Or
[DaveK_1].[MailAddress] Is Null,[DaveK]![AgencyCode],[DaveK_1]![AgencyCode])))
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyCode, DaveK.SortName, DaveK.AgencyName,
DaveK.MailAddress, DaveK.MailCity, DaveK.MailState, DaveK.MailZip
HAVING (((DaveK.MailState)=[What State]))
ORDER BY DaveK.SortName;

This returns the data I want except I have multiples of some data Example
Below:

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
S 70601 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
S 73300 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
S 70600 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
P 70120 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492
P 70140 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492

I only want to return just one of these it does not matter which.

This is what I want Below:

P_or_S AgencyCode SortName AgencyName MailAddress MailCityMail State MailZip
P 70120 Ferg Ferguson & McGuire, Inc. PO Box 846 Wallingford CT
06492
S 70601 GeraGerardi Ins. Services, Inc. 181 Main St Danielson CT
06239
--
Dave K.

:

P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
is not the same -- 73000 vs 70300 AND LLC / 227 vs LLC
227
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468

P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
70019 vs 70350
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
KARL DEWEY
Build a little - Test a little

:

When I add SortName, MailCity, MailState and MailZip the Query stays the
same. Only when I add the AgencyCode does it include all the records from
DaveK Query.

Without AgecnyCode:

P_or_S /SortName/ AgencyName /MailAddress /MailCity/ MailState/ MailZip

P / Adam / Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe
/CT /06468
S / Affi/ Affiliated-Stone Agency/ PO Box 909
/Meriden /CT/ 06450
P / Ahre / Ahrens, Fuller, St. John & Vincent, Inc./161 Mountain
Rd /Suffield/ CT/ 06078
P /Ande /Anderson-Krause, Inc./238 E Main St/ Branford /CT /06405
P /Ande /Anderson-Meyer Ins., Inc. /PO Box 977 /Glastonbury /CT /06033
P /Arch /Archambault Ins. Assoc./ 136 Main St Ste 104 / Danielson
/CT/ 06239

With AgencyCode:

P_or_S /AgencyCode /SortName /AgencyName /MailAddress /MailCity /MailState
/MailZip

P /73000 /Adam /Adam Miller Agency, LLC /227 Monroe Tpke Ste 2 /Monroe /CT
/06468
P /70300 /Adam /Adam Miller Agency, LLC 227 Monroe Tpke Ste 2 /Monroe /CT
/06468
S /74394 /Affi /Affiliated-Stone Agency /PO Box 909 / Meriden /CT /06450
P /70019 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078
P /70350 /Ahre /Ahrens, Fuller, St. John & Vincent, Inc. /161 Mountain Rd
/Suffield /CT /06078

--
Dave K.

:

<<I need to add the following:
AgencyCode
SortName
MailCity
MailState
MailZip

If those other fields have different data then I would expect the list to
grow.
--
KARL DEWEY
Build a little - Test a little

:

It is not in there because it does not work. I have no idea where to place
it. [agencyCode] appears in queries DaveK and DaveK_1.
--
Dave K.

:

<<every time I add [AgencyCode]
I do not see [AgencyCode] in your query.

--
KARL DEWEY
Build a little - Test a little

:

Karl, I have this query set up, but every time I add [AgencyCode] to the mix
it shows all the records instead of the specific ones that were requested.

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.SortName, DaveK.AgencyName, DaveK.MailAddress,
DaveK.MailCity, DaveK.MailState, DaveK.MailZip
ORDER BY DaveK.SortName;

--
Dave K.

:

Try these two queries --
DaveK_1 ---
SELECT DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK
GROUP BY DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress
HAVING (((DaveK.PorS)="P"));

SELECT IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P") AS P_or_S, DaveK.AgencyName, DaveK.MailAddress
FROM DaveK LEFT JOIN DaveK_1 ON (DaveK.MailAddress = DaveK_1.MailAddress)
AND (DaveK.AgencyName = DaveK_1.AgencyName)
GROUP BY IIf([DaveK_1].[AgencyName] Is Null Or [DaveK_1].[MailAddress] Is
Null,"S","P"), DaveK.AgencyName, DaveK.MailAddress;

--
KARL DEWEY
Build a little - Test a little

:

I have a query that produces the results:

PorS AgencyName MailAddress
P Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Adam Miller Agency, LLC 227 Monroe
Tpke Ste 2
S Affiliated-Stone Agency PO Box 909

Click to expand...

Click to expand...

Share and download your ebooks and journal	0	Nov 21, 2008
Concatenating fields	0	Jul 9, 2003
a.c.f. November 2003 stats	33	Dec 2, 2003
WTD: Nvidia Videocard for 4x AGP Slot	0	May 29, 2008

Access 2003 query question

Dave K.

KARL DEWEY

Dave K.

Dave K.

KARL DEWEY

Dave K.

KARL DEWEY

Dave K.

KARL DEWEY

Dave K.

Dave K.

KARL DEWEY

Dave K.

KARL DEWEY

Dave K.

KARL DEWEY

Dave K.

KARL DEWEY

Dave K.

KARL DEWEY

Ask a Question

Similar Threads