Visibility of data in the table and on the report

[Gosha]

Hi

I have a database of clients (access 2003).
It's not very complicated, but I have a big problem...

I ve recently added a few new boxes i.e.:
sex, area code, age group, date of employment etc.

When I input info onto the form it is visible,

however as soon as I go to the table or try to create a report - fields
which should give me the same data as on the form are blank!

Could anyone help me please?

I am really desperate...

Thanks

 

[Ray C]

In your form, are the new textboxes pointing to the proper fields on the
table. One way to tell is if you reopen the form and see if your data
reappears, this way you know.

Secondly, make sure your reports are pointing to the same fields as the form.

Ray

 

[Gosha]

Hi Ray C,

Thanks for your quick reply.

Unfortunately, I realized that I forgot to add more info.. Sorry!

It is just the case of
[age group][sex][area code] control sources' are set up as functions to
allow drawing that info for stats...
ie.

=IIf([Age] Between "16" And "19","16-19",IIf([Age] Between "20" And
"25","20-25",IIf([Age] Between "26" And "35","26-35",IIf([Age] Between "36"
And "45","36-45",IIf(IsNull([Age])," ","45+")))))

Another field called [status at registration] is a combo box with
control source connected to the field in the table
and row source type as a value list

but none of this data is visible in the table or a report....

I hope I managed to illustrate it properly as English is not my first
language...

Thanks again Ray C!

 

[John W. Vinson]

Hi

I have a database of clients (access 2003).
It's not very complicated, but I have a big problem...

I ve recently added a few new boxes i.e.:
sex, area code, age group, date of employment etc.

When I input info onto the form it is visible,

however as soon as I go to the table or try to create a report - fields
which should give me the same data as on the form are blank!

Could anyone help me please?

I am really desperate...

Thanks

First off... the data is NOT stored in your Form. Adding a textbox to a form
will not store it anywhere. Data is stored in tables, and only in tables.

You need to add the fields to your Table (the table upon which the form is
based), then be sure that they're included in the Form's Recordsource
property; you can then add textboxes or other controls bound to the table
field.

If you open the form in design view and view the properties of the textboxes
you've added, they should have a fieldname in their Control Source property...
do they?

 

[Gosha]

Hi John W. Vinson,

because I was trying to have those fields filled out automatically, based on
fields that already existed, in the control source field I've input the
functions (for example see above reply)

as it was the only way I could find, for them to work....

is there any other place to put in these functions instead of control source?

 

[John W. Vinson]

Hi John W. Vinson,

because I was trying to have those fields filled out automatically, based on
fields that already existed, in the control source field I've input the
functions (for example see above reply)

as it was the only way I could find, for them to work....

is there any other place to put in these functions instead of control source?

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

 

[Gosha]

So generally, why functions like this one:

=IIf([Age] Between "16" And "19","16-19",IIf([Age] Between "20" And
"25","20-25",IIf([Age] Between "26" And "35","26-35",IIf([Age] Between "36"
And "45","36-45",IIf(IsNull([Age])," ","45+")))))

exist?

I need to draw data for reports and make charts for our stats, using age
groups, sex, area codes etc......

 

[John W. Vinson]

So generally, why functions like this one:

=IIf([Age] Between "16" And "19","16-19",IIf([Age] Between "20" And
"25","20-25",IIf([Age] Between "26" And "35","26-35",IIf([Age] Between "36"
And "45","36-45",IIf(IsNull([Age])," ","45+")))))

exist?

I need to draw data for reports and make charts for our stats, using age
groups, sex, area codes etc......

Fine. Store the raw data - the birthdate in this case, since any Age field can
be guaranteed to be *WRONG* a year from the day it's entered - and do this
kind of calculation *IN A QUERY".

I'd actually use a range table instead, with number fields for the low and
high end of each range and a text field for the label. Why you're using text
strings in your criteria I do not know; it will probably work but it would
certainly be inefficient. Note that "21545" is in fact "between" "20" and
"25"...

 

[Gosha]

Field [age] is automatic as well and it takes data from the [DOB] field....

and I just dont understand the second part, as I am a beginner.....Sorry and
thanks for you rhelp
--
Gosha

So generally, why functions like this one:

=IIf([Age] Between "16" And "19","16-19",IIf([Age] Between "20" And
"25","20-25",IIf([Age] Between "26" And "35","26-35",IIf([Age] Between "36"
And "45","36-45",IIf(IsNull([Age])," ","45+")))))

exist?

I need to draw data for reports and make charts for our stats, using age
groups, sex, area codes etc......

Fine. Store the raw data - the birthdate in this case, since any Age field can
be guaranteed to be *WRONG* a year from the day it's entered - and do this
kind of calculation *IN A QUERY".

I'd actually use a range table instead, with number fields for the low and
high end of each range and a text field for the label. Why you're using text
strings in your criteria I do not know; it will probably work but it would
certainly be inefficient. Note that "21545" is in fact "between" "20" and
"25"...

 

[John W. Vinson]

Field [age] is automatic as well and it takes data from the [DOB] field....

My point is that the [age] field should *simply not exist* in any table; it
should not be stored anywhere. Instead it should be recalculated whenever it's
needed; the DOB is the only data which needs to be stored in your table.

and I just dont understand the second part, as I am a beginner.....Sorry and
thanks for you rhelp

I'll try to get back to this thread later today or tomorrow but the basic idea
is that you should store translations like this in tables, and look up the
value, rather than embedding them in complicated, hard to maintain nested IIF
statements in your queries. You'ld have a table with records like

Low; High; Rangename
16; 24; "16-24"
25; 30; "25-30"

etc. and use a Query to select which age range applies.

 

[Gosha]

Thanks John

--
Gosha

Field [age] is automatic as well and it takes data from the [DOB] field....

My point is that the [age] field should *simply not exist* in any table; it
should not be stored anywhere. Instead it should be recalculated whenever it's
needed; the DOB is the only data which needs to be stored in your table.

and I just dont understand the second part, as I am a beginner.....Sorry and
thanks for you rhelp

I'll try to get back to this thread later today or tomorrow but the basic idea
is that you should store translations like this in tables, and look up the
value, rather than embedding them in complicated, hard to maintain nested IIF
statements in your queries. You'ld have a table with records like

Low; High; Rangename
16; 24; "16-24"
25; 30; "25-30"

etc. and use a Query to select which age range applies.