Query: Count (amount of (xDates later than GivenDate))

[Jean]

Hello all,

I have the following data (Note: dates in dd-mm-yyyy format)

ID DEPT TARGET70 TARGET90 TARGET100
-- ---- -------- -------- ---------
12 ACCT 12/06/2005 31/07/2005 21/12/2005
14 MKT 30/08/2005 02/09/2005 11/04/2006
19 ACCT 13/04/2004 13/05/2005 22/08/2005
20 SAL 12/12/2005 20/12/2005 12/02/2006
21 SAL 21/03/2004 21/03/2005 21/09/2005

etc. etc.

It basically shows what the target dates (TARGETxx) are for certain
stages of a deparment's (DEPT) different projects (ID).

What I have to do next, is proving to be quite trivial to me. Can
someone please help?

I must compare the target dates to a certain date (which is stored in a
global variable, say gdatTheDate), and then return all the projects
(ID) for which a target date is later than gdatTheDate. But it (the
amount of later dates) has to be counted for each dept and presented as
follows:

Say gdatTheDate = 05/08/2005
Then the following is returned

DEPT NUM70 NUM90 NUM100
---- ----- ----- ------
ACCT 0 0 2
MKT 1 1 1
SAL 1 1 2

I know how to use the global variable in a query, but how do I
implement the query to do the counting and grouping etc.?

Any help would be appreciated. Thanks,

J

[floronDBA]

Jean, what will you do when a project acquires another target? Add a
column, I suppose. But then this column can accept values for *all*
projects, whether they be running or not.

The following table design is more restrictive, in that it does not
allow spurious values:
(PROJECT, DEPT, TARGET, TARGETDATE)
It is fairly easy to do a crosstab query on this table to get the
result you want.

If you cannot change the table's design, you may resort to summing, in
stead of counting,
like this
SELECT dept, sum (IIF (target70>TheDate, 1, 0)) AS num70, <repeat for
other targets>
FROM targets

[floronDBA]

Jean, what will you do when a project acquires another target? Add a
column, I suppose. But then this column can accept values for *all*
projects, whether they be running or not.

The following table design is more restrictive, in that it does not
allow spurious values:
(PROJECT, DEPT, TARGET, TARGETDATE)
It is fairly easy to do a crosstab query on this table to get the
result you want.

If you cannot change the table's design, you may resort to summing, in
stead of counting,
like this
SELECT dept, sum (IIF (target70>TheDate, 1, 0)) AS num70, <repeat for
other targets>
FROM targets

[Jean]

Hi,

I have actually already had a suggestion to change the table design;
unfortunately I am accessing this data from an external ODBC source and
this is therefore not under my control. The data as I gave it in my
first post was a query that I created. Is it then possible to set up
another query that will have your suggested column headings? I.e.
(PROJECT, DEPT, TARGET, TARGETDATE)

I think I will follow your summing suggestion in the meantime. I
imagine it to be rather complex to set up a proper query for those
column headings, maybe I am wrong. But thanks for your help, it
certainly makes me one step further in finishing this job and
undertanding the problem.

Kind Regards
J

[floronDBA]

Well, if you use the Wizard for Crosstab Queries
(Queries | New | Wizard Crosstab Queries)
Access does something like the following:

TRANSFORM Max(targets.targetdate) AS MaxDate
SELECT targets.project, targets.dept, Max(targets.targetdate) AS Total
FROM targets
GROUP BY targets.project, targets.dept
PIVOT targets.target;

The result of this query is a table with
- Row headings: Project, Dept
- Column headings: Target
- Under each target heading: max(targetdate) for each combination of
Project, Dept, Target (blank if this combination is not present)
- A column called Total: max(targetdate) for every row

You may eliminate the Total column by editing the query