Calculating time 1

[telcomwork]

I have a problem that I can not figure out using MS Access. I don't use Access very much at all other than very simple DB functions.

My query needs to have an output that displays first name, last name and total time spent participating in events.

My first table EVENT NAMES has the titles "Event ID" with 4 events listed, "Date" with each listed, "Start Time" in the format h:m:m for all four, "End Time" (same as start time), and "Location" for all four.

My second table EVENT PARTICIPATION has the titles "Member ID" and "Event ID". Each member has multiple entries for the coresponding Event. Some participate in one event, while other participate in 2 or 3.

My third table MEMBERS has "Member ID" for each of the 7 people, and "Member Last Name", "Member First Name", "Address", "Phone" adn Yearly Physical.

Any ideas on what I need to do in my query to get last name, first name and Total time they spend participating in events?

Here are some preint screen shoots of what I'm looking at unless someone wants to look at the DB first hand.

http://milletarypa.tripod.com/Help.ppt

Thanks!

 

[dhookom]

Create a query with all three tables and join the appropriate fields. Add the name fields to the query grid as well as a column:
TotalMinutes: DateDiff("n",[Start Time], [End Time])

Change the query to a totals query and group by the name fields and Sum the TotalMinutes column.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[telcomwork]

Thank you. I'll give that a try now!

 

[telcomwork]

OK stupid question so please bare with me because this is all new to me,

1. How do I change to a totals query and group the name fileds and sum the TotalMinutes colum? Is there a way to put this in hours instead of minutes. Also I guess with these steps it will combine the information so there is one number for one name?

Thank again for your patience adn your help!

Here's what it's look like so far..

http://milletarypa.tripod.com/help2.ppt

 

[telcomwork]

I changed the "n" to an "h" for hours, I think that's right however when I run my report my times seem to be rounded instead of showing for instance

Johson John should have 10.25 Total Time and the following
Franklin John 6.5
O'Shea Jamie 5
Falkner Nicole 5
Mason Lee 4
McGregor Taylor 2.5

Here's my latest...

http://milletarypa.tripod.com/help3.ppt

 

[dhookom]

Change your calculation back to "n" for minutes and then divide by 60.
TotalMinutes: DateDiff("n",[Start Time], [End Time])/60


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[telcomwork]

Bang! So nice!!

Thanks man!