Fordraiders
asked on
Query to determine date time difference and view only the changed data
Access 2003
2 - Tables Linked to sql server
Both tables have the same field names, and , datatype
Table1
material_no - text
update_way_date -- date/time
update_fgom_date -- date/time
Table2
material_no - text
update_way_date -- date/time
update_fgom_date -- date/time
What I need I need to look at the date/time change between Table 2 and Table1
Let me see the records that have a later date then in Table2
Hope this makes sense ?
Thanks
fordraiders
2 - Tables Linked to sql server
Both tables have the same field names, and , datatype
Table1
material_no - text
update_way_date -- date/time
update_fgom_date -- date/time
Table2
material_no - text
update_way_date -- date/time
update_fgom_date -- date/time
What I need I need to look at the date/time change between Table 2 and Table1
Let me see the records that have a later date then in Table2
Hope this makes sense ?
Thanks
fordraiders
Can you show us a few examples from the two tables and what you want returned?
"Hope this makes sense" -> Nope
Why do you have two tables with identical structure? Is Table2 and archive of Table1?
Is [material_no] unique in each table?
Do you want to identify only those records where there are [material_no] matches, but where one of the Table1 fields is more recent than the matching record in Table2, or do you also want to identify those records in Table1 which don't have a matching [material_no] value in Table2.
By "let me see", do you mean that you just want to display these results in a query?
Why do you have two tables with identical structure? Is Table2 and archive of Table1?
Is [material_no] unique in each table?
Do you want to identify only those records where there are [material_no] matches, but where one of the Table1 fields is more recent than the matching record in Table2, or do you also want to identify those records in Table1 which don't have a matching [material_no] value in Table2.
By "let me see", do you mean that you just want to display these results in a query?
ASKER
one is a backup staging table...
Is there ONLY one record for each material_no in each table? yes
Is there ALWAYS one record for each material_no in each table? yes
Is there ONLY one record for each material_no in each table? yes
Is there ALWAYS one record for each material_no in each table? yes
ASKER
table1
sa2234 07/22/2011 3:45:23: pm
sa2235 07/22/2011 3:46:22: pm
sa2236 08/22/2011 3:47:10: pm
table2
sa2234 07/22/2011 3:45:23: pm
sa2235 08/10/2011 13:23:22: pm
sa2236 09/03/2011 1:47:10: pm
I should only see
sa2235 08/10/2011 13:23:22: pm
sa2236 09/03/2011 1:47:10: pm
Thanks
fordraiders
If both dates in table1 must be later than the corresponding date in table 2...
Select t1.material_no , t1.update_way_date , t1.update_fgom_date , t2.update_way_date , t2.update_fgom_date
From table1 as t1 inner join table2 as t2
on t1.material_no = t2.material_no
where t1.update_way_date>t2.upda te_way_dat e and t1.update_fgom_date>t2.upd ate_fgom_d ate
if just one of the dates in table1 must be later than the corresponding date in table 2
Select t1.material_no , t1.update_way_date , t1.update_fgom_date , t2.update_way_date , t2.update_fgom_date
From table1 as t1 inner join table2 as t2
on t1.material_no = t2.material_no
where t1.update_way_date>t2.upda te_way_dat e or t1.update_fgom_date>t2.upd ate_fgom_d ate
Select t1.material_no , t1.update_way_date , t1.update_fgom_date , t2.update_way_date , t2.update_fgom_date
From table1 as t1 inner join table2 as t2
on t1.material_no = t2.material_no
where t1.update_way_date>t2.upda
if just one of the dates in table1 must be later than the corresponding date in table 2
Select t1.material_no , t1.update_way_date , t1.update_fgom_date , t2.update_way_date , t2.update_fgom_date
From table1 as t1 inner join table2 as t2
on t1.material_no = t2.material_no
where t1.update_way_date>t2.upda
<Let me see the records that have a later date then in Table2> I take it from this statement that you want to see the records in Table1 that have a later date than in Table2 - yet you show the Table2 records that are later than Table1??
Oops! - yet you show the Table2 records that are earlier than Table1??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry better example here:
table1 update_way_date -- date/time update_fgom_date -- date/time
sa2234 07/22/2011 3:45:23: pm 07/23/2011 2:01:23: pm
sa2235 08/10/2011 13:23:22: pm 08/10/2011 11:01:27: pm
sa2236 09/03/2011 1:47:10: pm 09/03/2011 1:47:10: pm
table2 update_way_date -- date/time update_fgom_date -- date/time
sa2234 07/22/2011 3:45:23: pm 07/23/2011 2:01:23: pm
sa2235 08/10/2011 13:23:22: pm 08/15/2011 01:03:10: pm
sa2236 09/06/2011 2:49:04: pm 09/03/2011 1:47:10: pm
I should only see
table2 update_way_date -- date/time update_fgom_date -- date/time
sa2235 08/10/2011 13:23:22: pm 08/15/2011 01:03:10: pm
sa2236 09/06/2011 2:49:04: pm 09/03/2011 1:47:10: pm
table1 update_way_date -- date/time update_fgom_date -- date/time
sa2234 07/22/2011 3:45:23: pm 07/23/2011 2:01:23: pm
sa2235 08/10/2011 13:23:22: pm 08/10/2011 11:01:27: pm
sa2236 09/03/2011 1:47:10: pm 09/03/2011 1:47:10: pm
table2 update_way_date -- date/time update_fgom_date -- date/time
sa2234 07/22/2011 3:45:23: pm 07/23/2011 2:01:23: pm
sa2235 08/10/2011 13:23:22: pm 08/15/2011 01:03:10: pm
sa2236 09/06/2011 2:49:04: pm 09/03/2011 1:47:10: pm
I should only see
table2 update_way_date -- date/time update_fgom_date -- date/time
sa2235 08/10/2011 13:23:22: pm 08/15/2011 01:03:10: pm
sa2236 09/06/2011 2:49:04: pm 09/03/2011 1:47:10: pm
ASKER
Thanks
Is there ALWAYS one record for each material_no in each table?