Jamie, I truly appreciate everything that you have mentioned and the
fact that you have taken the time to respond. Unfortunately, this
isn't really what I wanted. I believe what you are suggesting is more
complicated than what I require.

Here is the process again.

I have two files Invoices and Tracking. (Both of these are uploaded
into Access once all duplicates are removed)

Tracking
CREATEDATE INVOICENUMBER OLDCODE NEWCODE
09012004 6:00 am 0001 O P
09012004 6:03 am 0001 P CH
09012004 6:05 am 0001 CH C
09062004 12:12 pm 0002 O P
09062004 1:45 pm 0002 P CH
09172004 9:00 am 0003 0 P
09222004 11:15 am 0003 P CH
09272004 2:22 pm 0003 CH P

I cycle through and delete the duplicates by invoice number and come
up with this (I have a little delete duplicates code). This is what I
end up with.

CREATEDATE INVOICENUMBER OLDCODE NEWCODE
09012004 6:05 am 0001 CH C
09062004 1:45 pm 0002 P CH
09272004 2:22 pm 0003 CH P


Invoices (this does not have a date, I have requested but again issues
with having format changed) Sometimes there are duplicates and
sometimes there isn't. I'm not sure why this spreadsheet gets a
duplicate but this is what it would look like. Sometimes there are
duplicates, sometimes there isn't.

INVOICENUMBER CODE
0001 P
0001 CH
0001 C
0002 CH
0003 P
0003 CH


What I would like ......

I would like to delete the duplicates in the invoice spreadsheet so
that the code in the Invoices table matches the New Code in the
Tracking table.

The results I would like

INVOICENUMBER CODE
0001 C
0002 CH
0003 P


I am deleting duplicates from the Invoices spreadsheet (with the same
code that I use to delete duplicates from the tracking table), then I
compare the invoices that were duplicated with the NewCode from the
Tracking table to make sure they are the same. I don't want a new
spreadsheet, I don't want to query anything. I want to DELETE
duplicates based on the match between the code in the invoices table
and the newcode in the tracking table.

Can it be done, if not just say so and I will continue doing what I am
doing.

Thanks again for all your time. I do appreciate what it takes to
recreate everything.

yt, Lynn






(Jamie Collins) wrote in message . com...
(Lynn A.) wrote ...

Thank you Jamie, if changing the original SQL query was an option,
your suggestions would be great.

You can use sql to maniplulate the Excel data.

Here is more detail.

You again omitted test data and expected results: if you don't supply
the former, someone has to put effort into creating test data which
may not logically fit your schema; if you don't supply the latter, the
person replying can't be sure their answer is correct because the
results aren't verifiable. So again I'm guessing here...

First, let's create a workbook from which to run some code: in the
Excel app, create a new blank workbook and save as e.g. C:\test.xls.

Open MS Query by choosing: Data, Import External Data, New Database
Query, Excel Files, OK, navigate to the open workbook (e.g.
C:\test.xls), OK, close the Add Tables dialog.

In MS Query, open the SQL window by hitting the SQL button (or choose:
View, SQL).

Now to create a workbook e.g. C:\TempDB.xls to hold the test data and
results. Paste the following text into the SQL window:

CREATE TABLE
[Excel 8.0;HDR=YES;Database=C:\TempDB.xls;].Tracking
(
CREATEDATE DATETIME,
INVOICENUMBER VARCHAR(255),
OLDCODE VARCHAR(255),
NEWCODE VARCHAR(255)
)
;

Hit OK and the SQL is executed. You should get a success message. Open
the SQL window again, delete the text and paste in the following:

CREATE TABLE
[Excel 8.0;HDR=YES;Database=C:\TempDB.xls;].Invoicing
(
INVOICENUMBER VARCHAR(255),
CODE VARCHAR(255)
)
;

Hit OK and the second sheet should now be created.

Now for some test data. For each of the following lines (separated by
a semicolon), paste into the SQL window and hit OK to execute:

INSERT INTO [Excel 8.0;HDR=YES;Database=C:\TempDB.xls;].[Tracking$]
(CREATEDATE, INVOICENUMBER, OLDCODE, NEWCODE) VALUES ('2004-09-01',
'ABC123', 'AA', 'BB');
INSERT INTO [Excel 8.0;HDR=YES;Database=C:\TempDB.xls;].[Tracking$]
(CREATEDATE, INVOICENUMBER, OLDCODE, NEWCODE) VALUES ('2004-09-02',
'ABC123', 'BB', 'CC');
INSERT INTO [Excel 8.0;HDR=YES;Database=C:\TempDB.xls;].[Tracking$]
(CREATEDATE, INVOICENUMBER, OLDCODE, NEWCODE) VALUES ('2004-09-03',
'ABC123', 'CC', 'DD');
INSERT INTO [Excel 8.0;HDR=YES;Database=C:\TempDB.xls;].[Tracking$]
(CREATEDATE, INVOICENUMBER, OLDCODE, NEWCODE) VALUES ('2004-09-01',
'XYZ987', 'AB', 'CD');
INSERT INTO [Excel 8.0;HDR=YES;Database=C:\TempDB.xls;].[Tracking$]
(CREATEDATE, INVOICENUMBER, OLDCODE, NEWCODE) VALUES ('2004-09-02',
'XYZ987', 'CD', 'EF');
INSERT INTO [Excel 8.0;HDR=YES;Database=C:\TempDB.xls;].[Tracking$]
(CREATEDATE, INVOICENUMBER, OLDCODE, NEWCODE) VALUES ('2004-09-01',
'JMC555', 'JJ', 'MC');
INSERT INTO [Excel 8.0;HDR=YES;Database=C:\TempDB.xls;].[Invoicing$]
(INVOICENUMBER, CODE) VALUES ('ABC123', 'AA');
INSERT INTO [Excel 8.0;HDR=YES;Database=C:\TempDB.xls;].[Invoicing$]
(INVOICENUMBER, CODE) VALUES ('ABC123', 'BB');
INSERT INTO [Excel 8.0;HDR=YES;Database=C:\TempDB.xls;].[Invoicing$]
(INVOICENUMBER, CODE) VALUES ('ABC123', 'CC');
INSERT INTO [Excel 8.0;HDR=YES;Database=C:\TempDB.xls;].[Invoicing$]
(INVOICENUMBER, CODE) VALUES ('ABC123', 'DD');
INSERT INTO [Excel 8.0;HDR=YES;Database=C:\TempDB.xls;].[Invoicing$]
(INVOICENUMBER, CODE) VALUES ('XYZ987', 'CD');
INSERT INTO [Excel 8.0;HDR=YES;Database=C:\TempDB.xls;].[Invoicing$]
(INVOICENUMBER, CODE) VALUES ('XYZ987', 'EF');
INSERT INTO [Excel 8.0;HDR=YES;Database=C:\TempDB.xls;].[Invoicing$]
(INVOICENUMBER, CODE) VALUES ('JMC555', 'MC');

Now for the results, as I understand them to be.

I delete any duplicate invoice numbers based on the
most recent date so that this only contains one row per invoice
number.

So, SELECT only the most recent rows, using a subquery to find the
maximum date for each INVOICENUMBER, into a new sheet i.e. by
executing:

SELECT
T1.CREATEDATE, T1.INVOICENUMBER,
T1.OLDCODE, T1.NEWCODE
INTO
[Excel 8.0;HDR=YES;Database=C:\TempDB.xls;].NewTracking
FROM
[Excel 8.0;HDR=YES;Database=C:\TempDB.xls;].[Tracking$] T1
WHERE
T1.CREATEDATE =
(
SELECT
MAX(T2.CREATEDATE)
FROM
[Excel 8.0;HDR=YES;Database=C:\TempDB.xls;].[Tracking$] T2
WHERE
T1.INVOICENUMBER = T2.INVOICENUMBER
)
;

I manually loop through each
invoice number that had duplicates. I manually delete the line(s)
that don't match the "newcode" from the Tracking spreadsheet is the
same as the "code" from the Invoicing spreadsheet.

I not sure what you want here but suspect you want to JOIN to the
'NewTracking' table using INVOICENUMBER and CODE/NEWCODE i.e.

SELECT
T1.INVOICENUMBER,
T1.CODE
INTO
[Excel 8.0;HDR=YES;Database=C:\TempDB.xls;].NewInvoicing
FROM
[Excel 8.0;HDR=YES;Database=C:\TempDB.xls;].[Invoicing$] T1
INNER JOIN
[Excel 8.0;HDR=YES;Database=C:\TempDB.xls;].[NewTracking$] T2
ON
T1.INVOICENUMBER = T2.INVOICENUMBER
AND T1.CODE = T2.NEWCODE
;

Take a look at the results e.g. by quitting MS Query and opening
C:\TempDB.xls. If the 'new' tables are not you expected results, post
back with some test data and what you expect to see.

Jamie.

--