Creating a display field that displays total of other form fields

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


0
Utf
11/12/2007 5:23:01 AM
access.forms 6864 articles. 2 followers. Follow

5 Replies
687 Views

Similar Articles

[PageSpeed] 26

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 
-- 
Maurice Ausum


"DrPepsi" wrote:

> 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
> 
> 
0
Utf
11/12/2007 8:08:02 AM
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
0
Utf
11/12/2007 5:08:04 PM
Yvette,

See inline comments to get points clear...


-- 
Maurice Ausum


"DrPepsi" wrote:

> 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.

> Appreciate your advice and expertise.
> 
> Yvette
0
Utf
11/12/2007 9:29:04 PM
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
0
Utf
11/13/2007 12:38:04 AM
Ok, glad to be of assistance '-)
-- 
Maurice Ausum


"DrPepsi" wrote:

> 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
0
Utf
11/13/2007 10:07:02 AM
Reply:

Similar Artilces:

Problems with creating a newsletter
I chose the form "Newsletter - email". I have created a 3 page newsletter. Now, I have NO IDEA how to send it out as an email. I do not want to send it as an attachment. I cannot figure out which "save as" format or what I need to do so that i can email this newsletter. Is there a website that goes through how to do this? Am I correct when I say that it has to be html in order for me to send it as email? That is not one of the options. Any help you give me would be greatly appreciated. Thank you, Markis www.adreamforabetterworld.com ...

forward to: field
Hi Everyone, im looking for the AD attribute in which the 'forward to:' field of the delivery options is stored. Thank you in advance kind regards marc -- Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/ Its in the altRecipient attribute. -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "Marc Wenger" <haga(at).iesg--nspm-noa9h.gmx.ch> wrote in message news:opr64hs7z6fyi4rt@news.microsoft.com... > Hi Everyone, > > im looking for the AD attribute in which the 'forward to:' field of ...

Excel Opens Without Displaying Workbook
I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Displaying Workbook (http://support.microsoft.com/default.aspx?scid=kb;en-us;158996&Product=xlw97), but neither of the resolutions fixed the problem. Any suggestions?? Are you using Excel 97? -John Baughman Fort Collins, CO >-----Original Message----- >I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Di...

VBA to creating autotext entries or quickparts in different catego
I have a VBA application that basically allows people to easily create autotext entiries, move them between machines and use them making comments on assignments. Currently it operates in EXACTLY the same way in Word 2003 and 2007 (using userforms) and I want to keep that as long as possible. You can see the application at http://emarking-assistant.baker-evans.com and either the screen image or the video demos will give you an idea of what I am doing Currently I store all the comments in a long list of autotext entries that is displayed in a field with the value of the entr...

custom field for projects with tasks past due
I am wondering how/what formula would be needed for creating a custom field at the project level to show when a project has at least one task that is past due. I have a custom field at the task level (IIf([Finish]<[Current Date] And [% Complete]<100,1,2)) that will show when an individual task is overdue, but I want to add a custom field to the Project Center > Summary view to show when a project has any overdue tasks. The PM can then drill down from there into the project and see exactly what tasks are overdue. Once I figure out the formula for this I want to assign ...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Display of UML State Transition Event
Dear Group, I am attempting to use the UML Statechart with a couple of states and a transition between them. I select properties, Events, Change Event type, say, and can see ChangeEvent1 in the property window but... ....when I Ok back to the drawing the ChangeEvent1 is not displayed by the transition. I can enter actions in a similar way and they are displayed. How cna I get the event visible too in the drawing? Surely this is the most important aspect of a transition i.e. what caused it, regards, Colin Smith ...

Opening pub files created with older Publisher versions #2
I have just upgraded to Publisher 2003 from 2000 and am having trouble with pub files sent to me for our chuch newsletter which is using Publisher 97 The text is not wrapping around graphics boxes. Can I fix this? I really don't want to go back to Pub 200 Thank Richard this is caused by Publisher 97 not been printer independent. Even if you went back to Publisher 2000, unless you have the identical font versions and printer driver you would have issues with formatting. It sounds like the person sending you the file has a garbage HP inkjet printer. Get them to install the HP5P laser p...

Getting the BCC field by default.
I would like the BCC field to show by default but cannot find the setting for this in 2007. Anyone know where this is located? In a new message window, use the Options chunk to display the "Show BCC." --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, JC HARRIS asked: | I would like the BCC field to show by default but cannot find the | setting for this in 2007. Anyone know where this is located? Thanks Milly. I had...

Word2007. Checkbox. No Form
Hello! We have a document (not a form) that we would like to use checkboxes in. This document has a couple of sections where it would be useful if the user could check if something is applicable. We can't protect the whole document. Thank you for your help! Stacey I actually used this feature in a form this morning. Although you don't say what version of Word you are using, you can access the Control Toolbox toolbar in Word 2003, or in Word 2007, it is on Developer tab. To show the developer tab, click the Microsoft Office Button and then click Word Options. S...

I would like to know how to set up a 'fill in the blanks' form?
I would like to set up a templet of sorts to fill in the blanks for certificates. ...

Creating Text Box in Publisher 2007 Crashes the Application
Hello, we have a clean install of Publisher 2007 under Windows XP SP2, and when we try to create a text box in a document, (both an existing document and a blank document), publisher crashes. Office is fully patched. I ran Office Diagnostics from the help menu and no problems were found and the issue persists. Any thoughts on how to resolve the issue? Thanks, Syd See if selecting a different printer as default helps. How to view error signatures if an Office program experiences a serious error and quits http://support.microsoft.com/kb/289508/en-us -- Mary Sauer MSFT MVP http://of...

Creating a Macro to Delete Commas #2
I have an excel file that the size will varry. I need a macro that will check all the fields for a comma. If there is one I would like to get rid of it. Does anyone have any idea how to do this? I have no idea and I have been assigned this task. Help --- Message posted from http://www.ExcelForum.com/ No macro required. ctrl-H for find/replace. find , replace nothing (leave the replace field blank). You can of course record that within a macro if you wish. Drabbacs >-----Original Message----- >I have an excel file that the size will varry. I need a macro that will >check ...

Create Exchange mailbox from command line
I'm writing a script using dsadd and I was wondering if it's possible to create an exchange mailbox from the command line. Donovan Maybe not exactly what you want but it may help: http://www.joeware.net/win/free/tools/exchmbx.htm -- Neil Hobson Exchange MVP For Exchange news, links, and tips, check: http://www.msexchangeblog.com "Donovan Linton" <DonovanLinton@discussions.microsoft.com> wrote in message news:D9C839EF-883D-4E2E-8BE9-57782582F043@microsoft.com... > I'm writing a script using dsadd and I was wondering if it's possible to > create an ...

COUNTIF on Summary Fields
Can't use on a cell that "sums" with arithmetic operators "+". Is there a workaround, etc.? a bit more explanation? -- Don Guillett SalesAid Software donaldb@281.com "DLC" <dlcopesr@yahoo.com> wrote in message news:117v954kaf4s2f6@corp.supernews.com... > Can't use on a cell that "sums" with arithmetic operators "+". Is there a > workaround, etc.? > > ...

creating a backup on 2002 for Money 98
I am helping a friend who has 98. I need to make a backup of info on my 2002 for him to use on 98. Any suggestions as to how to do this? M98 can't read any file written by M02 besides .QIF import. M02 can't write any file readable by M98 except for .QIF export. Sounds like QIF export/import is your only choice. I suspect you will find this doesn't do what you want. "Carlotte" <Carlotta41@discussions.microsoft.com> wrote in message news:015b01c3d2fa$bbf8fd60$a101280a@phx.gbl... > I am helping a friend who has 98. I need to make a > backup of info on...

Help about numeric type field. Thanks.
I created a SQL Server 2005 CE DB ( .sdf, version 3.0) with vs2005. And I created a table which has 2 fields: fld1 - int, fld2 - numeric(38,25). But I encounted an error messagebox when I tried to insert a record (4005,9000000). The msgbox said Conversion overflows. The setting for my numeric fld2 is (precision=38,scale=25). So why occur error when to insert 9000000? Thanks in advance. ...

Calculating totals
I have two columns on a worksheet: "Project Codes" and "Totals." I would like to have columns on another worksheet that will automatically total up the different project numbers "A,B,C,etc." How do I do that? Thanks in advance for your help, Technically Handicapped Enter a *unique* list of your "Project Codes", starting in A2 of Sheet2. In B2, enter this formula: =SUMIF(Sheet1!A:A,A2,Sheet1!B:B) And copy down as needed. -- HTH, RD ===================================================== Please keep all correspondence within the G...

total group & max function
I am using Access 2003 I created a Db for Machine Mtce. The table consists of 3 tables, (1) Basic Mach Infor (ie Mach#, Mach Name, Mfg etc) - (2) Operations Table - consists of different prev mtce (PM) that need to be completed at various time frames. Ex: "Mach 100" could have 3 different operations; Oper # 100A to change the oil due every 90 days, Oper # 100B to check the belts due every 180 days, Oper # 100C to grease the machine due every 365 days. The 3rd table is the "PM History" table which includes the history of all of the PM's completed with Work Order...

Problem displaying expected results with CString
I am writing a MFC program to import data from a single table database into a normalized database with numerous tables. The first component opens a recordset object to the database and performs some basic tests on the field value, and the plan is to write the records out to a spreadsheet that fail to meet any of the criteria defined for the field. Along with writing out the record, I want to populate a comments field describing what failed. I have tried to implement this with a CString variable; I initialize it to a blank string each time a new record is examined, and then as each field is che...

CRM Email Displays Size=2>
One of my users is experiencing an issue when they save an email that is tracked within CRM the email displays in CRM with "Size=2>" directly in front of certain lines of the email. So for example "Size=2>" will appear in front of someone's comments in the email or in front of their name. Does anyone know why "Size=2>" is displaying in front of the lines of an email? Thanks. Mike H. "Mike H." wrote: > One of my users is experiencing an issue when they save an email that is > tracked within CRM the email displays in CRM wit...

Picture control on a form
What is the best way to fill a form with a picture control so that when the client window is resized the picture control will fill the form? -- Just Al Take a look at this and see if this is what you want: http://www.codeproject.com/dialog/bmpdlg01.asp Tom "Al" <Al@discussions.microsoft.com> wrote in message news:5BEA0FA2-3F95-4090-B908-A75DBF1D19DE@microsoft.com... > What is the best way to fill a form with a picture control so that when > the > client window is resized the picture control will fill the form? > -- > Just Al Thank You! -- Just Al &...

Null value in form not trapped by beforeupdate event
I have a form in Access 2003 linked to a SQL Server 2005 table. When I clear the value in a textbox (bound field is varchar and is required), I want the before update event to run to tell the user the value cannot be null. When I press the tab button to move to the next field after clearing the texbox, the before update event is not triggering and instead I'm getting the following error: You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162) How can I prevent nulls before and this error from triggering? Thanks! ...

Start macro creating a mail with contact data and autotext
Hallo, I am working with an user form. The developing of that form started with Outlook XP with a lot of code inside for different buttons. I changed to Outlook 2007 and unfortunately the code of the form was not longer displayed. What I learned about this is that MS does not support to much code in the form (or maybe a bug). They also do not support any longer. I was sending this form to MS support but they told it is do much code inside and they do not know, why the code is not displayed. In Outlook 2003 the code is displayed as in Outlook XP. Because I do not know real...

Disappearing data in sync'd forms
I have a small sized text box in a form called frmMain. This text box may or may not contain a large amount of text. If the text box does contain alot of text I want to open up a new form called frmLargeText that contains a larger text box to allow the user to easily see and edit the large amount of text. I also need the two forms to stay in sync. i.e. if the user moves to a new record then both forms move to the same record. I thought I could do this by setting the recordset of frmLargeText to equal the recordset of frmMain as follows: Dim frm as Form_frmLargeText Dim rst As DAO.Rec...