Solved

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

Posted on 2011-09-13
11
214 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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
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.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

863 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

18 Experts available now in Live!

Get 1:1 Help Now