Solved

Query to determine date time difference and view only the changed data

Posted on 2011-09-13
11
223 Views
Last Modified: 2012-06-27
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


0
Comment
Question by:fordraiders
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 36530645
Is there ONLY one record for each material_no in each table?
Is there ALWAYS one record for each material_no in each table?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 36530667
Can you show us a few examples from the two tables and what you want returned?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36530690
"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?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 3

Author Comment

by:fordraiders
ID: 36530762
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
0
 
LVL 3

Author Comment

by:fordraiders
ID: 36530797

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

0
 
LVL 77

Expert Comment

by:peter57r
ID: 36530823
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.update_way_date  and t1.update_fgom_date>t2.update_fgom_date
 

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.update_way_date  or t1.update_fgom_date>t2.update_fgom_date
0
 
LVL 44

Expert Comment

by:GRayL
ID: 36530826
<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??
0
 
LVL 44

Expert Comment

by:GRayL
ID: 36530832
Oops!  - yet you show the Table2 records that are earlier than Table1??
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 36530837
Not sure which date/time field you are displaying, but something like (I assume that was supposed to be [update_from_date], not [update_fgom_date]):

SELECT Table1.[Material_no], Table1.update_way_date, TAble1.update_from_date
FROM Table1 LEFT JOIN Table2 ON Table.material_no = Table2.material_no
WHERE Table1.Update_way_date <> Table2.Update_way_date
OR Table1.Update_from_date <> Table2.Update_from_date

0
 
LVL 3

Author Comment

by:fordraiders
ID: 36530841
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

0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 36531682
Thanks
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now