Query Design Question

[Guest]

I am trying to generate a query or report that gives me a list of States, the
cities therein, and the zip codes within each city, that we serve.

I built a simple query putting each of these fields in ascending sort, but I
am getting duplications where there are multiple records for each city and
zip code.

I only want one record reported for each. That is, I expected to get more
than one city for some states, and more than one zip code for some cities.
What I don't want, is that if there are 25 records listed for the same zip
code within the same city and state, is to see all 25 listed. If there are
10 zip codes for Anytown, GP, and say that within the first zip code there
are 3 records, the second zip code contains four records, and the third zip
code contains 3 records, I just want to see each City, State and Zip Code
ONCE (giving me three records for the query or report, not 10). Am I stating
this clearly?

Thanks in advance.

 

[Guest]

Hi
First of all, if your query has additional fields in it (perhaps street &
house number) that make each of your records unique then you'd have to remove
those fields.

Go to the SQL design view for your query and put in the qualifier "DISTINCT"
in order to get distinct records. e.g.

Select distinct YourTable.Zipcode, YourTable.City, YourTable.State from
YourTable

Look up DISTINCT in the help library for more details, but that should do it.

GL

 

[Guest]

hi,
Post the query's SQL.

 

[Guest]

These are the only 3 fields I need for this query; the main table that the
data is coming from is where the additional fields are that makes each record
unique.

Thank you for the quick reply. I don't know what SQL means or is; can you
please re-explain in laypersons terms? I did figure out how to get into the
SQL Design View, and this is what I found (without the asterisks; I put them
there to offset the text to make it more readable for others). Where do I
put the word DISTINCT? The name of the main table is Students. Do I put the
word DISTINCT between SELECT & Students.ZipCode? Do I add it in all 3 lines?

**SELECT Students.ZipCode, Students.City, Students.Region, Students.State
FROM Students
ORDER BY Students.ZipCode, Students.City;**

Before this, I didn't even know what a relational database was, let alone
all these other things. I do know enough to guess at the potential for what
we might be able to do with Access, and that I do not know how to do all
these things. I have offered to my boss to go and learn it, as I'm always
open to learning, but we are a non-profit, and the bottom line is always what
it comes down to (no pun intended). (They won't even buy me a good resource
like "The Access Bible", tsk). So I thank all of the MVP's and anyone else
who can give me an answer, from the bottom of my heart! You are my only hope
<<grin/wink>>!

In Peace, Max

 

[Guest]

Just put the DISTINCT in right after the SELECT statement as in:

SELECT DISTINCT Students.ZipCode, Students.City, Students.Region,
Students.State FROM Students ORDER BY Students.ZipCode, Students.City;

That should do it and I'm happy to be able to help (I think I ask more
questions than I answer, so I know how you feel.)

 

[Guest]

Forgive me if this comes up twice - I got an error message the last time I
posted it.

SQL is language that is used to query a table (Structured Query Language - I
think); the design view that Access provides is merely a user friendly method
of constructing a SQL string. However, if you wish to do anything more
complicated than basic SQL, you have to actually do it writing the SQL
yourself.

As for the DISTINCT statement, put it in right after the SELECT statement,
as in:

SELECT DISTINCT Students.ZipCode, Students.City, Students.Region,
Students.State FROM Students
ORDER BY Students.ZipCode, Students.City;

Hope that helps, and glad I could help (I think I ask more questions than I
answer, so I know how you feel).

 

[Guest]

Wow, that worked! <<grin>>

What I'm actually seeing, is that sometimes I do get multi listings per
unique city/state/zip, but that is because of data entry inconsistencies,
such as Ft. Henry/Fort Henry/FH, etc. Which is kind of good, because then I
can see what to correct.

My next question will deal with an easy way to actually make all the
corrections, lol.

Thank you all!