[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2011-09-13
11
Medium Priority
?
255 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 49

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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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 49

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…
Suggested Courses

612 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