[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2011-09-13
11
Medium Priority
?
252 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 48

Expert Comment

by:Dale Fye
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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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 48

Accepted Solution

by:
Dale Fye earned 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

650 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