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 said:
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
:
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
:
Why not add two row headings - Start: Min([YourDateField])
- End Max([YourDateField])
--
KARL DEWEY
Build a little - Test a little
:
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...
