Back Again! - Auto populate all records?

[John Jnr]

I have searched the forum and there is a couple of threads that describe how to populate a single record with a combo!
My question is this:

I want the user to enter a year on a front form (2002). Then open form and that form is based on a previous table (exactly) but the enetered year is enetered into every record of the field PLAN_YEAR. This is a budget DB. That previous table will have last year (2001) in the PLAN_YEAR field. So the new year (2002) will have to over-write last year (2001) in the PLAN_YEAR field.

I hope this is clear?!?!?

Thanks

John

 

[John Jnr]

Is this a design problem?

This is giving me a headache and I think it may be a design problem!

What I am trying to do: Create a Financial Budget Plan DB

Overview

Approx 540 projects carried out per year.
These are set with costs and form the financial plan over a four to five year spread.
At the end of that year a new plan has to be formulated.
This new plan needs the option to be based on the previous plan but for the following year.
Also the new and changing plan needs to be compared against the existing plan to measure variance against the plan.

I seem to be going around in circles. Does anyone have a plan for such a DB, just the format and relationships would do?


Thanks

John

 

[cogent1]

answering your first post, what are you basically trying to do? It seems you are trying to copy data from one table into another and alter the date in one of the fields. But it is extemely unlikely that you would want to replicate the data as well. Surely you only want to copy the structure? And consider this - do you really need to create a new table, if the plans are based on the same factors ? You can use queries to extract the data selectively.

 

[John Jnr]

Cogent1,
During the budget process the plan at Jan 1 becomes fixed. These projects in the budget are reviewed quarterly. The project costs can change. I want to be able to track these changes. The only way I can do that is if the amended plan is saved to another table? Then compare the amended table with the original plan table to measure variance.
Also come the new budget process which becomes fixed. I would like to base that plan on the previous plan. This way I have history of previous plans. In doing this I would want to auto populate the Plan_Year field with the new plan year?

I hope this makes sense and is logical or am I missing the plot in terms of structure/design?

As ever, thanks

John

 

[cogent1]

It sounds as if these tables are financial scenarios. So my next question is: Why not do this in Excel, which is better suited to handle this type of thing?

 

[John Jnr]

It's done in Excel but the plan has to be sent out to 50 plus engineers who have very limited ability in Excel. I have been tasked in making this idiot proof (I hope you are not an engineer?). If the engineers could update their project via Access all the data would be collated for analysis within the DB. There would be no mistakes from one amendment to another. I favoured an Excel approach but I wouldn't know where to start in terms of making it idiot proof using entry forms. Then send these forms out to the engineers to amend, that automatically copied this data to a master file?

I will have a look on the Excel forum unless you have any other suggestions?

Thanks

John

 

[cogent1]

Well, I really don't think Access would do this job as readily as Excel could. But perhaps you should take advice from more than one source!

Don't forget that with Excel you can send only those parts of your scenario to your idiots (sorry, engineers),which are appropriate to their project. The results can be checked and then pasted back into your main table to overwrite existing data or stored as separate work sheets which can be melded together to yield a summary report. This does not require a particularly intimate knowledge of Excel.

As to being idiot-proof, whichever system you use some idiot is going to be cleverer than you are in defeating whatever checks and restrictions you impose. I don't think Access will be better than Excel in that respect.

 

[Pat Hartman]

This can be done with an append query that takes a parameter. The parameter is used in two places so make sure that it is spelled identically.

1. In the QBE grid add the table that contains the budget data. Select all the columns (don't use the asterisk).
2. In the criteria field for the Year, put:
Forms!YourFormName!YourBudgetYear - 1
3. Uncheck the show box
4. Change the query type to append. Choose the budget table.
5. Add a new column. In its Field cell put:
Forms!YourFormName!YourBudgetYear and choose the year field in the append to cell.

So, in summary, the query selects a set of records for the year prior to the budget year and inserts them after changing their year field.

Of course this process assumes that there is at least 1 year's worth of data in the table.

 

[John Jnr]

Sorry Pat for not replying sooner. I will give your solution a try and let you know if I have any problems.
It's not that I have any problems with the Excel solution in terms of my Excel ability but I would like a dynamic solution that Access should be able to deliver.

Ps. How's the book coming on? Oh sorry I shouldn't have mentioned that probably?!?!??!