Question Locked

Access database date calculation

0 helpful
8 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I want to display the difference between one list of 10 dates and one list of 20 dates = 10 dates. But I would like to display the actual dates and associated data and not the difference in time.

Hope that makes sense.
Thank you,
Peter.

8 replies

by tony hopkinson

Are you saying you want to display the records from TableB that have a different date in TableA ?

if not a a few example records in each table and the expected result, should explain it well enough to get an answer.

by peter.anders

Hi Tony,
I have two fields in one table, one is called "Date Job Raised" and the other is "Date Job Finished". The amount of jobs raised by the 1/5/07 was 2000. The amount of jobs finished by 1/5/07 was 1800. This means that there are 200 jobs which had not be finished by the 1/5/07. Those 200 jobs were outstanding on the 1/5/07. However they had been completed since then, so the finish date is no longer blank but populated with dates >1/5/07. I would like to create a query that can return those 200 jobs. It needs to return all jobs where "Date Job Raised" <1/5/07 minus all jobs where "Date Job Finished" is < 1/5/07.

Have you got an idea, how to do that? I think it might be fairly simple.
Thank you,
Peter.

by tony hopkinson

Select * From Jobs
Where Date_Job_Raised <= '2007-05-01
and ((Date_Job_Finished is null) Or Date_Job_Finished > '2007-05-01'))

If I've understood.

All outstanding jobs on 1/5/2007 existed , (were raised) and have not yet been finished or were finished after.

by peter.anders

You certainly have understood. This is the start of a great German - British friendship.

Thankx again,
Peter.

by peter.anders

I am trying to fill an empty column in a table, similar to Edit> Fill> Down in Excel.
Is there a way in Access to do this easily or do I have to run a query?

This discussion is closed to new replies.