Re: Month date offset query

Tech-Archive recommends: Fix windows errors by optimizing your registry

chemicals wrote:
I have a table that contains:
Project, Phase, StartDate, Month1, Month2, Month3,...Month48

Ecah Month column contains the number of hours spent in that month on
the project. The Month columns are not dates.

Does "Month1" mean "Jan2008" or "FirstMonthOfProjectExistence"?

I am trying to query the projects so that I can align all of the
columns of hours based on the current month.

For example is the data was:
Proj A, Build, 2/08, 5,10,15,20,...240
Proj B, Build, 5/08, 1,3,5,7,...97
Proj C, Build,11/07, 2,4,6,8,...96

The output would be:
Proj A, Build, 2/08, 30,35,40,45,...240 (skips 1st 5 months)
Proj B, Build, 5/08, 5,7,9,11,...97 (skips 1st 2 months)
Proj C, Build,11/07, 20,22,24,26,...96 (skips 1st 9 months)

I am having trouble getting my hands around this...

Thanks

Outside of the discussion about the design, I'm having a little trouble
understanding the requirements. Let's see if I have it straight:
"Month1" does mean "FirstMonthOfProjectExistence", correct? So Month1 of
ProjA contains data for Feb2008 of ProjA, and Month1 of ProjB contains data
for May2008? ... yes, it is now making sense ... for starters, you need to
relate the MonthX columns to actual Date/Time values.

Let's assume the StartDate is an actual Date/Time column (if it isn't you
will need to add the necessary step to convert the data in that column to
Date/Time to what I am proposing). At the same time, you will need to
normalize the data with a union query:

Select Project, Phase, StartDate,
DateSerial(year(StartDate),month(startdate),1) As ReportingMonth
[Month1] As HoursUsed From TableName
Union All
Select Project, Phase, StartDate,
DateSerial(year(StartDate),month(startdate)+1,1) As ReportingMonth
[Month2] As HoursUsed From TableName
Union All
Select Project, Phase, StartDate,
DateSerial(year(StartDate),month(startdate)+2,1) As ReportingMonth
[Month3] As HoursUsed From TableName
....

Union All
Select Project, Phase, StartDate,
DateSerial(year(StartDate),month(startdate)+47,1) As ReportingMonth
[Month48] As HoursUsed From TableName

Save the above as NormalizedData (or whatever you like) and then, to select
the data, it's simply a matter of:

Select Project, Phase, StartDate,ReportingMonth,HoursUsed
From NormalizedData
WHERE ReportingMonth >= DateSerial(Year(Date),Month(Date),1)

Save that query as CurrentMonthHours (or whatever name you prefer), and then
use it as the source "table" for the crosstab query wizard.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

Relevant Pages

  • Re: UPDATE TABLE FROM UNBOUND QUERY
    ... This buttons run the TestQuery and I can see the results. ... results of the query, I want to update the main data tale with Moved = Yes, ... Rather than trying to construct the SQL, use the Design view of the query ... AddTables --> TestQuery, Tablename ...
    (microsoft.public.access.modulesdaovba)
  • Re: Please help with a Query
    ... The query with the MIN would replace the query you were to add the HAVING ... SELECT studentID, MIN ... FROM tableName AS a INNER JOIN tableName AS b ... Since a rank of 1 is the minimum, you could do it simpler and faster ...
    (microsoft.public.access.queries)
  • Re: How do I count equal values as one?
    ... DCount is a function in ACCESS VBA that can be called from a query. ... Select Tools | References from the menu bar. ... > you mean TableName = the same tablename in both queries. ... >> SELECT idPlace AS P, ...
    (microsoft.public.access.queries)
  • Re: Totals Query
    ... Better to do it in multiple queries. ... FROM tableName As a INNER JOIN q2 ... with the LATEST EventDate of the BIGGEST FitID number for EACH Style ... I want to just tell the query to find ...
    (microsoft.public.access.queries)