format to self-join text dates and date/time dates

J

J.Scott

I have two access tables, one has dates in yyyymmdd saved as text, the other
has dates saved as mm/dd/yyyy saved as date/time (these are both coming from
outside sources and cannot be given to me in like formats). I need to
self-join these fields and look for duplicates between the two files. each
file has around 500k-600k lines. how can i format my access tables (or just
one of them) to run my self-join. I'm new at access so forgive me if I
haven't explained myself properly. Thanks!!

Scott
 
D

Douglas J. Steele

If the data types are Dates, then they're not stored with any particular
format: they're stored as 8 byte floating point numbers (where the integer
portion represents the date as the number of days relative to 30 Dec, 1899,
and the decimal portion represents the time as a portion of a day). That
means you'd be able to join them without any problems.

If the data type isn't Date, try adding a Date field to each table, and use
an Update query to populate the new field.
 
A

Albert D.Kallal

I would take the table with the text date, and either import this into
another table, or just simply add a new column.

What you want here is to make sure that both tables have a true date field
and both are indexed. You can then do a relational join from the first table
to the 2nd table, and get reasonable performance.

Since the table sizes are quite large, then you might keep the files
separate. You could however import both tables from the two different files
into one mdb, and do the join that way. The decision here would depending on
how large the mdb files are.

So, just make a make table query into a new blank database, link to the old
table, and then update this query, and convert the text field into an actual
date field. (just create the table first, and then import). Or, simply make
a new column with the field as a true date type, and run a update query.

update tableDates set newDateField = oldTextDateFieldName

Once you got both columns as a true date type, and index on them, should be
able to get decent performance here..
 
Top