Query code

[matpj]

Hi, I have a query, and no nothing about SQL really.
I need to perform an average on some times.

I have a 'Problem Type' field, and have written an expression that calculates the duration and displays it in 'hh:mm' format.
I need to somehow write an expression that will average that time field at each change in the 'Problem Type' Field, and then display each problem type with one entry just showing average time.

I could paste my current SQL script if it will help.
Should I have a sub query to use the average function?

kind regards,

Matthew

 

[Amiel]

The query will look something like this...

"SELECT [Problem Type], avg([YourTimeField]) as [Avg Time]
GROUP BY [Problem Type]"

...displays a list of the avg. time for each problem type.

You can almost cut and past this query into your module.

More? problems? Let me know.
As always, have fun! Gotta go to work now B-). Amiel
amielzz@netscape.net

 

[matpj]

Amiel,
thankyou for your reply. I do not know where to paste your statement. I tried a few locations but it had some errors, one error showed 'Data type mismatch' or words to that effect.
Here is my SQL code:
It is quite a simple thing, but the average thing may not work because even thought there are fields with the same 'Problem Type' they are still individual, as they have a unique data/time field ('Date', and 'Cdate')
Any ideas? I tried creating another query which uses just the problem type and the duration fields from the first one but I cant get it working.


SELECT [Problem Tickets].[Problem Type], [Problem Tickets].Date, [Problem Tickets].Cdate, DateDiff("s",[Date],[Cdate]) AS duration
FROM [Problem Tickets]
GROUP BY [Problem Tickets].[Problem Type], [Problem Tickets].Date, [Problem Tickets].Cdate;


Matthew

 

[Amiel]

Change the query to read...

SELECT [Problem Tickets].[Problem Type], [Problem Tickets].Date, [Problem Tickets].[red][[/red]Cdate[red]][/red], DateDiff("s",[Date],[Cdate]) AS duration
FROM [Problem Tickets]
GROUP BY [Problem Tickets].[Problem Type], [Problem Tickets].Date, [Problem Tickets].[red][[/red]Cdate[red]][/red];

Notice the brackets around "Cdate" , CDate is a conversion function name in the VBA language.

As you mentioned, create a query that uses just the problem type (for a problem ticket?). AS in:

SELECT [Problem Tickets].[Problem Type], avg([Date]) as [Avg Time]
FROM [Problem Tickets]
GROUP BY [Problem Tickets].[Problem Type]

Select 'Create' from the Query tab. Close the table selection dialog. On the drop down menu above select, "View", then select "view SQL". Cut the above query and paste it into the pane. Save it, and run it. More? let me know.
Amiel
amielzz@netscape.net

 

[matpj]

I have tried this, but I get the error:


Data type mismatch in criteria expression.


any ideas?

Matthew
when I use an expression to form another field, as in my above SQL (duration), can I just put that in your latest code in place of [Date]? do I need to put it in square brackets?

 

[Amiel]

Yes, the word,"Date", is also the name of a function in VBA. You must place brackets around it. Amiel
amielzz@netscape.net

 

[matpj]

ok, but why do I get a data type mismatch? would it be because my Date feilds are of the form 'dd/mm/yy hh:mm:ss' and I am trying to average the time part of it?


help!

Matthew

 

[Amiel]

The words "criterion expression", suggest to me that it is the formatting of the values that are used that is causing the problem.

The use of a date value (as distinct from field name) here is no problem, however a date (and any variable) must be provided with the correct formatting.

The conventional "American" date format for dates is "mm/dd/yyyy" and not, "dd/mm/yyyy", as you indicated you are using (above).

If you are using the format that you indicated in the query expression and the date (data) is stored in the table in another format then, yes, this is the source of the problem. Check that and be sure.

Also, are you using a WHERE clause in the statement? Amiel
amielzz@netscape.net