Month date offset query

C

chemicals

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.

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
 
P

pietlinden

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.

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

first things first. Wrong structure.
It should be:
(Project, Event, Date, Quantity)
and not a repeating field of Quantity or whatever that repeating
number is.
 
J

Jerry Whittle

It's because you are using Access as a spreadsheet and not a database. It's
like using a hammer instead of a screwdriver to insert a screw.

How do I know this? It's your 48 columns of Months. Your table has serious
normalization problems and just will NOT work correctly in a relational
database. Period. Instead of 48 across, there probably should be just one
column of Months down and maybe a table or two linked to it.

I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.
 
C

chemicals

I agree. It is de-normalized because it sits in a data warehouse.
Unfortunately I did not create the table...

Do you think creating a new table structure and moving the data to that
would be a better first step?

Normalizing it would give me 48+ rows for each Phase - (of which there are
7). Depending on the Project each "Month" column has a different value in
it. Also for each Project there are 7 different Phases...
 
J

Jerry Whittle

Data warehouses are often de-normalized. Not your fault then!

I'd test it by creating a huge UNION ALL query which would join the 48
columns. If there aren't too many records, you might get acceptable
performance without the trouble of importing fresh data into a new table
frequently.

Use that query as the basis for a select query that excludes the previous
dates with something like the DateAdd function as part of the criteria.

Then use that second query as the record source for a crosstab query.

It will be pretty complicated. If you have performance or memory issues, you
may need to import into a table as an intermediate step.
 
B

Bob Barrows [MVP]

chemicals said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top