How to add up a list of numbers

[Emery]

Hi. I need to create a field that will allow me to input a list of numbers, and then another field that will calculate the total. I'll explain how I used to do this in Filemaker Pro:

1. Create a number field, set it to repeat (say 20) times. This allows you to input up to 20 different numbers into the field, each on its own line.
2. Create a calculation field that adds up all the numbers in this field.

How would I do something like this in Access?

thanks,

-Emery

 

[georgedwilkinson]

Nobody's answered yet so I'll take a stab.

Access and FileMaker Pro are different products and unfortunately I know nothing about FileMaker Pro.

I'm sure there's a valid reason for doing what you're doing but it doesn't really seem like the kind of thing that I would want to get done with a relational database system.

What exactly are you trying to do (in business terms) and why can't you do that in FileMaker Pro (why do you want to do it in Access)?

Perhaps if I understood a little better I (or another poster) could provide a suggestion for a solution that works in an Access paradigm.

 

[Emery]

Thanks George. I'm using Access because my office doesn't have Filemaker, and this is something I would have created in Filemaker. Access is the most similar Office product, as far as I can tell, so that's what I must use.

What I am creating is a database of clients, and for each client we have checklists of what has and has not been done. We also need to sort clients by this data. So for example, for all the clients we have sent an inquiry letter to, we have a check box. With a database, I can sort by clients who do not have this box checked, so I can easily see who we've missed.

We need to do these sorts of queries for all sorts of parameters, hence the database.

The other element we need, is to add up the outstanding bills of each client. So for example, a client may have 7 purchases outstanding, and I want to be able to enter their bills, and have a grand total. This is part of the data we need to keep on the clients.

If you can think of a better Microsoft product to keep track of all this, let me know. Otherwise, if I can get this small piece figured out (how to add up a list of numbers), it seems Access will work.

thanks,

-Emery

 

[Mike375]

Are you talking about a table that has 20 records and you want the total of the values in one of the fields.

Or are you talking about 20 fields or 20 unbound text boxes on a form, that is one record.

Or to put it another way are you talking about a value for each of 20 clients or is 20 values to be added for one client.

If it is value for each client then in an unbound textbox enter =Sum([YourFieldName]). A query can also be used and you can select Sum, Average, Count etc.

If it is 20 values for each client then basically it is

=([Field1]+[Field2]+[Field3]) etc entered into an unbound text box or the same (without the =) in a calculated field in a query.

 

[georgedwilkinson]

Access is the perfect solution for your business problem. Glad you got the first question sorted 'cause I had no clue how to answer.

 

[Mike375]

For a more complete answer, at least wtihin my limitations, the formula for 20 boxes on one record should be like this:

=(Nz([Textbox1])+Nz([Textbox2])) etc

The Nz function solves the problem if any of the fields or unbound text boxes are null otherwise you won't get an answer.

 

[Emery]

If it is 20 values for each client then basically it is

=([Field1]+[Field2]+[Field3]) etc entered into an unbound text box or the same (without the =) in a calculated field in a query.

Yes, it's 20 values for each client. So in Filemaker, I would create one field, and assign it a property (20 repeating lines). In Access, do I have to create 20 actual columns--that is, add 20 new fields to my table--then manually line them up in the layout view of my form, and then create an unbound text box that will add up those 20 columns?

Thanks, and sorry for the very basic questions. I'm just used to the Filemaker structure, where you only have to create one field, and then you can assign all sorts of properties to it (make it a repeating number field, make it a calculation field, make it a lookup field, etc.) It's taxing my brain to understand the way Access does things.

 

[Mike375]

Note my last post as you were posting with respect to the Nz function.

 

[Mike375]

Yes, it's 20 values for each client. So in Filemaker, I would create one field, and assign it a property (20 repeating lines). In Access, do I have to create 20 actual columns--that is, add 20 new fields to my table--then manually line them up in the layout view of my form, and then create an unbound text box that will add up those 20 columns?

Maybe and mabe not, it depends.

If you add 20 fields then you might do the calculation in a query, similar to the Textbox but without the = sign. A calculated field is made.

The other thing to consider is whether 20 fields are what is required. In Access the tendency is to add records to a field rather than extra fields.

For example I am in the insurance business and I have a table that holds policy details, about 15 fields. If a policy holder has 10 policies then I don't have 150 fields for that. Instead there would be 10 records in the table with the 15 fields and a query, macro or code would select records for calculations to be done. That could be on type of policy, all his policies, all the policies of a certain type for those people who are non smokers and were born after 1960 etc and etc.

It quite possible that your 20 pieces of data for each client should all go into one field in another table and that table would have another field which linked it to the client table and probably another field or two that identified the type of entry.

 

[Emery]

Note my last post as you were posting with respect to the Nz function.

Thanks Mike, let me digest your suggestions.

BTW, where do you enter the Nz functions? Is in the Data tab of the property sheet, and then you put it into the Expression builder?

thanks,

-Emery

 

[Mike375]

You enter in an unbound textbox as I typed it and it goes in the data source section. Actually, when you drag an unbound textbox from the toolbox you can just type it in the box.

=(Nz([Textbox1])+Nz([Textbox2]))

The function part of the Nz is being picked up by the Zx() Function in Access are invariable FunctionName(). Field in virtuall all cases fields and textboxes are enclosed in []

With queries you create calculated fields. This is done on the query grid like

NewFieldName:whatever goes here

Generally in Access if you have fields in a table that is repeating type data then you are doing it wrong and a warning sign is you have do calculations on a lot of fields across the row. Think data in fields as being a description.

But there are plenty of exceptions that on the surface look wrong but are fine. For example I have 5 date fields in my main table and on the surface that would be a no no. However, those dates are changing and is to do with telemarketing and the dates and differences between them are in fact describing the person and whether and when the data base will select him again for another call.

In your case I would imagine the numbers you would have in 20 fields would probably go in one table. That table would have a field for the number, a field for to the customer/client record in your main table and probaly a field that describes the number. So it might look like

ID..........Article..........Amount.....Ordered
123........Oranges........20............15/7/08
123........Apples..........15............12/6/09
321........Oranges........10............15/6/08

In the above example we have data for two different customers. The Article and Ordered field allow Access to select the records for calculations and the ID allows it to be done for a specific customer.

One (of the many) advantages of such a table set up as opposed to having 20 fields on the client/customer table is you can do all sorts of calculations. For example you could get the number for all the oranges ordered between two different dates and for all customers.

In a nutshell, if you find your setup means calculating across a row or record then you are probably heading for difficulty.

 

[Emery]

Thanks Mike. That makes sense. Now all I have to do is figure out how to implement it properly

I'll start hacking away in the direction you suggest.

thanks again.

-Emery