If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Find the first value in a crosstab query
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 |
#2
|
|||
|
|||
Find the first value in a crosstab query
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 |
#3
|
|||
|
|||
Find the first value in a crosstab query
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 |
#4
|
|||
|
|||
Find the first value in a crosstab query
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 |
#5
|
|||
|
|||
Find the first value in a crosstab query
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 |
#6
|
|||
|
|||
Find the first value in a crosstab query
One more thing... My uderstanding of Max() and Min() functions is the largest
and smallest of the data, not the field value of the first occurance, and last occurance... -- Thanks - John "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 |
#7
|
|||
|
|||
Find the first value in a crosstab query
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 |
#8
|
|||
|
|||
Find the first value in a crosstab query
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 |
#9
|
|||
|
|||
Find the first value in a crosstab query
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 |
#10
|
|||
|
|||
Find the first value in a crosstab query
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 |
|
Thread Tools | |
Display Modes | |
|
|