Comparing Two Date Fields across Two Tables

  • Thread starter Thread starter Neil10365
  • Start date Start date
N

Neil10365

I have two tables, called Week1Data and Week2Data. Each table holds a
list of customer records and each table has two date fields within it,
called Start Date and Finish Date.

What I want to do is to display only the common customer records
between Week1Data and Week2Data and display only those records where
either date or both dates have changed between the two tables.

Does anyone have any ideas?

Thanks


Neil
 
I have two tables, called Week1Data and Week2Data. Each table holds a
list of customer records and each table has two date fields within it,
called Start Date and Finish Date.

What I want to do is to display only the common customer records
between Week1Data and Week2Data and display only those records where
either date or both dates have changed between the two tables.

Does anyone have any ideas?

Thanks


Neil
Click to expand...

To start with... your table design IS WRONG, pure and simple. Storing
data in tablenames is a VERY bad idea and will make your queries
vastly more complex; if you overwrite these tables every week, your
database will bloat rapidly and performance will suffer. I'd strongly
suggest having just one table of customer records with a date field,
and use Queries to extract the records for any particular week or
other time period.

That said... create a query joining Week1Data to Week2Data by the
unique CustomerID (which I presume you have, though you don't say).

Add Week2Data's [Start Date] and [Finish Date] fields to the query,
together with any other fields you want to see. On the Criteria line
under [Start Date] type

<> [Week1Data].[Start Date]

and similarly under Finish Date.

John W. Vinson[MVP]
 
Thanks John

I appreciate your comment on the table design, that gives me food for
thought.

The query produced an interesting result - it only returned results
when both dates had changed. It didn't return the results for those
accounts where only the Start Date differed or only the Finish Date
differed, with the other dates matching. How do I tweak it to include
the single date change scenario?

Regards


Neil
 
Thanks John

I appreciate your comment on the table design, that gives me food for
thought.

The query produced an interesting result - it only returned results
when both dates had changed. It didn't return the results for those
accounts where only the Start Date differed or only the Finish Date
differed, with the other dates matching. How do I tweak it to include
the single date change scenario?

Regards


Neil
 
Thanks John

I appreciate your comment on the table design, that gives me food for
thought.

The query produced an interesting result - it only returned results
when both dates had changed. It didn't return the results for those
accounts where only the Start Date differed or only the Finish Date
differed, with the other dates matching. How do I tweak it to include
the single date change scenario?
Click to expand...

Put the two criteria on *different* lines in the query grid so it uses
OR logic. Sorry - that was my oversight!

John W. Vinson[MVP]
 
John,

This works fine, except that if there is no date for finish date (i.e
the field is blank), the query won't pick up if only the start dates
differ. Do you know why that could be?

Thanks for your insight


Neil
 
John,

This works fine, except that if there is no date for finish date (i.e
the field is blank), the query won't pick up if only the start dates
differ. Do you know why that could be?

Thanks for your insight


Neil
 
John,

This works fine, except that if there is no date for finish date (i.e
the field is blank), the query won't pick up if only the start dates
differ. Do you know why that could be?

Thanks for your insight


Neil
Click to expand...

A NULL value doesn't match ANYTHING: any expression involving NULL
returns NULL.

So use a criterion of

<> [Table1].[EndDate] OR IS NULL


John W. Vinson[MVP]
 
John

This works perfectly, is elegant and proves that you are indeed a logic
genius!

I can't thank you enough.

Do you know any good books on query logic or query creation in general?

Thanks again!

Neil
 
Back
Top