Which month fields to update

[merry_fay]

Hi,

I've got a form where the user enters a system, forecast, the month it applies from & the year.

This will then populate a table with the Year under a Year column header & use the month to choose which column to put the forecast in. I then want to get it to update so all the future months will update to that number too

Eg This:
System Year Jan Feb Mar etc Nov Dec
A 2012 1 1 1 1 1
Becomes this:
System Year Jan Feb Mar etc Nov Dec
A 2012 1 2 2 2 2

The change could also be a reduction or down to 0.

I'm really trying to think of a simpler way then a list of update queries (if Mth =Feb then update Feb..... Dec Else If etc etc) but haven't come up with anything yet. Any suggestions?

Thanks

 

[John Big Booty]

Why not simply store the date in one field rather than breaking it down into parts (which is a violation of the rules of data normalisation), you can then use the DatePart() function to break that date apart for the month and year for display purposes, this will greatly simplify all your processes.

 

[merry_fay]

I've managed to find a way around it. I'm using a link table with a column for Month then 12 Jan-Dec columns with 0 or 1 depending on which needs to be applied.

The table I'm working with here isn't a table for general viewing, it's a table to hold data which will be applied to other tables -some of which are supplied & hence imported in a Jan-Dec format.

 

[John Big Booty]

...

The table I'm working with here isn't a table for general viewing, it's a table to hold data which will be applied to other tables -some of which are supplied & hence imported in a Jan-Dec format.

In general no table (or it's data) should be directly accessed by the User. The user should always interact with a table and it's data through a Form, in this way you (the developer) can control the manner in which the user interact with that data, and how and what he is able to see of that data.

 

[merry_fay]

In general no table (or it's data) should be directly accessed by the User. The user should always interact with a table and it's data through a Form, in this way you (the developer) can control the manner in which the user interact with that data, and how and what he is able to see of that data.

I know & that's how it works....
That's not what my question was though, I'm trying to build the working's into the form so the user can enter the data into the form & then what needs to happen, happens without them having touch the tables directly at all.
Regardless of whether I have a column for 'month' & only 1 column for the value or I use 12 columns for each month, if the user chooses eg Mar, how do I get the table to update all the Apr-Dec data without have to write If statements for each potential month selection?

 

[Galaxiom]

The problem arises from your denormalized data and you will continue to have problems while you persist with this structure. Having data supplied in a denormalized format is no reason to adopt that structure for your whole database.

Updating multiple fields with repetitive data is a sure sign of normalization issues.

Normalized data would store the value just once in a record against a date in March. The data model would apply that same value to supsequent periods until a later record supplanted it for those periods.

 

[vbaInet]

merry_fay,

For good relational database design you shouldn't worry about the form until you've structured your tables correctly. If your tables have been properly normalised then you can do pretty much anything on a form in tandem with a query.

Your table is currently this:

System  |  Year  |   Jan   |   Feb  |   ...   |  Dec
==================================
A            2012      1           1       ........    1

But when normalised it could be:

System  |  DateField   |    ValueField
============================
A             01/[COLOR=Red]01[/COLOR]/[COLOR=Blue]2011       [/COLOR]1
A             15/[COLOR=Red]02[/COLOR]/[COLOR=Blue]2011[/COLOR]       1
A             10/[COLOR=Red]12[/COLOR]/[COLOR=Blue]2011     [/COLOR]  1
.
.
. 
etc

You can set the Default Value of the DateField to =Date() so it auto-completes for new entries. The day part of the date will be redundant but it's needed in a Date/Time data type.

If you want to update February 2011 your query will look like this:

UPDATE [I]table[/I]
SET [I]ValueField = 2[/I]
WHERE  [I]Format([DateField], "mm/yyyy") = 02/2011)[/I];

Edit: So slow today

 

[merry_fay]

Structure:

Table A has no dates whatso-ever. It is a continually moving input table (all changes/inputs made via a form!)

Table B is the 'output' data -this is where in eg Jan 2011, a fixed, will never change Jan 2011 using Table A is written. Other inputs from other sources (including the imported Jan-Dec table) will be collated with this. Feb 2011 onwards will use the same values as Jan 2011 but also apply changes from Table C.
In Feb 2011, Feb 2011 will be written from the latest Table A & other inputs, & all future months the same with the latest list of changes from Table C then applied etc etc

Table C has changes to be applied. Eg in March 2011, one particular row (eg system A) might need to have 5 added, this will need to also be added for all months after
this too -there will be lots of different one's of these all to be applied in different months/years. Each system will only have 1 change per year though.

For Table B, we're looking at about 30,000 rows per year, starting with 4 years data & adding 1 year on each year.
While it's unlikely, it is possible that every historical month for each system could have a different value. Forecast will only change when there are entries in Table C.

I'm struggling to equate your suggestions to this.

 

[John Big Booty]

I really think that you need to read up on and become familiar with the concept of Data Normalisation. Perhaps this tutorial will help you get your head around the concept.