Eliminate redundant data from Pick Box?

[jsbdiver]

Eliminate redundant data from Pick Box?
Is there a way to limit the results of a query to only one of each item, removing the redundancy?


Example: I have a DB with Addresses.
I have a form with a pick box to select the State of residence, that opens a report with all the people living only in state X. The problem is, it lists the states for each entry in the DB so there are multiple TXs, multiple NMs …. For selecting which state to produce the report for, I would like it to list only one TX, one NM….. Is there an easy way to do this without creating a new table?

Thanks
jsbdiver

[SoftwareMatters]

A group by on the state field should solve your problem.

Click on the Sorting and Grouping icon on the toolbar.
Now select your State field and turn group header on.

Do a search for sorting and grouping for more info in this.

[Jim Doherty]

Also Look at.... SELECT DISTINCT in SQL

[jsbdiver]

Thanks, I’ll see what I can do this weekend.

[jsbdiver]

The "SELECT DISTINCT" worked! Is there a good book that will help with the SQL commands? This was a simple fix, but I could not find much info in the Access help files.

Thanks

[Jim Doherty]

The "SELECT DISTINCT" worked! Is there a good book that will help with the SQL commands? This was a simple fix, but I could not find much info in the Access help files.

Thanks

Playing around with the query wizard and looking look at its SQL generated in the SQL window will give you a start. Beyond that and to be frank there are tons of references on the internet on SQL and slowly but surely you will get the hang of things like SELECT, SELECT INTO, INSERT INTO, TRANSFORM and then correlated sub queries etc etc.

The SELECT DISTINCT and SELECT DISTINCTROW predicates can easily be generated in Access when you look at the properties dialog of your query and pick 'unique records' or 'unique values' this syntax is generated for you.

Any good book on Access generally has a good sampling of different query syntax including DDL derivates "CREATE TABLE", "ALTER TABLE", "ADD COLUMN" and so on