Arg! Stupid business rules! 1

[ErikZ]

Ok, I have a table.

On this table are Services, Tasks, and Projects
Projects and services are independant, but a task can be independant, or belong to a service. In this case, it's called a task, but it's really a service.

I need to do a ratio of Services, Tasks and Projects, shown in a pie chart. The table tells me if it's a service, task or project, and it also has a "link" column. If the task belongs to a service, the key to that service is in this field, otherwise it is empty.

So, my query would show how many tasks, services and projects I have, then it's easy to put it into a pie chart. I can't figure out how to set up the query though. :-(

 

[MichaelRed]

Not to clear, but if Tasks which are part of a service are NOT counted, then a simple aggregate query w/ criteria for Tasks not null (or not "" or not ...) does it.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[ErikZ]

Ok, so that query can show me the true tasks. Now, is it possible to get a list of services and projects in the same query?

Remember, a task that is part of a service is considered a service for the chart, and should be counted into the service total.

 

[THoey]

Since you have this all in one table, how do you distinguish a task record from a service record from a project record? Please describe your table and maybe show some sample data (one of each type). This doesn't look that difficult... Terry M. Hoey

 

[ErikZ]

ItemType: Task, Service, Project

They are all contained in the same table. Tasks actually use a "ItemLink" column if they are part of a service.

ID ItemType ItemLink
1 Project
2 Service
3 Project
4 Task 2
5 Task 2
6 Task

So, the totals for this (For the ratio report) would be

project 2
service 3
task 1

I don't think I'll be able to do this without resorting to VBA.

 

[THoey]

Would a union of three queries work? I am not very experienced with them, but I have seen some neat tricks done here with them. Like I said, this is a guess:

SELECT "PROJECTS", COUNT(*)
FROM tblItems
WHERE ItemType = 'project'
UNION
SELECT "SERVICES", COUNT(*)
FROM tblItems
WHERE ItemType = 'service' OR ItemLink IS NOT NULL
UNION
SELECT "TASKS", COUNT(*)
FROM tblItems
WHERE ItemType = 'task' and ItemLink IS NULL
Terry M. Hoey

 

[THoey]

BTW, I did test this and it does work... Terry M. Hoey

 

[MichaelRed]

The Table(in the below it is "tblStuff&quot

Id ItemType ItemLink[\b]
1 Project
2 Service
3 Project
4 Task 2
5 Task 2
6 Task

First Query


Id ItemType ItemLink ReType
1 Project Project
2 Service Service
3 Project Project
4 Task 2 Service
5 Task 2 Service
6 Task Task

First Query SQL (in the below "qrySvcType&quot
SELECT tblStuff.Id, tblStuff.ItemType, tblStuff.ItemLink, IIf(IsNull([ItemLink]),[ItemType],"Service&quot AS ReType
FROM tblStuff;

Second Query

ReType NumOfType
Project 2
Service 3
Task 1

Second Query SQL
SELECT qrySvcType.ReType, Count(qrySvcType.ReType) AS NumOfType
FROM qrySvcType
GROUP BY qrySvcType.ReType;


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[ErikZ]

Wow TH3856, That worked perfectly.

Man, when I saw how easy it was to set up querys using the graphical interface in Access, I thought I could forget all that SQL stuff. Add more more thing to the "Why I hate Microsoft" list.

Thanks again! This one was driving me crazy!

 

[THoey]

Thanks Erik. That was my first ever union. Surprised it worked... Terry M. Hoey