Chart based on counts of records on a date

[astoch]

I think that I am making this more difficult that I need to but here goes: (Thanks in advance for any help).

I have a table which records a few different dates for a particular order:
Order_Num
planned_complete_date
actual_complete_date

I want to create a chart that shows:
Count of orders planned to complete during a period
Count of those orders that were completed on time
Count of those orders that were late
Count of those orders that have not yet been completed

I get stuck in the queries as follows:
I have created 4 individual 'total' queries that correctly count each of the pieces of info I need for the chart (all counts are based on planned_complete_date).
I then created a fifth query where I am trying to bring the 4 individual queries together by joining them on planned_complete_date. I use the Count of orders planned to complete as the main query and link the other 3 to it.

Even though I am changing the join property to show all records for the planned complete, and only those that have values for the others, I get all nulls for all of the others.

Please help.

 

[jksmi]

hi,

you can do it all in one query - the thing here is that you dont need any joins...

SELECT d1.TOTAL_PLANNED_ORDERS,
    d2.COMPLETED_ON_TIME
    d3.COMPLETED_LATE
    d4.UNCOMPLETED
FROM (SELECT COUNT(ORDER_NUM) AS TOTAL_PLANNED_ORDERS
    FROM YOUR_TABLE
    WHERE PLANNED_COMPLETE_DATE BETWEEN PD_START_DATE AND PD_END_DATE
    )AS d1,
    (SELECT COUNT(ORDER_NUM) AS COMPLETED_ON_TIME
    FROM YOUR_TABLE
    WHERE (PLANNED_COMPLETE_DATE BETWEEN PD_START_DATE AND PD_END_DATE)
        AND PLANNED_COMPLETE_DATE >= ACTUAL_COMPLETE_DATE 
    )AS d2,
    (SELECT COUNT(ORDER_NUM) AS COMPLETED_LATE
    FROM YOUR_TABLE
    WHERE (PLANNED_COMPLETE_DATE BETWEEN PD_START_DATE AND PD_END_DATE)
        AND PLANNED_COMPLETE_DATE < ACTUAL_COMPLETE_DATE 
    )AS d3,
    (SELECT COUNT(ORDER_NUM) AS UNCOMPLETED
    FROM YOUR_TABLE
    WHERE (PLANNED_COMPLETE_DATE BETWEEN PD_START_DATE AND PD_END_DATE)
        AND ACTUAL_COMPLETE_DATE IS NULL
    )AS d4

swap PD_START_DATE and PD_END_DATE for actual dates or prompt the user to enter them - but they will need hash marks around them so that Access can recognise them as dates (i.e. #24/06/2004#)

HTH, Jamie
FAQ219-2884

 

[astoch]

Jamie,

Firstly, thanks a mil for your help.
Sorry for being so slow, but I am still stuck.
I copied and pasted your code into a new query in my app (MSAccess 97). I inserted my table name.

First I got error messages that there was an extra ")". So I deleted the ")" before all "AS" statements.
Now I am getting the error messages that there is a missing operator and Access is directing me to the AS statement.

your ongoing help is HUGELY appreciated.

 

[PHV]

Unfortunately, I don't think the FROM (SELECT ...) AS is legal in access97.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jksmi]

Sorry, I use A2K so I'm not sure - but you should be able to use the old queries you made (they return one row each?) and drag them into a new query, no joins, just add the fields to the query and you should get one row of counts...

HTH, Jamie
FAQ219-2884