Filtering for Unique Records in an Access Database

[Guest]

How can I filter a column for unique records? For several of the fields,
there is a lot of repeating data (i.e. names) and I need to pull a list of
only the unique data. I know how to do this in excel, but the database is to
large to export into several spreadsheets and ensure im getting
everything...plus it would take forever.

thanks in advance.

 

[Allen Browne]

In query design view, open the Properties box (View menu), and set the
Unique Values property to Yes.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"(e-mail address removed)" <[email protected]@discussions.microsoft.com>
wrote in message
news:[email protected]...

 

[Guest]

Thanks Allen... But everytime I do that the database closes itself. When I
go to open the table in design view it says that this is "a linked table with
some tables that can't be altered'. Is this affecting my ability to filter
for unique? I didn't link this table to anything else...I simply imported
several spreadsheets into one database table.

Thanks.

 

[Allen Browne]

In the Database window, on the Tables tab, does the icon for this table have
a little arrow next to it? If so, you have not imported the table, but
linked it. That would be why you get the message that you can't change the
way the table is designed, because the data is defined in another program,
not Access. If you intended to import it:
File | Get External | Import.

If you are saying that Access closes with a message along the lines of "...
shut down by Windows... Sorry for the inconvenience", then somehing is wrong
with it. Try this:
1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact/Repair

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access (holding down the Shift key if you have any startup code),
and compact again.

If it still crashes, try importing the data instead of linking it. You
should be able to open the Properties box in query design. If it crashes
only when you run the query (not when you try to set the query property),
switch the query to SQL View (View menu), and paste the SQL statement here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"(e-mail address removed)" <[email protected]>
wrote in message

 

[Guest]

When I click on the field I want to filter for unique, theres no Unique
Values line in the design veiw....nor when I open the properties box in
design view.

Thanks for your assistance,...I don't understand why this is so difficult.

 

[Allen Browne]

In query design view, it sounds like you are looking at the properties of a
field instead of the Properties of the query.

Click the grey area in the upper pane of table design, and see if the title
bar of the Properties sheet changes to "Query Properties."

If you still can't find it, switch the query to SQL View (View menu), and
add the word DISTINCT after SELECT, i.e.:
SELECT DISTINCT ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"(e-mail address removed)" <[email protected]>
wrote in message