Search results

hey.. sorry for the late reply. was out on personal accounts.. hope the error is solved by now.. if not.. from your screenshot i could see only one possibility of error. try putting count(DISTINCT [TableName]![ColumnName]).. note the use of the square brackets.

can you share the table structure you are working with??

Please see the following thread... http://www.access-programmers.co.uk/forums/showthread.php?t=85003

DatePart("yyyy",date_variable) is what you are looking for...

If so, then add a new column into the query, that extracts the year from the date, (eg. use DatePart, or convert into string and extract the last two chars). Now group by this column and give 'sum' for sales column.

I don't know if this is what you want. Try giving the 'distinct' clause if you don't want double counting. ie. count(distinct qry_cfu_HD.Size) as ...

If you note properly, in your query SELECT clause you still have tblFlight.FlightTime, before Sum(tblFlight.FlightTime). Remove that, and your error should vanish.

I'll have to admit, that this star's shine is not on mySQL... However, I don't understand why max does not work... May I know what error is actually thrown??

also clarify what type of column is FlightTime. is it time datatype. ie. 1:34 denotes 1:34 am/pm or does 1:34 signify 1 minute and 34 seconds???

Did you try giving sum(tblFlight.FlightTime) in the select clause and removing it from the group by clause???

Did you try max() instead of first() ??

Forget nulls.. I don't see the else part of the outer IIF statements at all. In this case first we first check if Hoitiotili <> 51, then <> 52, then <> 61, finally <> 62. If all satisfies then "EUR", else if Hoitiotili=62 then "Ulk os maa". You have not written in the else handlers of the outer...

Thnx for the praise. But I don't deserve it now... Coz ppl like you bring out the star in me.. ;)

SELECT tableA.fieldA, tableA.fieldB, first(tableB.fieldA) as expr1 FROM tableA LEFT JOIN tableB ON tableA.fieldID = tableB.fieldID group by tableA.fieldA, tableA.fieldB; This should do it..

ie. what is actually the purpose of Duty Payrate and FlightTime??

could you please give details on the structure of both the tables.

group by in design view and change the group mode as first or last for tableB.fieldA

That is a real cool functionality you expect out of Access; as a matter of fact out of any Database Management System. I'm afraid, I don't have the answer to this. Let's eagerly wait for some other input from others.

This should work if your Time table does not track volunteers who have not booked time for that day. Instead, if the Time table tracks all volunteers, for all dates, say with 0 as working hours, for the days they didnt book, then change the last clause in the actual query from Is Null to =0 ...

I dunno if this may be combined into one step. But here is what you can do. First let me tell you the tables I'd created. Vol_Info : Vol_ID,Vol_Name Vol_Time : Vol_ID,Work_Date,Time_Worked Create a query SubQry1 as SELECT Vol_Time.Vol_ID, Vol_Time.Work_Date, Vol_Time.Time_Worked FROM...