Adding Checkbox to a grouped by query (1 Viewer)

[kobiashi]

Hi all

i have an issue im not sure how to resolve.

i have a query thats grouped in in that query is an expression, that calculates total hours, baed upon a user and date which is the last box in picture 1 below. picture 2 displays the sub form that the query is bound to, in table TblManHours there is a field called "Approved" this is a yes/no property, what im trying to do is be able to check this box on the sub form, but the query will not allow me tp update as its grouped by, is there any other way of creeying this out?

 

[plog]

Why are you using GROUP BY in your query?

 

[kobiashi]

Why are you using GROUP BY in your query?

the field "minutes" is an aggregate

on the form its viewed as "Total hours"

 

[theDBguy]

Hi. Maybe try using DSum() instead?

 

[plog]

Is it? ManHours_ID is in the query which seems like it virtually ensures there will be no grouping of data.

If you remove the grouping do you get the same amount of records?

 

[kobiashi]

Is it? ManHours_ID is in the query which seems like it virtually ensures there will be no grouping of data.

If you remove the grouping do you get the same amount of records?

if i remove the grouping i get an error (which ever field is first i,e Manhours_ID) your query does not include the specified expression manhours_ID as part of the aggregate function

 

[kobiashi]

Hi. Maybe try using DSum() instead?

thanks ill take a look

 

[plog]

Yes, you will have to eliminate the calculation of the Minutes field. You can't use SUM. I don't think you need it since there's no point in aggregating this query.

I believe you simply need to use DateDiff to calculate minutes instead:

MS Access: DateDiff Function

This MSAccess tutorial explains how to use the Access DateDiff function with syntax and examples. The Microsoft Access DateDiff function returns the difference between two date values, based on the interval specified.

www.techonthenet.com

 

[kobiashi]

Yes, you will have to eliminate the calculation of the Minutes field. You can't use SUM. I don't think you need it since there's no point in aggregating this query.

I believe you simply need to use DateDiff to calculate minutes instead:

MS Access: DateDiff Function

This MSAccess tutorial explains how to use the Access DateDiff function with syntax and examples. The Microsoft Access DateDiff function returns the difference between two date values, based on the interval specified.

www.techonthenet.com

thanks for the reply, forgot to mention, on the sub form under Total hours, under the control source of the field i have this "=[Minutes]\60 & Format([Minutes] Mod 60,"\:00")" which converts the total minutes in the hours and minutes, so if i remove the sum this no longer works.

 

[plog]

Technical issues gets solved in steps. The only reason I wanted you to remove the aggregation is to prove that it wasn't needed. If you run the query aggegated and unaggregated and get the same number of results, there is no point in the aggregation.

Once that is established you can remove the aggegation and design your query without it so that your query is editable.

 

[Pat Hartman]

Also, do NOT use LIKE when you mean =. It is very inefficient since it causes a full table scan to retrieve all the rows of the table and examine each and every one of them. Using = allows the query engine to use indexes to find the set of records you want to retrieve.

 

[kobiashi]

Technical issues gets solved in steps. The only reason I wanted you to remove the aggregation is to prove that it wasn't needed. If you run the query aggegated and unaggregated and get the same number of results, there is no point in the aggregation.

Once that is established you can remove the aggegation and design your query without it so that your query is editable.

thanks for the help, i decided to handle the check box on another form, which achieved the same result