Problem with subform

[pser]

Hi i have a challenge with a form included a subform. Subform is based on a query which show the column last year and current year and some other stuff from the table person. A user can click on a button which will insert a new column in the table person, the new columname would then be Year2003.
Then i need to update query, i.e. last year should be set to 2002 and current year to 2003. What i do is i change the sql statment, put it in the recordsource of the subform and then do a requery on the subform.
But it won't work! If i check the recordsource everything looks fine, but the subform doesn't update.
Anyone have idea of what to do

 

[Jimbob]

maybe because the subform won't automatically pick up the new column. I think you would have to go into design view and add that column to your subform

 

[pser]

That would work, but i want this to go automatic. It's going to be used by a user which is not familiar with Access at all.

Maybe you have other suggestion to solve this issue. It could be that it not should be solved with subform. But i have some column with calculation depending on what user type, after being typed it's put back in the person table.

Pse

 

[David R]

I have a suggestion, but you may not like it.

Your database is not normalized. It should not require a recode every time the year flips over.

Instead of fields Year2001, Year2002, Year2003, etc, have a field: [FiscalYear] (or whatever). The values will then be 2001, 2002, 2003, etc.
This may require some reworking of your data structure initially, but you will be MUCH happier with the results in the long run. Think about all the hundreds/thousands of blank fields you're leaving now, that can be eliminated. If any one record can only ever have one Year, then you won't even need a new table.

If you need help, look up some webpages/articles/past topics on database normalization. If you need specific help, please post back here.

Good luck,
David R (spent 8 months reworking & fighting with an unnormalized database)

 

[pser]

Nice tip, i've already consider this. But the fact is that this is a system to look at development of yearly income for a manager in a company. Let say this company have 300 employees and you want to register income and general/personal raise every year for each person. Then you want to generate reports based on individual, department ... to look at salary development. I didn't find any good way to solved it.

Pser

 

[David R]

Since you will need year-after-year data, you'll need an additional table, but it will be small.

Your year-specific data, like income, raises, etc, should be stored in a separate table (we'll call it tablePersonsMoney). This new table will have the following fields:
MoneyID: An autonumber primary key, just to identify the record.
PersonID: A field of the same type as the PK from your tablePersons. If the PK is autonumber, make it Long Integer. If it's Text, make this one Text too.
YearField: 4 characters wide, text. Default value can be set as =Year(Date()) but that is not required.
Other fields: Your fields related to Salary, GeneralRaise, PersonalRaise, etc, can go here. However if GeneralRaise is across-the-board for all employees it doesn't need to be stored here.

Now create a link between this table and your tablePersons in the Relationships window, and start rebuilding your code.

Your queries on salary data can use both tables as their source, and the linkage will connect the right data together. Crosstab queries sound like they would be useful to you, look them up in Access Help.

This is the ONLY sensible way to structure this database, I'm sorry you've gotten so far into it already, but I really am coming from the voice of painful experience here.

Again, read up on database normalization as you probably have other areas that can be tweaked while you're doing this. Might as well do it all at once.

 

[pser]

If i understand you correct in the table personmoney i would get the number of employees multiplied for every year i register. So if this company have 350 employees and they want to register for let say 10 year i would get 3500 record in that table?

Pser

 

[David R]

That's correct. Because one employee can have many years of records, you have to have a one-to-many relationship between the two tables.

As long as it is defined in the Relationship screen or in your queries, you will only get those records on the subform corresponding to the main form.
If you haven't taken a class on Access or read anything on relational database design I strongly suggest you do so. There are webpages out there, not to mention books and articles on the Microsoft Support page. http://support.microsoft.com/default.aspx?scid=kb;en-us;Q100139

[This message has been edited by David R (edited 05-21-2002).]

 

[pser]

Ok. I was considering this when i start looking into this database development, but i tried to see if there where other ways od solving it. But you're right, it's important to keep the database normalize.

Thanks David R.

Pser