Query Works until add criteria. after which it returns same record a thousand times.

[karl1971]

This must be simple to someone. When I run my query.. say.. Name, State, Zip.. it returns fine. But when I try to just see LA as state, or APPROVED as status.. it returns the first event event.. and populates a huge table with the same record. Just the addition of ="LA" glitches it.. I would expect it to just pull the records where the state is LA. What am I doing wrong? BTW I am running 2007 Guess I am in the wrong Forum.

[ssanfu]

What is the SQL of the query?

[karl1971]

SELECT qryZipCodeCat.CARR_ID, qryZipCodeCat.STATE, qryZipCodeCat.zipcode, PIDFSR1_US_ZIPS.LAT, PIDFSR1_US_ZIPS.LON
FROM qryZipCodeCat INNER JOIN PIDFSR1_US_ZIPS ON qryZipCodeCat.zipcode=PIDFSR1_US_ZIPS.ZIP;

I had to concatenate the zip (which was split into two fields) Which I saved as qryZipCodeCat. Then I ran that against my Zip code table with all city zipcodes/lat longs. It works for the first instance and repeats it ad infinitum I suppose

[ssanfu]

I see nothing wrong with the SQL.

When you tried to filter by State, is this what the SQL looked like?

SELECT qryZipCodeCat.CARR_ID, qryZipCodeCat.STATE, qryZipCodeCat.zipcode, PIDFSR1_US_ZIPS.LAT, PIDFSR1_US_ZIPS.LON
FROM qryZipCodeCat INNER JOIN PIDFSR1_US_ZIPS ON qryZipCodeCat.zipcode=PIDFSR1_US_ZIPS.ZIP
WHERE qryZipCodeCat.STATE = "LA";

What is the SQL for qryZipCodeCat?

[karl1971]

SELECT ICQ_CARRIERS.CARR_ID, [pst_1_c] & "" & [pst_2_c] AS zipcode, ICQ_CARRIERS.STATE
FROM PIDFSR1_US_ZIPS, ICQ_CARRIERS
WHERE (((ICQ_CARRIERS.STATE)="LA"));


Here is the query that I would Filter by state.. I just did it, and got one record over and over and over.. not useful really

CARR_ID	zipcode	STATE
AANC	70737	LA

[ssanfu]

Try adding DISTINCT :

SELECT DISTINCT ICQ_CARRIERS.CARR_ID, [pst_1_c] & "" & [pst_2_c] AS zipcode, ICQ_CARRIERS.STATE
FROM PIDFSR1_US_ZIPS, ICQ_CARRIERS
WHERE (((ICQ_CARRIERS.STATE)="LA"));

The empty string is not needed between [pst_1_c] and [pst_2_c].

SELECT DISTINCT ICQ_CARRIERS.CARR_ID, [pst_1_c] & [pst_2_c] AS zipcode, ICQ_CARRIERS.STATE
FROM PIDFSR1_US_ZIPS, ICQ_CARRIERS
WHERE (((ICQ_CARRIERS.STATE)="LA"));

Why is there [pst_1_c] and [pst_2_c]? What does the data look like? (Just curoius)

[karl1971]

for some reason (I am trying to find out why) this company has broken the zip code into two fields. Which I can't use. It looks like: DesMoines 503 09
which is the city, first 3 of the zip, then last 2 of zip in three separate fields. There must be a reason. Cool, I will try that distinct trick.. I am still learning

[karl1971]

well, I added the distinct clause.. and ran query.. and it appears to be crunching data.. which is an improvement. Unless it never stops.lol. let me say That I do appreciate your help in this matter.

[karl1971]

IT WORKED!!!!! Thank you x 100

[ssanfu]

You're welcome.

Ready to mark this solved?