Need Query based on the current month

[Guest]

Is it possible to generate a query that would change depending on the current
month and include the next 11 months after that???

I have a table with months (i.e. Feb-05, Mar-05 ... Mar-08). Each of these
months are manpower requirements for that month. Is it possible for me to
generate a query that is based upon the current date (month and year) and
give me the following 12 months from that point ?????

I can't seem figure it out if its possible. I know I could do this using
vba code where I fill in a table of 12 months with the correct start months.

Your help is deeply appreciated!!

Thanks,

Gary

 

[Tom Ellison]

Dear Gary:

You could have a table with the values 0 through 12 in it. Using a
Cartesian product and having DateAdd add the associated number of months to
your base date, you'd have 13 rows with all these values.

If you require details to do this, please provide full details of what you
have now as a starting point.

Tom Ellison

 

[Guest]

Is the actula format of the date field returning "Feb-05"? What data type is
it?
We need to know that to define the criteria for you. Or, are you saying you
have fields named Feb-05, etc. that contains the manpower requirements?

Please post back with more info.

 

[Guest]

I still need help here.

This is what my actual data is arranged in the table:

There are many columns or fields to the table. The first column has the
project name, the following columns has the field name of Aug-05, Sept-05,
Oct-05, Nov-05, Dec-05, Jan-06, Feb-06, Mar-06, Apr-06, May-06, etc.

The data in each field is a real number (of type Single). For example, in
project xxx, Jan-06 I have 5.5, Feb-06, I have 7.0, etc. The 5.5 in
Jan-06 is the number people allocated to project xxsx, and in Feb-06, I have
7 people for that.

I am given a table for the projected during of the project and for other
projects. So I wanted to generate a query that returns the project name with
it man power for the Current Month (which I can get from the Date() function)
and get the other 11 months after this too. I want my Query to display all
of the projects with 12 columns or fields of Manpower starting with the
current month. So when we are in May, the current month will now be May and
I will show May-6 to April-07


I hope this explain what I need.


Thank You!!!!

Gary

 

[John Vinson]

I still need help here.

This is what my actual data is arranged in the table:

There are many columns or fields to the table. The first column has the
project name, the following columns has the field name of Aug-05, Sept-05,
Oct-05, Nov-05, Dec-05, Jan-06, Feb-06, Mar-06, Apr-06, May-06, etc.

This is not a relational table. This is a spreadsheet! Your table
structure *is simply incorrect* - you're storing data in fieldnames.

You'll have a LOT better luck if you normalize the data (into a form
that Klatuu was apparently assuming).

The data in each field is a real number (of type Single). For example, in
project xxx, Jan-06 I have 5.5, Feb-06, I have 7.0, etc. The 5.5 in
Jan-06 is the number people allocated to project xxsx, and in Feb-06, I have
7 people for that.

A much, much better design would have a table of Projects, with one
record per project; and a table of Projections, with a date/time field
for the first of each month, the ProjectID, and your single number.

I am given a table for the projected during of the project and for other
projects. So I wanted to generate a query that returns the project name with
it man power for the Current Month (which I can get from the Date() function)
and get the other 11 months after this too. I want my Query to display all
of the projects with 12 columns or fields of Manpower starting with the
current month. So when we are in May, the current month will now be May and
I will show May-6 to April-07

A Crosstab query based on the normalized table structure will do this
for you.

John W. Vinson[MVP]

 

[Guest]

This is not a relational table. This is a spreadsheet! Your table
structure *is simply incorrect* - you're storing data in fieldnames.

You'll have a LOT better luck if you normalize the data (into a form
that Klatuu was apparently assuming).


A much, much better design would have a table of Projects, with one
record per project; and a table of Projections, with a date/time field
for the first of each month, the ProjectID, and your single number.


A Crosstab query based on the normalized table structure will do this
for you.

John W. Vinson[MVP]

 

[Guest]

I am still a little lost. I tried some of the suggestions and I still have
some questions:

1) I make a new table which contains only projects.

2) I make a second table which has the projects and misc fields like
Date/Time, ProjectID, manpower requiremenet etc.

I was clost with the CrossTabl Query that was suggested. I never used one
of those and need to answers on the cnfiguration of it.

The second table (projections) seems like I need the program filled in along
with different Months until that project is completed? Is this correct.
That means I have to fill down the column of the project.


What and how would you use the Crosstab query??? I never used one or
configure one. All I know is that it switched the column with the rows.


Can you help!!!

Thanks,


Gary