Access 2003 query question

[Dave K.]

Karl. I did a test in using a smaller table and it does work, but I think
that the problem is with the size of the table and it being linked to a SQL
table, plus the complexity to the query is making it think too much. Even
with the smaller table it sits for a time.
--
Dave K.

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

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.

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


:

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