View Full Version : MS Access operation help

31-07-2010, 12:35 PM
Hi i am doing an assignment for school that requires me to use MS access to solve a problem but the resource i have been given tells me how to use MS access 2000-2003 (yes these books are hopelessly out of date) up until now i have been able to figure out how to use all the programs in ms office without the help of the resources (all dating back to 2000) my teachers are being even more useless than my resources and i cant find any useful information/tutorials for MS access 2010 (trial) i know this is not exactly a computer issue but i really need help :( any help is appreciated, my questions are below:

1. i need to work out the difference between the start time and end time displayed as hours and minutes in the total hours field, the times are written in 24 hour clock form i have tried just working the hours and minutes out as a number but then it wont let me put in decimal places.

2. i have the date of birth for several kids how can i calculate how old they are with that information

Thanks in advance

31-07-2010, 12:56 PM
forgot to add this one:

3. how do i calculate the min average max and sum of a currency field?

31-07-2010, 02:46 PM
You need to do a little research on the DateDiff function.

Syntax (from rusty memory) is domething like Datediff("yyyy",{EarliestDate},{LatestDate}) although I'm not certain that the "yyyy" bit doesn't go as the last arguement and not the first... but I'm not going to check and do all your homework for you.

If you're puting decimals into the answer, then you aren't really getting to grips with the purpose of using months and days to do the equivalent of the decimal portion of the year.

The "yyyy" bit can be replaced with "mm" or "dd" in order to get greater use out of the function.

You should also be using Date() for your age calculations rather than hard-coding in todays date.

As for your numerical work with the currency field... perhaps a Query using the Totals (sigma) button... that gives you Min, Max, Sum, Count.

The resources you have should be fine - this part of Access hasn't really changed since the mid nineties.

31-07-2010, 03:18 PM
thanks for that, im was trying to calculate the hours between two different times for question 1, to display the minutes i was trying to use a decimal places but it wouldn't let me, i have read many forums with questions like this and have tried many datediff functions for calculating days between the children dob and today date but mine never seemed to turn out even thought the a written exactly like the examples given. ill give an example of some i trying to calculate:

1. difference between 7:45 and 16:00 i could get the answer sometimes but it was displayed as a time and i need it as hours and minutes

2 years between 3/2/2004 and 31/7/2006

3. i have no idea what you mean :waughh: ("As for your numerical work with the currency field... perhaps a Query using the Totals (sigma) button... that gives you Min, Max, Sum, Count")

sorry i have never used this program before

31-07-2010, 05:51 PM
Some of the differences in the New Access 2010 are way different from Works or 2000

For dates it's done in Query or Filter and in "design view" to find someone of 20 yrs or older you enter >=31/07/1980 in the criteria field.

The Office help is great and watching some of the online tutorials can be very helpful.

31-07-2010, 09:08 PM
Ohh, yeah, I haven't even seen Access 2010, so I have to confess to some ignorance there.

It used to be with queries that you had to enclose dates in the criteria line within hashes, these things #. Dunno if that is still the case.

eg: >=#31/02/2001#

The DateDiff function also works with hours, minutes and seconds. Dates and times are kinda one-and-the-same. A year being about 365.25 days, a day being 24 hours, an hour being 60 seconds. If you can do the maths, then any date can be stored as a number of seconds since a particular date and time (this is actually what the computer does for dates - it doesn't actually store a date as you recognise, it stores a number which is an interval of time since a standard reference date. Knowing the reference date, and that interval of time the computer then does some maths to generate a date in the manner a human is familiar with).

If you want to get right down to the nitty gritty, you might find you can get an easier way to recognise the problem if you determine the number of seconds between the two times. Once you know the number of seconds you need to divide by 60, and convert to integer to get the number of minutes. Divide by 60 and convert to integer again to get the hours.
Once you know the hours and minutes you can do a bit more maths (or find another function that gives you the remainder when you divide your seconds by 60) to figure out how many seconds are in the answer. Then put it all together to get your final answer.

Something along the lines of: Int(Datediff("s",[StartTime],[EndTime])/3600)
will give you the hours (60 seconds x 60 minutes = 3600)

Then Int(Datediff("s",[StartTime],[EndTime])/60) will give you the minutes.

((There is a simpler way, if you care to study DateDiff and pay attention to the use of "h" or "m" or "s" within the function.))

There's a couple of ways you can figure out the seconds that remain, but I don't wanna do all your homework for you.

You can use a few more tricks to string the answer together... getting a single output from a single (long) formula.

The key thing is a single operation of DateDiff will not give you the answer you want (unless you also study the Format() function)

If you run DateDiff once for the hours, and a second time for the minutes you'll be a long way there.

Concatenation is the process of joining strings (text). In Access you use the & to join strings. You can put in you own text as well by enclosing it inside quotes "like this".

You can construct the whole thing like this:

=Int(Datediff("s",[StartTime],[EndTime])/3600) & ":" & Int(Datediff("s",[StartTime],[EndTime])/60) & ":" & "figure out the seconds yourself"

This will give you an output in hours:minutes:seconds format.

You can substitue "." instead of the ":" if you wish, but times typically are displayed with : as the seperator.


As for the Min, Max , Sum and Average bit - a Query is your easiest route.
Google the Greek alphabets Sigma character. In query design there is a Sigma button (Totals). Once you are in Totals mode you get another line of data handling options, typically "Group By" is the default option displayed.
Add your Currency field to the query FOUR times.

Change Group By to Min, Max, Sum, Avg for each of these Currency fields.

Run your query to see the result.

Job's done!

Access is tons more challenging than the other Office apps, because frankly it is tons more versatile, but demands more of the user (or programmer) to master all the facets of it.

From my own experience, Access is easiest to get to grips with via a book.
The Help in Access 2003 was just pathetic unless you have online Help available to you, and in my opinion the older versions of Access were easier to learn from.
I can't comment on anything later than 2003.

01-08-2010, 12:43 PM
Thanks so much for all of your help i will try out everything you suggested and let you know how it goes, thanks again