Creating a display field that displays total of other form fields

[Guest]

First, don't laugh at my hobby. I am trying to develop a database to track
my rather extensive gift card collection. I am using Access 2003. I have
the database / table set up as well as the forms, queries and reports. (that
whole learning process practically proved to be a second hobby of it's own).
My problem is that my table / database has a Quantity field. I want this
Quantity field to automatically calculate the total Quantity of a single gift
card design for me based on the data entry I input from the Form. I want
this Quantity field to auto-calculate and auto-update and display on the Form
as I enter the data.

An example is that I have 5 copies of a special edition gift card. This
would be a Target, Christmas, Special Edition gift card. When entering the
various gift card data into the database, by using the Form I, will key into
the various categories listed above to indicate that I have 1 QTY filed in
the Target file, 1 QTY filed in the Christmas file, and 3 QTY in the Special
Edition file.

I want the Form to display the auto-calculate and auto-update (display) what
the grand total quantity (this quantity field is located in my table) is of
this gift card that I have in stock each time I enter a Quantity into the
different categories.

I have tried playing with Data Type from the Design View of the Table and
attempted to add other fields of the Table into the Quantity field with no
success. I would greatly appreciate any advice or direction anyone can offer.

Thanks in advance - Yvette

 

[Guest]

Yvette,

Base your form on a query. Place the calculated fields in this query. Don't
try to create the calculated fields in the table because that's not the place
to do the math. When placed in a query you'll notice that the calculation is
being done while you do the entering in your form. Updating is also automatic.

So place all the fields in a query and create a calculated field in the
query like:

QuantityChristmas: [field1]+[field2] etc.

Rename the [field1] with your own fieldnames.

hth

 

[Guest]

Maurice,

Let me see if I understand this clearly. I have my categorical fields
(B-day, x-mas, special edition, baby, wedding, etc., etc.) located as fields
in my main / primary table - correct?

I create a query based off that main / primary table using those categorical
fields and set the query to tabulate the totals for each categorical field.
This will add together all the values I have keyed into the categorical
fields - these values will be present sporadically throughout all 3,000+
records. This will generate a total for me per categorical field that shows
me I have 173 baby, 237 b-day, 693 x-mas, and so forth - correct? Do I have
to do a laborious, never-ending, manual calculation such as
[field1]+[field2]+[field3].... or will it auto-calculate a specified column
(field) for me?

Can I also add up the values within a record / row to show how many cards I
have of that specific card; i.e. 5 QTY cards for that specific record broken
down to 1 QTY Target, 1 QTY x-mas, 3 QTY Special Edition generated off the
data I key into the categorical fields. Then it would be this one QTY field
that would be inserted into my form, which should tabulate and display the
total as I key the values into the various field within the form - correct?

If I base my Form on the Query, can I also base the Form on the main /
primary table at the same time? Allowing me to add fields to the Form from
the main / primary table and insert the one single QTY field from the Query
into the same Form???

And one last question - I am a little confused about how many tables I
should have to handle my data. Right now I have one single table which
contains about 8 fields of basic card information + approx. 38 categorical
fields + 17 fields for special effect / edition categories. Should I break
this down into 3 separate tables (basic info; categorical; and special
effect), if so, what is the benefit or advantage to maintaining three
separate tables? I have the categorical and special effect fields set up as
Yes/No check-mark fields and the rest is general data entry - I am trying to
incorporate the auto-calculate total QTY feature into the Form. For example
if I check-mark B-day, then I would key the appropriate QTY such as 1, 3 or 5
and a display box at the top of the Form would auto-calc the total QTY as
they are keyed into the form in the various categorical fields. I am
thinking that to have a Yes/No box AND to key in a QTY is redunant and that
possibly I should just key in the QTY and eliminate the Yes/No box since this
results in each category having 2 fields each (i.e. 93 total fields per
record instead of 58 fields per record). Any suggestions?

Appreciate your advice and expertise.

Yvette

 

[Guest]

Yvette,

See inline comments to get points clear...


--
Maurice Ausum

Maurice,

Let me see if I understand this clearly. I have my categorical fields
(B-day, x-mas, special edition, baby, wedding, etc., etc.) located as fields
in my main / primary table - correct?

-- No, seperate the fields in a seperated table called "Category" and link
this via a ID to your main table.

I create a query based off that main / primary table using those categorical
fields and set the query to tabulate the totals for each categorical field.
This will add together all the values I have keyed into the categorical
fields - these values will be present sporadically throughout all 3,000+
records. This will generate a total for me per categorical field that shows
me I have 173 baby, 237 b-day, 693 x-mas, and so forth - correct? Do I have
to do a laborious, never-ending, manual calculation such as
[field1]+[field2]+[field3].... or will it auto-calculate a specified column
(field) for me?

-- If you have corrected point 1 you should now be able to create a query
from those two tables and grouping it by [Count]. This will give you a count
of cards based on every category you have. You group the records by clicking
the big E-sign (tooltip - Totals) in the query menubar.

Can I also add up the values within a record / row to show how many cards I
have of that specific card; i.e. 5 QTY cards for that specific record broken
down to 1 QTY Target, 1 QTY x-mas, 3 QTY Special Edition generated off the
data I key into the categorical fields. Then it would be this one QTY field
that would be inserted into my form, which should tabulate and display the
total as I key the values into the various field within the form - correct?

-- When you have grouped by totals (and count) you won't see a specific
count for that specific card. In that case it would be better to create a
seperate query for that view.

If I base my Form on the Query, can I also base the Form on the main /
primary table at the same time? Allowing me to add fields to the Form from
the main / primary table and insert the one single QTY field from the Query
into the same Form???

If you use the main table in the query you can use this query as a source
for your form and still be able to add records. The category could be a
combobox on your form which is bound to the id field in the main table but
has a rowsource of the categories table.

And one last question - I am a little confused about how many tables I
should have to handle my data. Right now I have one single table which
contains about 8 fields of basic card information + approx. 38 categorical
fields + 17 fields for special effect / edition categories. Should I break
this down into 3 separate tables (basic info; categorical; and special
effect), if so, what is the benefit or advantage to maintaining three
separate tables? I have the categorical and special effect fields set up as
Yes/No check-mark fields and the rest is general data entry - I am trying to
incorporate the auto-calculate total QTY feature into the Form. For example
if I check-mark B-day, then I would key the appropriate QTY such as 1, 3 or 5
and a display box at the top of the Form would auto-calc the total QTY as
they are keyed into the form in the various categorical fields. I am
thinking that to have a Yes/No box AND to key in a QTY is redunant and that
possibly I should just key in the QTY and eliminate the Yes/No box since this
results in each category having 2 fields each (i.e. 93 total fields per
record instead of 58 fields per record). Any suggestions?

Yes, you should indeed normalize you tables. Separate the table fields as
you described. Maintenance comes at a price further down the road. By
normalizing the data you will get consistent data. My guess is that the way
you have set it up right now you might never get the correct counts for what
you are looking.

Believe me if the query is setup right you don't have to do the math, Access
will do it for you.

 

[Guest]

Maurice,

Thank you so much for the clear and easy to understand explanations.
Amazingly for only having used Access for 4 days, I get the concept and I
follow the direction you are trying to lead me - now I just need to figure
out the technicalities of actually doing it.

Yvette

 

[Guest]

Ok, glad to be of assistance '-)