CODEWITHSUNDEEP
ms-accessoffice365
jpl458
jpl458

Reputation: 615

How to join two tables by dates?

I have two tables imported into Access from Excel workbooks.

They are narrow tables:

INRMaster:      MastDate       Date/Time (Short Date)

CIInput:        CIDate         Date/Time (Short Date)
                INRTestResult  Number
                Dose           Number
                OutOfRange     Short Text

CIInput table was downloaded and the date was date/time with date and time of test. I reformatted that date field to mm:dd:yyyy to match the table I created, INRMaster.

There is no primary key on either table. I tried the join with primary keys of date in both tables and it returned nothing as well. Creating the query using the QBE Grid.

The generated SQL is as follows:

SELECT INRMaster.MastDate, CIINput.[INR test result], CIINput.Dose, CIINput.OutofRange
FROM INRMaster INNER JOIN CIINput ON INRMaster.MastDate = CIINput.CIDate

Office 365 Access, Windows 10.

Upvotes: 0

Views: 177

Answers (1)

June7
June7

Reputation: 21370

Setting Format property does not change data. Unless you actually modify saved values, time part is still there and since it is unlikely values will agree to the second, a join will fail. Don't apply formatting in table - view full saved value.

Extract date part with expression in query. If both fields were saved with date and time components, then extract date portion from both. Consider:

SELECT INRMaster.MastDate, CIINput.[INR test result], CIINput.Dose, CIINput.OutofRange
FROM INRMaster INNER JOIN CIINput ON Int(INRMaster.MastDate) = Int(CIINput.CIDate);

DO NOT OPEN QUERY IN DESIGN VIEW. Query Designer cannot resolve this join. Would have to join nested subqueries to enable Design View.

Upvotes: 2

Related Questions