Ok, the recordcount is 17196 and I dont know how to figure the sum of all of those (and of course i'm not doing it by hand).
For the second querry I'm doing this:
How to delete duplicate records from a table in Access 2000
View products that this article applies to.
Article ID : 209183
Last Review : July 29, 2004
Revision : 1.0
This article was previously published under Q209183
For a Microsoft Access 97 version of this article, see 109329 (
http://support.microsoft.com/kb/109329/).
Novice: Requires knowledge of the user interface on single-user computers.
SUMMARY
This article describes how you can remove duplicate records from a table by using primary keys. By using this method, you can check for duplicate values in up to 10 fields in the table.
MORE INFORMATION
To remove duplicate records from a table, follow these steps:
1. Make a copy of the structure of the table from which you want to remove the duplicate records.
To copy a table:
a. Select the table in the Database window
b. On the Edit menu, click Copy.
c. On the Edit menu, click Paste.
d. Enter a name for the new table.
e. Select Structure Only
f. Click OK.
2. Open the new table in Design view.
3. Select the field(s) that contain the duplicate values.
4. To make your selection the primary key for the table, click the Primary Key button on the toolbar.
5. Save and close the table.
6. Create an append query based on the original table containing duplicates.
7. In the query Design View, on the Query menu, click Append Query.
8. In the Append dialog box, click the name of the new table from the Table Name list, and then click OK.
9. Include all the fields from the original table by dragging the asterick (*) to the query design grid.
10. On the Query menu, click Run.
11. Click Yes in the dialog box advising you that you are about to append records.
12. Because the Primary Key field(s) in the new table will not accept duplicate values, the following error message will be displayed:
Microsoft Access can't append all the records in the append query.
Microsoft Access set 0 field(s) to Null due to a type conversion failure, and it didn't add <number> record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.
Do you want to run the action query anyway?
To ignore the error(s) and run the query, click Yes. For an explanation of the causes of the violations, click Help.
13. Click Yes.
14. View the contents of the new table. When you're sure the new table has the correct unique records, you can delete the original table, and then rename the new table using the name of the original table.
REFERENCES
For more information about deleting duplicate records, click Microsoft Access Help on the Help menu, type Automatically delete duplicate records from a table. in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
This is the text from the website that I linked to in a previous post. Notice that in the new table I coppied only the structure from the original table and made the "Address" field primary (therefore no duplicates, correct?) so when I wrote all of the records from the first table to the new table it takes out the duplicates (correct again?) or does it take ALL records that have a duplicate. I.E. if two records had the same address it would take them both out. This would answer why I only get about 600 records in the new table.
So how do i delete duplicates without deleting ALL of the duplicated records?
Thanks for all your help!!
Will
