12 month's of data

[thart21]

I'm looking for a starting point for this query.

I currently have a table "tblMonth" with the fields: product, year and each month (Jan, Feb, Mar, Apr, etc.)

I enter month volumes into this table which is used to calculate savings amounts. Currently I create queries that show the month data for each month for 2006. Each month is a column header and I just have 2006 in the "Year" column at this point.

I now need to create another one that will show data for the current month plus the next 11 months, August 2006 through July 2007. In my tblMonth table I just add the product again for the year 2007 and enter the data so I will have two rows of data for each product.
How can I get my query to show 2007 data as well with the column headings beginning at Aug and ending at July 2007?

I can create the data but Jan 2006 and Jan 2007 are in the same column.

I'm sure it's easy, just can't wrap my brain around the concept yet.

Thanks

 

[CEH]

How bout seperating "Year" out into it's own table. Might be a little late now.... But I would have done the same with a "Months" table. OR simply used a date field.

 

[grnzbra]

How about eliminating the individal months and have one month field and 12 records for each year.

 

[pbaldy]

Search here on normalization. What you've got there is a spreadsheet, and getting data out of it will be difficult at best, as you're discovering. The proper design would be a record for each month, which would be easy to query any given time frame.

 

[thart21]

So are you saying that I should alter my table and have my product names as my fields and the records/rows as the month/years?

Each time I add a new product I would have to add a field to my table.

Now

Product Year Jan Feb Mar Apr May...

Suggestion (?)

Month Year Prod1 Prod2 Prod3 Prod4 Prod5...

Thanks

Toni

 

[grnzbra]

No, you would add an new set of records

Your table would be something like

product, year, month, field x

in which whatever currently goes into the month field would go into field x.

If you want to look at a date range, you could create a query with a calculated field which concatinates the month number with /1/ and the year number to create a date. Then, selecting any date range would be simple. (Perhaps the end of month would be better than the first of the month)

 

[thart21]

Thanks for the help, I will try your solution.

 

[thart21]

I created the table as suggested and was able to create my query to show the monthly amounts for every project in my database. I now need to be able to do some kind of Date function to allow the user to select a range of months to view. I think I'm going to run into problems since I'm not actually using a "Date" in my database. It is a text field "Month" and a text field "Year". Is this going to keep me from using the DateAdd function?

 

[grnzbra]

You would have to convert the text date to a date field to use DateAdd. If all you want to do is select months, have them enter the month and year for the start and end dates, convert them to yyyymm, convert the dates to yyyymm and and set the criteria for between the two numbers. So July 6, 2006 would become 200607 which is between June 1,2006 (200606) and Aug 1, 2006 (200608)

 

[thart21]

Thanks. Due to other change requests, I need to basically re-write my database anyway, would it be easier, smarter to just change my field names to read 1/1/06, 2/1/06, etc instead of having the Month and Year text fields?

 

[grnzbra]

If you go to a normal date, you can use DateAdd. However, what you do may also depend on how receptive the users are to change; usually they aren't. However they enter dates now, you can always use code to force it into a normal date format and store that value. If they are used to entering or selecting only month and year or if they are used to selecting the monnth name rather than its number, you must convert it to a normal date. If they don't actally enter a day of the month, you will have to force one in.

 

[thart21]

Myself and my manager are really the only ones who use the db, so if it is the more correct way to do it, I will make this change.

Thanks for all of your help.

 

[grnzbra]

Don't think in terms of "correct". In a regular date format, you can do a lot of stuff so it would be more accurate to say that it was "better". Either way would work.

 

[boblarson]

This scares me:

change my field names to read 1/1/06, 2/1/06,

This makes me think that you are creating individual fields for each month. This would be bad database design. You should look into the posts on normalization. As a sample, tables should be like this:

Product Table
productID
ProductCode
ProductName
ProductPrice
ProductPriceEffectiveDate

Customer Table
CustomerID
CustomerName
CustomerAddress
CustomerCity
CustomerState
CustomerZip
etc.

Orders
OrderID
OrderDate
CustomerID

OrderDetail
OrderDetailID
OrderID
ProductCode
ProductQty


From this, a customer is tied to an order, which is tied to the details of the order, which is tied to the product and the product has an effective date of the price, so if the price changes you can keep history and add the product again with a new effective date and then you set it so the order is set to use the price at the effective date.

Using a separate column for each month/year will make it VERY, VERY difficult for you as you will have way more maintenance, the data cannot be tied together very easily, and aggregations are very difficult to do.

 

[thart21]

My tables are not set up that way, it's the queries that I have to create that way. The database is much more complicated than just calculating a one-time price. I have to be able to show the individual savings on a project for every single month from the effective date on. Each month will be a different total for each project. The calculation is [monthlyvolume] * [costreduction]. It has to be a separate calcuation for each project for each month. For example, Project1 has an effective date of 8/1/2006 and a cost reduction of $.50. There is a volume in my volumes table for that specific product tied to that project - Aug06-100,Sep06-300,Oct06 400,Nov 500,Dec06 100, Jan07-100,.....

I have to show what the calculated savings is in my query for that project for the month of August, then Sept, then Oct, etc.

Aug06 Sep06 Oct06 Nov06 Dec06 Jan 07
Project1 $50 $150 $200 $250 $50 $50

I'm trying to figure out an easier way than creating a separate field in my query for each month-Here is what I have now for each month but, now that I need to go into 2007 as well, this won't work.
Jan: (IIf(1>=[cutinmonthnmbr] And IsNull([reduction]),([currentprice]-[newprice])*[1],(IIf(1>=[cutinmonthnmbr],[reduction]*[1]))))
I'm hoping to just be able to use the new effdate field (1/1/2006), which replaced my previous 2 text fields of Month and Year, as the basis for my calculations and eliminate the cutinmonthnmbr completely.

Am I making this harder than it is? Thanks for everyone's help so far.

 

[grnzbra]

You would need to get to a table or query that has a project field, date field and savings field. Once you've got that, you can run it into a crosstab query to get it into the format you want, ie on row for each project and one column for each month's savings.

 

[thart21]

This will be my first attempt at this, but is a good way to do this to create a function that will loop through the dates/volumes and do the calculation in one query field rather than the need to have the calculation I was using for each and every month? Then I can use my crosstab query which I already have.

Jan06: (IIf([tblProjectDetails].[effdate]>=[qryVolumesMonth].[effdate] And IsNull([reduction]),([currentprice]-[newprice])*[qryVolumesMonth].[volume]))

Toni

 

[thart21]

grnzbra, thanks for the help - still struggling with this. When you say to get to a table or query with a project field, date field and savings field, this will get me ONE calculation for the effective month of the project, right? I have tried everything I've seen and can come up with but I don't think I'm going to be able to get around creating fields in my query for each individual monthly calculation for each project. I've attached a sample of what I am trying to accomplish-would you mind looking at it and letting me know if you agree with me as far as not being able to get all of the monthly calculations in one field?

Thanks for your time-I really appreciate it.

 

[grnzbra]

OK you still use a 3 field table.

ProjectID, Date, Amount.

When you run the query, the sql would be something like

SELECT ProjectID, Date, IIf(Month(Date) = 1 AND Year(Date) = 06, Amount) AS "Jan06", IIf(Month(Date) = 2 AND Year(Date) = 06, Amount) AS "Feb06" etc
FROM Table

You might be able to do the same thing with a crosstab query.

 

[thart21]

Thanks for sticking with me on this one - it's finally making sense. I have one query field and have started my multiple IIf statements. I am getting this message:

"..expression contains invalid syntax, or you need to enclose text data in quotes". The cursor moves to the comma just before my second IIf statement

Savings:IIf(#1/1/2006#>=[cutindate] And IsNull([reduction]),([currentprice]-[newprice])*[tblVolumes].[volume],[reduction]*[tblVolumes].[volume]),IIf(#2/1/2006#>=[cutindate] And IsNull([reduction]),([currentprice]-[newprice])*[tblVolumes].[volume]),[reduction]*[tblVolumes].[volume])

I've tried adding/removing parentheses and nothing seems to make it work.

Any ideas?

Thanks