Adding time format in a COLUMN(not row)

[spearson]

Got one for you! I am trying to calculate how much time is spent on tasks in a time criteria. I have created the query to filter a time period such as 8/1/02 to 9/1/02. In my table I have a column called time used which has a format like 4:40 meaning 4 hours and 40 minutes. In my query I have a column which changed my time to a decimal which can be added to give me hours and minutes. I just need to add these numbers in the column to get a total time used Cell. Grouping only sums rows not columns. How can I do this?

NEED HELP!!

thanks
steph

 

[Jack Cowley]

Hmmm. Grouping sums Columns not rows. If you use a Totals query and select Sum for the Column you want to add you will get the total. If you are also grouping on a name, for instance, then you will get the total for each name.

hth,
Jack

 

[Pat Hartman]

There are NO functions in a relational database that work across columns. Use a spreadsheet if your table structure is flattened. It will be easier for you. If you want to stick with a table, normalize the structure. Once that is done, functions that operate down a column will work fine.

 

[spearson]

I got it...Thanks- I changed the groupby in my TimeUsed Column to Sum like you said, then right clicked on TimeUsed and went to properties to change the format to short time and it is working perfect.

Got one problem with the form that I created from that query. In my query there are six criteria. A Beginning Date, An Ending Date, A Name and 3 difference task codes to enter separately. Once these have been entered then the query displays the total time used for the specific task and date criteria.

When I use the form, you have a drop down menu with all the options in it but when you choose one it will not change. I tried to set up a different query with no criteria set up but not sure what is the problem or how to set criteria in a form. Can this be done?

 

[Jack Cowley]

I am a little confused by what you want, but let me try this... Create a form with 3 combo boxes and 3 unbound controls. Name the Unbound controls BeginDate, EndDate and Name. Base the three combos on the three different task codes so it is easy for the user to select them. Name them, for this example, TaskCode1, TaskCode2 and TaskCode3. Place a command button on this form to open a report or a form, whatever you want.

Next go to your query and in the Criteria line for the date place code like this:

Between [Forms]![YourFormNameHere]![BeginDate] And [Forms]![YourFormNameHere]![EndDate]

In the criteria line for the Name column put:

[Forms]![YourFormNameHere]![Name]

(Note: If you have a control or field named Name then you should change it as Name is a reserved word in Access.)

For the task code 1 criteria use the following:

[Forms]![YourFormNameHere]![TaskCode1]

I think you get the picture. Now just fill in the data on the form and click you button and you should see whatever it is you want to see!

I hope I understood your question....

Jack