Solved

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

Posted on 2011-09-13
11
246 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 (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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 (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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

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.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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.
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…

691 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