Comparing 2 date fields? (1 Viewer)

[bkanealy]

All,

I have two date fields in seperate tables. I'd like to get records from table 1 where the effective date field resides when it is less than the trouble ticket field in table 2. Is there a way in a query to accomplish this? Thanks in advance.

Brian

 

[dcx693]

Is there only one record in table 2? If so, then yes, you can join the two tables on the date fields and set the critiera for the date field in table 1 to be something like: < [table2].[date_field].

If you've got more than one record in table 2, then I'd have to ask how you'd know which record you wanted to refer to.

 

[bkanealy]

Yes, there would be more than one occurence in table 2. What I'd like to see is if the most recent date in table two is greater than the date in table 1.

We have a conversion date in table one and higher ups would like to know if there are trouble tickets for a customer in table two created after the conversion date. Thanks again.

Brian

 

[dcx693]

I think I'm understanding you, but can you post some sample data of what will be in the two tables and what you'd like returned from the query?

 

[bkanealy]

DCX,

Not a problem thanks for your assistance. I hope you can read this as I don't think it will come out like I typed it in. The fields I'm seperating by "--". Thanks again,

Brian

In table one;

Customer #-- Conversion Date
555444-- 20040301
555445-- 20040326
555446-- 20040101

In table two;

Customer #-- Ticket Opened
555444-- 20031231
555444-- 20020805
555445-- 20020105
555445-- 20040401
555446-- 20040201

The output would be
Customer #-- Conversion Date-- Ticket Opened
555445-- 20040326-- 20040401
555446-- 20040101-- 20040201

 

[dcx693]

OK, I got it. There are a couple of ways to do this. This, I think, is the easiest.

First, create an intermediate query (one that doesn't give you the final answer) based on table 2. You need to pull the customer number and the most recent date (using a totals query, group on Customer # and choose Max or Last for the Ticket Opened field). You'll then have this for table 2. We'll call it Table2MaxDate:
Customer #-- Ticket Opened
555444-- 20031231
555445-- 20040401
555446-- 20040201

Now, all you need to do is create a query where you join Table1 and Table2MaxDate on the customer #. Use an inner join of course, so you only pull off customers that exist in both tables. Add the customer number from either of the two tables to the QBE grid. Now add the Ticket Opened field from Table2MaxDate. For the criteria for the Ticket Opened field, use something like: >[table1].[conversion_date]