Help

[John C]

I need to find a way to update feilds in a form based off of three combo
box's. I have been told that I need to run a query to accomplish this. Is
this true? What I need to happen is on my form I need to be able to Pick a 4
digit year then pick a month then choose a name. Once all these have been
chosen I need to have feilds on the form autopopulate with data that was
inputed by the user into another table. I have no clue how to do this.

 

[John Vinson]

I need to find a way to update feilds in a form based off of three combo
box's. I have been told that I need to run a query to accomplish this. Is
this true? What I need to happen is on my form I need to be able to Pick a 4
digit year then pick a month then choose a name. Once all these have been
chosen I need to have feilds on the form autopopulate with data that was
inputed by the user into another table. I have no clue how to do this.

We don't either, because we have no idea what your table structure
might be; what tables you have; or what information you want to
"autopopulate". You can see your database. We cannot.

It SOUNDS like - and I'm guessing in the dark here - that you want to
use the combo boxes to *search* for a record in a table, and then
display the data in the found record on a Form. Is that correct?

If so, could you describe this table? What fields are you trying to
search?

John W. Vinson[MVP]

 

[John C]

I have a table called Fiscal year. In this table I have fields for "Fiscal
Year" "month" and "Name". It also has fields for what was budgeted at each
location. What I need to do is get the information from this table called
Fiscal year" into a Table called VS. I have a form set up to enter data into
VS. What I would like to do is have 3 combo box's one for Fiscal Year one
for month and one for name. When the user select the Fiscal Year and Month
and Name in there CBO's I would like the values that are in the budget fields
to autopopulate fields on the form for the table VS.

 

[John Vinson]

I have a table called Fiscal year. In this table I have fields for "Fiscal
Year" "month" and "Name". It also has fields for what was budgeted at each
location.

Ok... that's fine... is "Name" the name of the location? so the table
has four fields?

Do note that Month and Name are reserved words, and should be avoided
as fieldnames. You might want to consider changing these fieldnames to
(say) BudgetMonth and LocName.

What I need to do is get the information from this table called
Fiscal year" into a Table called VS.

Ummm... I doubt that you do need or want to do this.

Storing data redundantly is ESSENTIALLY NEVER a good idea. Could you
explain the structure of VS, and why you feel it necessary to store
the same budget number in two different tables, realizing that this
leaves you wide open to update anomalies? Say you do store the data
into VS, and then someone comes along and edits the budget amount in
the [Fiscal Year] table. You now have two budget figures; they're
different; and you have NO WAY ON EARTH to tell by looking at the data
which one is right. Is this a satisfactory situation for you?

I have a form set up to enter data into
VS. What I would like to do is have 3 combo box's one for Fiscal Year one
for month and one for name. When the user select the Fiscal Year and Month
and Name in there CBO's I would like the values that are in the budget fields
to autopopulate fields on the form for the table VS.

As opposed to creating a Query joining VS to [Fiscal Year], joining by
[Fiscal Year], [Month], and [Name]? That leaves the budget figures in
the budget table - where they belong - linked to the other field
values in VS.

If you REALLY want to do this - and, as you can gather, I don't think
that you do! - post back; it nees some VBA code but it can be done. An
Update query might be better (rather than using a Form), depending on
the context, however.

John W. Vinson[MVP]

 

[John C]

The " Name" field is the name of the doctors I support. The "VS" forms is
set up so the user can put in the actually patients seen at our different
sites each month. I orginally did not want to put the budget figures on this
form but "those that are in power" wanted that way. I agree with you it
would make it much simpler to administer to only have the budget figures in
one place. The tabel "Fiscal Year" actually has about 20 fields. One field
for each of our locations. I need to try and keep the data input as simple
as possible due to the user that will be putting these figures in is not that
comfortable on a computer.

I have a table called Fiscal year. In this table I have fields for "Fiscal
Year" "month" and "Name". It also has fields for what was budgeted at each
location.

Ok... that's fine... is "Name" the name of the location? so the table
has four fields?

Do note that Month and Name are reserved words, and should be avoided
as fieldnames. You might want to consider changing these fieldnames to
(say) BudgetMonth and LocName.

What I need to do is get the information from this table called
Fiscal year" into a Table called VS.

Ummm... I doubt that you do need or want to do this.

Storing data redundantly is ESSENTIALLY NEVER a good idea. Could you
explain the structure of VS, and why you feel it necessary to store
the same budget number in two different tables, realizing that this
leaves you wide open to update anomalies? Say you do store the data
into VS, and then someone comes along and edits the budget amount in
the [Fiscal Year] table. You now have two budget figures; they're
different; and you have NO WAY ON EARTH to tell by looking at the data
which one is right. Is this a satisfactory situation for you?

I have a form set up to enter data into
VS. What I would like to do is have 3 combo box's one for Fiscal Year one
for month and one for name. When the user select the Fiscal Year and Month
and Name in there CBO's I would like the values that are in the budget fields
to autopopulate fields on the form for the table VS.

As opposed to creating a Query joining VS to [Fiscal Year], joining by
[Fiscal Year], [Month], and [Name]? That leaves the budget figures in
the budget table - where they belong - linked to the other field
values in VS.

If you REALLY want to do this - and, as you can gather, I don't think
that you do! - post back; it nees some VBA code but it can be done. An
Update query might be better (rather than using a Form), depending on
the context, however.

John W. Vinson[MVP]

 

[John Vinson]

The " Name" field is the name of the doctors I support. The "VS" forms is
set up so the user can put in the actually patients seen at our different
sites each month. I orginally did not want to put the budget figures on this
form but "those that are in power" wanted that way. I agree with you it
would make it much simpler to administer to only have the budget figures in
one place. The tabel "Fiscal Year" actually has about 20 fields. One field
for each of our locations. I need to try and keep the data input as simple
as possible due to the user that will be putting these figures in is not that
comfortable on a computer.

You're violating normal design even more. :-{(

It is not necessary to store the budget figures in the TABLE in order
to display the budget figures on the FORM. The "Powers that be"
should be concerned with what is *seen* and how well it works - not
dictating that you should corrupt your tables with non-normal data!
Can you perhaps put textboxes on the form with

=DLookUp("[the right budget field]", "[Fiscal Year]", "<criteria>")

so they can SEE the budget figures?

If you have a one (fiscal year) to many (locations) relationship -
*you should model it as a one to many relationship*. Storing the
location in a fieldname will continue to make your database more
complicated, harder to use, harder to modify, and unreliable. A Form
based on Fiscal Year, with a subform based on this (new) budget table,
can very easily be set up to be user friendly. It's NOT necessary,
prudent, or wise to denormalize your tables for that purpose!


John W. Vinson[MVP]

 

[John C]

OK. So what you are saying is I need to completely forget about keeping the
budget figures in the "VS" table. Just keep them in the Fiscal Year table.
Now my question is since I am going to have budget figures for 20 some plces
for 20 some doctors for everymonth of the year and the same for the actual
patients they see. Do i need to right some sort of query to be able to
report the budget vs the actual. Please bear with me I am not good with
access this kinda got dumped on me.

The " Name" field is the name of the doctors I support. The "VS" forms is
set up so the user can put in the actually patients seen at our different
sites each month. I orginally did not want to put the budget figures on this
form but "those that are in power" wanted that way. I agree with you it
would make it much simpler to administer to only have the budget figures in
one place. The tabel "Fiscal Year" actually has about 20 fields. One field
for each of our locations. I need to try and keep the data input as simple
as possible due to the user that will be putting these figures in is not that
comfortable on a computer.

You're violating normal design even more. :-{(

It is not necessary to store the budget figures in the TABLE in order
to display the budget figures on the FORM. The "Powers that be"
should be concerned with what is *seen* and how well it works - not
dictating that you should corrupt your tables with non-normal data!
Can you perhaps put textboxes on the form with

=DLookUp("[the right budget field]", "[Fiscal Year]", "<criteria>")

so they can SEE the budget figures?

If you have a one (fiscal year) to many (locations) relationship -
*you should model it as a one to many relationship*. Storing the
location in a fieldname will continue to make your database more
complicated, harder to use, harder to modify, and unreliable. A Form
based on Fiscal Year, with a subform based on this (new) budget table,
can very easily be set up to be user friendly. It's NOT necessary,
prudent, or wise to denormalize your tables for that purpose!


John W. Vinson[MVP]

 

[John Vinson]

OK. So what you are saying is I need to completely forget about keeping the
budget figures in the "VS" table. Just keep them in the Fiscal Year table.

Exactly. Or at least in some appropriate table. The budget figure
should be stored ONLY ONCE.

Now my question is since I am going to have budget figures for 20 some plces
for 20 some doctors for everymonth of the year and the same for the actual
patients they see.

In that case you should have a Budget table with fields for
BudgetYear, BudgetMonth, plce (whatever a plce is <g>), doctor, and
budget amount. That is - *again* - you should NOT store data (places,
doctors, dates, or ANY OTHER KIND OF DATA) in *fieldnames*. Any time
you have twenty fields all for the same kind of data, you should
instead have a one to many relationship - and therefore you need
another table. "Fields are expensive, records are cheap" - that's what
normalization is all about!

Do i need to right some sort of query to be able to
report the budget vs the actual. Please bear with me I am not good with
access this kinda got dumped on me.

The Query is the basic tool in your toolbox. You can no more build a
meaningful Access application without using Queries than you can use
Excel without using cell formulas - the Query is the basis of
EVERYTHING you want to do. Yes, you will need a query to compare
budget to actual.

Again... you have not posted the structure of any of your tables, so
in a way I'm groping in the dark here. Nor do I have a clear picture
of what it is you're budgeting (dollars? office time? counts of
visits?), nor what entity is being held to a budget (a doctor? a
patient? a practice?)


John W. Vinson[MVP]