If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Access 2003 query question
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. "KARL DEWEY" wrote: 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." wrote: 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" wrote: Post back what you have for DaveK_2. -- KARL DEWEY Build a little - Test a little "Dave K." wrote: Yes I did. It just sits there and I have to do end task to get out of it. -- Dave K. "KARL DEWEY" wrote: 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." wrote: DaveK_2 does not run -- Dave K. "KARL DEWEY" wrote: 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." wrote: I tried this and it freezes up my computer and returns no results. What are Q and Q1? -- Dave K. "KARL DEWEY" wrote: 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." wrote: 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" wrote: 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." wrote: 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" wrote: 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." wrote: 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" wrote: every time I add [AgencyCode] I do not see [AgencyCode] in your query. -- KARL DEWEY Build a little - Test a little "Dave K." wrote: 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" wrote: Try these two queries -- DaveK_1 --- SELECT DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress FROM DaveK GROUP BY DaveK.PorS, DaveK.AgencyName, DaveK.MailAddress |
Thread Tools | |
Display Modes | |
|
|