Calculating the number of days/weeks/months/years that have passed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have made a very simple database to allow me to keep a record of all the
people my team and I are supporting through difficult times. To ensure we
continue to receive funding we are required to do a quarterly return that
currently is a nightmare to complete. Specifically I need to show:

How many people were being supported at a given date.
How many people were signed off between two dates.
How many people died between two dates.
How many people have been receiving support in excess of two years.
How many people have been receiving support for between one and two years.
How many people have been receiving support for less than or one year exactly.

I suspect that the above can be achieved through a query but I'm at a loss
at how to set the criteria.

Ideally I would also like to have a field on each record that will show on
the individual record form the time elapsed since support began.

I know I'm asking a great deal but I've just got to that point where I can't
go any further on my own. (Yep, I'm a complete beginner!) So, if anyone
would be kind enough to kind of spell it out for me in the most basic of
terms, or point me in the direction of somewhere that I can get the answer I
will be forever grateful. Thanks.

Doug
 
Hi,
If all those criteria can be supported by one query, it won't be easy to
maintain.

The last 3 can fit into one query
a) Select the table, and fields required in the query builder
b) Add a new field, type in like follows
Duration: DateDiff("y",Now(),SupportStart) <-- check the syntax
c) Add a second new field
IDuration: Cint(Duration)
d) Press the group button (the funny E). You want to group by IDuration, and
count the number of Ids.

Approximately:
The first:
SELECT Count(ID) FROM tblSupport WHERE IsNull(SupportEnd) = true and
SupportStart < [Enter date]

The second
SELECT Count(ID) FROM tblSupport WHERE SupportEnd Between [Enter start date]
and [Enter end date]

The third
SELECT Count(ID) FROM tblSupport WHERE DeathDate Between [Enter start date]
and [Enter end date]

hth
Marc
 
Hi Marc,

Thanks for your efforts in trying to assist me. As I mentioned, I'm very
new to access and finding your instructions to difficult. However, I will
plod on and see if I can get it right. I really do appreciate your help
though, just sorry I'm not able to make the best use of it right now.

Regards

Doug


Marc said:
Hi,
If all those criteria can be supported by one query, it won't be easy to
maintain.

The last 3 can fit into one query
a) Select the table, and fields required in the query builder
b) Add a new field, type in like follows
Duration: DateDiff("y",Now(),SupportStart) <-- check the syntax
c) Add a second new field
IDuration: Cint(Duration)
d) Press the group button (the funny E). You want to group by IDuration, and
count the number of Ids.

Approximately:
The first:
SELECT Count(ID) FROM tblSupport WHERE IsNull(SupportEnd) = true and
SupportStart < [Enter date]

The second
SELECT Count(ID) FROM tblSupport WHERE SupportEnd Between [Enter start date]
and [Enter end date]

The third
SELECT Count(ID) FROM tblSupport WHERE DeathDate Between [Enter start date]
and [Enter end date]

hth
Marc
Dugster said:
I have made a very simple database to allow me to keep a record of all the
people my team and I are supporting through difficult times. To ensure we
continue to receive funding we are required to do a quarterly return that
currently is a nightmare to complete. Specifically I need to show:

How many people were being supported at a given date.
How many people were signed off between two dates.
How many people died between two dates.
How many people have been receiving support in excess of two years.
How many people have been receiving support for between one and two years.
How many people have been receiving support for less than or one year exactly.

I suspect that the above can be achieved through a query but I'm at a loss
at how to set the criteria.

Ideally I would also like to have a field on each record that will show on
the individual record form the time elapsed since support began.

I know I'm asking a great deal but I've just got to that point where I can't
go any further on my own. (Yep, I'm a complete beginner!) So, if anyone
would be kind enough to kind of spell it out for me in the most basic of
terms, or point me in the direction of somewhere that I can get the answer I
will be forever grateful. Thanks.

Doug
Click to expand...
Click to expand...
 
Sorry, didn't mean to be obscure. And a bit rushed so this worked example
has more steps.
Ok,
Select query
Select new
Select the table from the dropdown box of all tables - click next
Select all the fields required - click next
Access brings up a screen - top half the picture of the table selected, the
bottom half a grid of columns.
Drag each field you want to see in the report / form/ query into a column.
Go to the next new column, click on the magic wand
It brings up a dialog. Click on functions / built-in
In this case highlight DateDiff, and click Paste
Click help and that will tell you what each parameter needs to be.
Now click on the first entry on the leftpane. Should bring up the field list
you selected above.
Replace the first <<>> with the type of result as "d" , and the 2 dates you
want the difference between. Then add / (divide by) 365.
Click ok.
You're back to the query grid. Your expression is there with the literal
Expr1: preceding it. Change this to Duration or some other name that you
will remember when you look it this query in a year's time. say Wait for
example.
Then in the next column type in IWait: Round([Wait]-.5,0)
This will give you the whole years, rounding down.
Save this query as say MemberYearsPre
Click new - select simple
Select the query you just created
Select the unique field and the rounded field.
next next
Now click on the funny E.
Change the column that has an unique value from group to count.
Click run

Marc

Dugster said:
Hi Marc,

Thanks for your efforts in trying to assist me. As I mentioned, I'm very
new to access and finding your instructions to difficult. However, I will
plod on and see if I can get it right. I really do appreciate your help
though, just sorry I'm not able to make the best use of it right now.

Regards

Doug


Marc said:
Hi,
If all those criteria can be supported by one query, it won't be easy to
maintain.

The last 3 can fit into one query
a) Select the table, and fields required in the query builder
b) Add a new field, type in like follows
Duration: DateDiff("y",Now(),SupportStart) <-- check the syntax
c) Add a second new field
IDuration: Cint(Duration)
d) Press the group button (the funny E). You want to group by IDuration, and
count the number of Ids.

Approximately:
The first:
SELECT Count(ID) FROM tblSupport WHERE IsNull(SupportEnd) = true and
SupportStart < [Enter date]

The second
SELECT Count(ID) FROM tblSupport WHERE SupportEnd Between [Enter start date]
and [Enter end date]

The third
SELECT Count(ID) FROM tblSupport WHERE DeathDate Between [Enter start date]
and [Enter end date]

hth
Marc
Dugster said:
I have made a very simple database to allow me to keep a record of all the
people my team and I are supporting through difficult times. To ensure we
continue to receive funding we are required to do a quarterly return that
currently is a nightmare to complete. Specifically I need to show:

How many people were being supported at a given date.
How many people were signed off between two dates.
How many people died between two dates.
How many people have been receiving support in excess of two years.
How many people have been receiving support for between one and two years.
How many people have been receiving support for less than or one year exactly.

I suspect that the above can be achieved through a query but I'm at a loss
at how to set the criteria.

Ideally I would also like to have a field on each record that will show on
the individual record form the time elapsed since support began.

I know I'm asking a great deal but I've just got to that point where I can't
go any further on my own. (Yep, I'm a complete beginner!) So, if anyone
would be kind enough to kind of spell it out for me in the most basic of
terms, or point me in the direction of somewhere that I can get the
Click to expand...
answer
I
will be forever grateful. Thanks.

Doug
Click to expand...
Click to expand...
Click to expand...
 
Back
Top