Help with crosstab query I think?

[nunan]

Hi all

I have a report that details various types of analysis based on queries and all is fine but I am having trouble with one and need some advice on how to go about it please.

What I need to achieve on the report in a table like format is this:

Column 1 = Resolved by. The rows need to list names of people that have resolved complaints.
Column 2 = Between 0-2. The row needs to display the total number of complaints for the corresponding person that were resolved in between 0-2 days.
Column 3 = Between 3-7. The row needs to display the total number of complaints for the corresponding person that were resolved in between 3-7 days.
Column 4 = Between 8-13. The row needs to display the total number of complaints for the corresponding person that were resolved in between 8-13 days.
Column 5 = More than 14. The row needs to display the total number of complaints for the corresponding person that were resolved in more than 14 days.

I have created 4 select queries (trying to do it in one query didn't seem to work!), each query has the following fields:

Resolved by
Date received
Date resolved
And a date diff field calculated from the received and resolved fields.

The criteria for each query is the date diff I want, e.g. criteria for date diff field in query one is 'Between 0 And 2', criteria for date diff field in query two is 'Between 3 And 7 etc.

Any help would be greatly appreciated x

 

[dhookom]

I would first create a table of resolution days:
[TT][Green]
tblResolDays
=======================
MinDays
MaxDays
ResolTitle[/green][/tt]

With records like:[TT][Green]
tblResolDays
=======================
MinDays MaxDays ResolTitle
0 2 R 00 to 02
3 7 R 03 to 07
8 13 R 08 to 13
14 99999 R 14 and More
[/green][/tt]

You can then create a crosstab with this new table and your original query/table that has a column:
[tt][blue]DateDiff("d", [Date received],[Date resolved])[/blue][/tt]
and a criteria of:
[tt][blue]Between MinDays and MaxDays[/blue][/tt]

Set [Resolved By] as the Row Heading, ResolTitle as the Column Heading, and count of something as the Value.




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]

 

[nunan]

Hi

Thanks for your help. I have the crosstab query working now and am trying to find some advice on the report side.

I notice in a lot of your replies you mention the example on the Invisible Inc site but when I try the link there is nothing on the page that loads???

http://www.invisibleinc.com/divFiles.cfm?divDivID=4

Am I being blond or has the file now been removed?

 

[dhookom]

The site was a company I had worked for about 5 years ago. They removed my samples a few months ago. I have examples at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

I'm am also creating a site at

http://www.hookom.net/access.

I just changed hosting sites so there may be some lingering issues.

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]

 

[nunan]

You are an angel thanks x

 

[nunan]

Hi again

It's saying I don't have permission to enable or convert the database.

I am in Access 2003???

Thanks

 

[dhookom]

If you download a file from the internet, you usually need to browse to the file in Windows Explorer and right click and choose properties. Select to Unblock the file.

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]