table joins in Access 2003 on date-time field

Question

I need to join my data table to a server calendar file (has set fiscal dates, periods, etc.). I don't get any results and believe it is because my table has date/time like "11/23/2009 11:30:34AM" and the calendar file only has date/time like "11/23/2009", so the fields are not identical.

current SQL reads like: From tbl_data Inner Join tbl_calendar on tbl_data.TRXdate = tbl_calendar.TRXdate

any ideas?

Answer 1

Another possibility:

FROM tbl_data
INNER JOIN tbl_calendar
ON tbl_calendar.TRXdate = CDate(Format(tbl_data.TRXdate,"yyyy/mm/dd"))

This cannot be edited in the query design window except in SQL view. The calendar is on the left of the equals sign to take advantage of indexes.

Answer 2

Consider using the DateValue function to extract only the "date portion" from your date/time value:

? DateValue(#11/23/2009 11:30:34AM#)
11/23/2009

However, DateValue actually returns a Date/Time value for midnight of the date in question:

? Format(DateValue(#11/23/2009 11:30:34AM#), "mm/dd/yyyy hh:nn:ss")
11/23/2009 00:00:00

So see whether a join expression like this gets what you want:

FROM tbl_data
INNER JOIN tbl_calendar
ON tbl_calendar.TRXdate = DateValue(tbl_data.TRXdate)

Answer 3

Using functions in joins will not use indexes. You could do this, instead (assuming tbl_data is the table with time parts in its data):

  SELECT *
  FROM tbl_data, tbl_calendar
  WHERE tbl_data.TRXdate >= tbl_calendar.TRXdate
    AND tbl_data.TRXdate < tbl_calendar.TRXdate + 1

This will use the indexes on both sides (though not necessarily for both criteria).

The reason not to use the BETWEEN/AND operator is that many database engines treat the second argument as inclusive, so it would get you two days instead of just the one.

More speculative suggestions:

1. ADO allows the creating of virtual indexes on recordsets, so depending on what you're trying to do here, you could perhaps speed it up by creating a virtual index on a calculated field, Int(tbl_calendar.TRXdate). I've never done this, just know that it's possible.

2. some database engines allow the creation of virtual indexes on views. In SQL Server this is called an indexed view, and it would allow you to create a view with the Int(tbl_calendar.TRXdate) expression indexed.

3. temp tables on the server side might also be a solution, but the way indexed views work rather obviates the need for that, since it saves the indexed view as a table.

4. depending on the other database, the indexes may or may not be used. How efficiently they are going to be used in a heterogeneous join is going to depend on the db engines involved, and which one you use to execute the SQL SELECT. If, for instance, the server side is SQL Server, it could be that you'd get better performance by running it from SQL Server with the Jet/ACE data file as a linked server. My supposition is that there are optimizations for exactly this kind of situation that would probably make it more efficient done from SQL Server than from within Jet, but you'd have to test to be sure.

5. another option is to populate a temp table in your Access database that has two columns, the PK from the server-side table and the Int(tbl_calendar.TRXdate) value calculated from the server data. If you indexed that, you'd get pretty good performance in the select of the Jet/ACE data. But if you need to return the server-side data, you'd probably not get results as good, but it might be better than any of the alternatives above.