Find the first value in a crosstab query

Try this --
TRANSFORM First(tblMonthly.pf_td) AS FirstOfpf_td
SELECT tblMonthly.Job, Val(tblMonthly!act) AS Activity_ID,
Min(tblMonthly.Period) AS Start, Max(tblMonthly.Period) AS [End],
Last(tblMonthly.adesc) AS [Activity Title], Last(tblMonthly.whourbudg) AS
[Bud Hrs], Last(tblMonthly.whourerntd) AS [Ern Hrs],
Last(tblMonthly.whouracttd) AS [Act Hrs], Last(tblMonthly.whtot_td) AS [TD
$/Hr]
FROM tblMonthly LEFT JOIN tblProjInfo ON tblMonthly.Period =
tblProjInfo.CMIS_Mo
WHERE (((Val([tblMonthly]![act])) Between 9000 And 9499 Or
(Val([tblMonthly]![act])) Between 10000 And 59999))
GROUP BY tblMonthly.Job, Val(tblMonthly!act)
ORDER BY tblMonthly.Job, Val(tblMonthly!act)
PIVOT tblProjInfo.Period;

--
KARL DEWEY
Build a little - Test a little


"John" wrote:

I did, see note in prior response... still doesn't work...
--
Thanks - John


"KARL DEWEY" wrote:

Try removing the Min(tblProjInfo.Period), Max(tblProjInfo.Period), from
the ORDER by and GROUP by sections.
--
KARL DEWEY
Build a little - Test a little


"John" wrote:

Karl,

I get the following errors:

1. Cannot have an aggrigate function in an ORDER by clause
2. Cannot have an aggrigate function in an GROUP by clause

when I remove the Min / Max from the order by and group by clauses I get "4"
in all the Start / End columns and no data in the grid area.

I may not be explaining myself too well. Here's a sample of what I
bwant/b the output to look like:

PERIODS (or dates)--
Cost Code|START| END| 1 | 2 | 3 | 4 |
18800 | 3 | |0.00|0.00|1.50|1.52|
20200 | 0 | 3 |0.91|0.95|1.01|0.00|
36300 | 1 | |1.20|1.22|1.25|1.27|
42500 | 2 | 3 |0.00|1.01|1.00|0.00|

Notice that the START and END fields don't give you he max of the value (PF
TD), but rather the start period and end period of when workd started and
ended on the code.

Hope this helps define the problem better...

--
Thanks - John


"KARL DEWEY" wrote:

Try this --
TRANSFORM First(tblMonthly.pf_td) AS FirstOfpf_td
SELECT tblMonthly.job, Min(tblProjInfo.Period) AS [Start],
Max(tblProjInfo.Period) AS [End], Val(tblMonthly!act) AS Activity_ID,
Last(tblMonthly.adesc) AS [Activity Title], Last(tblMonthly.whourbudg) AS
[Bud Hrs], Last(tblMonthly.whourerntd) AS [Ern Hrs],
Last(tblMonthly.whouracttd) AS [Act Hrs], Last(tblMonthly.whtot_td) AS [TD
$/Hr]
FROM tblMonthly LEFT JOIN tblProjInfo ON tblMonthly.period =
tblProjInfo.CMIS_Mo
WHERE (((Val([tblMonthly]![act])) Between 9000 And 9499 Or
(Val([tblMonthly]![act])) Between 10000 And 59999))
GROUP BY tblMonthly.job, Min(tblProjInfo.Period), Max(tblProjInfo.Period),
Val(tblMonthly!act)
ORDER BY tblMonthly.job, Min(tblProjInfo.Period), Max(tblProjInfo.Period),
Val(tblMonthly!act)
PIVOT tblProjInfo.Period;

--
KARL DEWEY
Build a little - Test a little


"John" wrote:

Karl - it's rather complex, (at least for me..) but here you go...

It's a cross tab query built from a select query. The select query puts the
codes in order and returns only the fields I need for the crosstab query.
The select query to do this is:

SELECT tblMonthly.job, tblActMap.JVID, tblJVTitles.JV_Title,
tblProjInfo.Period, Sum(tblMonthly.whourbudg) AS [Bud Hrs],
Sum(tblMonthly.whourerntd) AS [Ern Hrs], Sum(tblMonthly.whouracttd) AS [Act
Hrs], IIf([Act Hrs]=0 Or [Ern Hrs]=0,0,[Act Hrs]/[Ern Hrs]) AS [TD PF]
FROM ((tblMonthly LEFT JOIN tblProjInfo ON tblMonthly.period =
tblProjInfo.CMIS_Mo) LEFT JOIN tblActMap ON tblMonthly.act = tblActMap.Act)
LEFT JOIN tblJVTitles ON tblActMap.JVID = tblJVTitles.JVID
GROUP BY tblMonthly.job, tblActMap.JVID, tblJVTitles.JV_Title,
tblProjInfo.Period
HAVING (((Sum(tblMonthly.whourbudg))0))
ORDER BY tblMonthly.job, tblActMap.JVID, tblProjInfo.Period;

The tblProjInfo.Period field is the date field converted to an ordinal date
(1, 2, 3, etc.) If I remove the ordinal date option, it will be a YYYY-MM
formated date. The formatting is not important here. I just need to know
when people started working on the code (ordinal period, or actual date), and
when they finished. (this is a monthly report on a 5 year project, so we
will end up with 60 date columns on the xtab query.)

The xtab SQL is

TRANSFORM First(tblMonthly.pf_td) AS FirstOfpf_td
SELECT tblMonthly.job, Val(tblMonthly!act) AS Activity_ID,
Last(tblMonthly.adesc) AS [Activity Title], Last(tblMonthly.whourbudg) AS
[Bud Hrs], Last(tblMonthly.whourerntd) AS [Ern Hrs],
Last(tblMonthly.whouracttd) AS [Act Hrs], Last(tblMonthly.whtot_td) AS [TD
$/Hr]
FROM tblMonthly LEFT JOIN tblProjInfo ON tblMonthly.period =
tblProjInfo.CMIS_Mo
WHERE (((Val([tblMonthly]![act])) Between 9000 And 9499 Or
(Val([tblMonthly]![act])) Between 10000 And 59999))
GROUP BY tblMonthly.job, Val(tblMonthly!act)
ORDER BY tblMonthly.job, Val(tblMonthly!act)
PIVOT tblProjInfo.Period;

I wish I could upload a screen shot to show you.. As they say, a picture is
worth 1000 words!



--
Thanks - John


"KARL DEWEY" wrote:

I do not see why my solution would not do it for you. Post the SQL for your
query so I can edit and post back.
--
KARL DEWEY
Build a little - Test a little


"John" wrote:

Sorry - my data sample kind of misslead you. They do not always increase in
value. It could go up or down. What I really need is the first occurance of
a non-zero value and (if the percent complete is 100) the last occurance of a
value before zero value... So for example:

0, 0, 0, .95, 1.05, 1.00, .95, .96, 1.2, 0, 0, 0, 0... etc.

should return the date that corresponds to the ".95", and the date that
corresponds to "1.2". (This code would be 100% complete.)

Thanks for the prompt reply...

--
Thanks - John


"KARL DEWEY" wrote:

Why not add two row headings - Start: Min([YourDateField])
- End Max([YourDateField])

--
KARL DEWEY
Build a little - Test a little


"John" wrote:

I have a crosstab query with YYYY-MM as column headings and cost codes as row
headings. It looks something like:

Cost Code, 2008-01, 2008-02, 2008-03, 2008-04...
18800 0 0 1.50 1.52
20200 0 0.91 0.95 1.01
36300 1.20 1.22 1.25 1.27
42500 .90 1.01 1.00 0

The values are various types of things (quantities, production factors,
cost, etc.). What I would like to do is add two columns as headings next to
the cost code for Start (YYYY-MM), and End (YYYY-MM if 100%). I am having a
little trouble calculating the first month that actually has a value in it.
Like cost code 18800 should have a start of 2008-03, and a blank end date
because it's not finished, and cost code 42500 should have a start date of
2008-01 an an end date of 2008-03 because it's finished. (The table has a
percent complete field so knowing it's 100% is rather easy.

Any suggestions would be appreciated...

--
Thanks - John

Karl! What you gave me didn't work, but it was sooooo close I was able to
figure it out from there. Here's what worked:

TRANSFORM First(IIf([pf_td]=0,Null,[pf_td])) AS PFToDte
SELECT tblMonthly.job, Val(tblMonthly!act) AS Activity_ID,
Min(IIf([tblMonthly]![pcompl_td]=0,Null,[tblProjInfo]![period])) AS Start,
Max(IIf([tblMonthly]![pcompl_td]100,Null,[tblProjInfo]![period])) AS [End],
Last(tblMonthly.adesc) AS [Activity Title], Last(tblMonthly.whourbudg) AS
[Bud Hrs], Last(tblMonthly.whourerntd) AS [Ern Hrs],
Last(tblMonthly.whouracttd) AS [Act Hrs], Last(tblMonthly.whtot_td) AS
[TD$/Hr], Last(tblMonthly.pcompl_td) AS PCT
FROM tblMonthly LEFT JOIN tblProjInfo ON tblMonthly.period =
tblProjInfo.CMIS_Mo
WHERE (((Val([tblMonthly]![act])) Between 9000 And 9499 Or
(Val([tblMonthly]![act])) Between 10000 And 59999))
GROUP BY tblMonthly.job, Val(tblMonthly!act)
ORDER BY tblMonthly.job, Val(tblMonthly!act)
PIVOT tblProjInfo.Period;

What you gave me would count a zero value as a "min". I only wanted to
count the start of a value 0, so I convereded any zero values to null...
Seems to be working fine now.

Thanks for the help!


--
Thanks - John


"KARL DEWEY" wrote:

Try this --
TRANSFORM First(tblMonthly.pf_td) AS FirstOfpf_td
SELECT tblMonthly.Job, Val(tblMonthly!act) AS Activity_ID,
Min(tblMonthly.Period) AS Start, Max(tblMonthly.Period) AS [End],
Last(tblMonthly.adesc) AS [Activity Title], Last(tblMonthly.whourbudg) AS
[Bud Hrs], Last(tblMonthly.whourerntd) AS [Ern Hrs],
Last(tblMonthly.whouracttd) AS [Act Hrs], Last(tblMonthly.whtot_td) AS [TD
$/Hr]
FROM tblMonthly LEFT JOIN tblProjInfo ON tblMonthly.Period =
tblProjInfo.CMIS_Mo
WHERE (((Val([tblMonthly]![act])) Between 9000 And 9499 Or
(Val([tblMonthly]![act])) Between 10000 And 59999))
GROUP BY tblMonthly.Job, Val(tblMonthly!act)
ORDER BY tblMonthly.Job, Val(tblMonthly!act)
PIVOT tblProjInfo.Period;

--
KARL DEWEY
Build a little - Test a little


"John" wrote:

I did, see note in prior response... still doesn't work...
--
Thanks - John


"KARL DEWEY" wrote:

Try removing the Min(tblProjInfo.Period), Max(tblProjInfo.Period), from
the ORDER by and GROUP by sections.
--
KARL DEWEY
Build a little - Test a little


"John" wrote:

Karl,

I get the following errors:

1. Cannot have an aggrigate function in an ORDER by clause
2. Cannot have an aggrigate function in an GROUP by clause

when I remove the Min / Max from the order by and group by clauses I get "4"
in all the Start / End columns and no data in the grid area.

I may not be explaining myself too well. Here's a sample of what I
bwant/b the output to look like:

PERIODS (or dates)--
Cost Code|START| END| 1 | 2 | 3 | 4 |
18800 | 3 | |0.00|0.00|1.50|1.52|
20200 | 0 | 3 |0.91|0.95|1.01|0.00|
36300 | 1 | |1.20|1.22|1.25|1.27|
42500 | 2 | 3 |0.00|1.01|1.00|0.00|

Notice that the START and END fields don't give you he max of the value (PF
TD), but rather the start period and end period of when workd started and
ended on the code.

Hope this helps define the problem better...

--
Thanks - John


"KARL DEWEY" wrote:

Try this --
TRANSFORM First(tblMonthly.pf_td) AS FirstOfpf_td
SELECT tblMonthly.job, Min(tblProjInfo.Period) AS [Start],
Max(tblProjInfo.Period) AS [End], Val(tblMonthly!act) AS Activity_ID,
Last(tblMonthly.adesc) AS [Activity Title], Last(tblMonthly.whourbudg) AS
[Bud Hrs], Last(tblMonthly.whourerntd) AS [Ern Hrs],
Last(tblMonthly.whouracttd) AS [Act Hrs], Last(tblMonthly.whtot_td) AS [TD
$/Hr]
FROM tblMonthly LEFT JOIN tblProjInfo ON tblMonthly.period =
tblProjInfo.CMIS_Mo
WHERE (((Val([tblMonthly]![act])) Between 9000 And 9499 Or
(Val([tblMonthly]![act])) Between 10000 And 59999))
GROUP BY tblMonthly.job, Min(tblProjInfo.Period), Max(tblProjInfo.Period),
Val(tblMonthly!act)
ORDER BY tblMonthly.job, Min(tblProjInfo.Period), Max(tblProjInfo.Period),
Val(tblMonthly!act)
PIVOT tblProjInfo.Period;

--
KARL DEWEY
Build a little - Test a little


"John" wrote:

Karl - it's rather complex, (at least for me..) but here you go...

It's a cross tab query built from a select query. The select query puts the
codes in order and returns only the fields I need for the crosstab query.
The select query to do this is:

SELECT tblMonthly.job, tblActMap.JVID, tblJVTitles.JV_Title,
tblProjInfo.Period, Sum(tblMonthly.whourbudg) AS [Bud Hrs],
Sum(tblMonthly.whourerntd) AS [Ern Hrs], Sum(tblMonthly.whouracttd) AS [Act
Hrs], IIf([Act Hrs]=0 Or [Ern Hrs]=0,0,[Act Hrs]/[Ern Hrs]) AS [TD PF]
FROM ((tblMonthly LEFT JOIN tblProjInfo ON tblMonthly.period =
tblProjInfo.CMIS_Mo) LEFT JOIN tblActMap ON tblMonthly.act = tblActMap.Act)
LEFT JOIN tblJVTitles ON tblActMap.JVID = tblJVTitles.JVID
GROUP BY tblMonthly.job, tblActMap.JVID, tblJVTitles.JV_Title,
tblProjInfo.Period
HAVING (((Sum(tblMonthly.whourbudg))0))
ORDER BY tblMonthly.job, tblActMap.JVID, tblProjInfo.Period;

The tblProjInfo.Period field is the date field converted to an ordinal date
(1, 2, 3, etc.) If I remove the ordinal date option, it will be a YYYY-MM
formated date. The formatting is not important here. I just need to know
when people started working on the code (ordinal period, or actual date), and
when they finished. (this is a monthly report on a 5 year project, so we
will end up with 60 date columns on the xtab query.)

The xtab SQL is

TRANSFORM First(tblMonthly.pf_td) AS FirstOfpf_td
SELECT tblMonthly.job, Val(tblMonthly!act) AS Activity_ID,
Last(tblMonthly.adesc) AS [Activity Title], Last(tblMonthly.whourbudg) AS
[Bud Hrs], Last(tblMonthly.whourerntd) AS [Ern Hrs],
Last(tblMonthly.whouracttd) AS [Act Hrs], Last(tblMonthly.whtot_td) AS [TD
$/Hr]
FROM tblMonthly LEFT JOIN tblProjInfo ON tblMonthly.period =
tblProjInfo.CMIS_Mo
WHERE (((Val([tblMonthly]![act])) Between 9000 And 9499 Or
(Val([tblMonthly]![act])) Between 10000 And 59999))
GROUP BY tblMonthly.job, Val(tblMonthly!act)
ORDER BY tblMonthly.job, Val(tblMonthly!act)
PIVOT tblProjInfo.Period;

I wish I could upload a screen shot to show you.. As they say, a picture is
worth 1000 words!



--
Thanks - John


"KARL DEWEY" wrote:

I do not see why my solution would not do it for you. Post the SQL for your
query so I can edit and post back.
--
KARL DEWEY
Build a little - Test a little


"John" wrote:

Sorry - my data sample kind of misslead you. They do not always increase in
value. It could go up or down. What I really need is the first occurance of
a non-zero value and (if the percent complete is 100) the last occurance of a
value before zero value... So for example:

0, 0, 0, .95, 1.05, 1.00, .95, .96, 1.2, 0, 0, 0, 0... etc.

should return the date that corresponds to the ".95", and the date that
corresponds to "1.2". (This code would be 100% complete.)

Thanks for the prompt reply...

--
Thanks - John


"KARL DEWEY" wrote:

Why not add two row headings - Start: Min([YourDateField])
- End Max([YourDateField])

--
KARL DEWEY
Build a little - Test a little


"John" wrote:

I have a crosstab query with YYYY-MM as column headings and cost codes as row
headings. It looks something like:

Cost Code, 2008-01, 2008-02, 2008-03, 2008-04...
18800 0 0 1.50 1.52
20200 0 0.91 0.95 1.01
36300 1.20 1.22 1.25 1.27
42500 .90 1.01 1.00 0

The values are various types of things (quantities, production factors,
cost, etc.). What I would like to do is add two columns as headings next to
the cost code for Start (YYYY-MM), and End (YYYY-MM if 100%). I am having a
little trouble calculating the first month that actually has a value in it.
Like cost code 18800 should have a start of 2008-03, and a blank end date
because it's not finished, and cost code 42500 should have a start date of
2008-01 an an end date of 2008-03 because it's finished. (The table has a
percent complete field so knowing it's 100% is rather easy.

Any suggestions would be appreciated...

--
Thanks - John