 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / April 2007summing fields
I have a form with the fields AR, LA, OK, TX, MS, Other... I enter miles in each field. Somedays I wont enter anything in the field. How do I add all of the entries and put them in the "Total Daily Mileage" field. I have it working now by entering =[AR]+[LA]+[TX]+[MS]+[OK]+[Other] but when there is nothing entered in one field nothing shows in Total Daily Mileage... The simple way is to set the field default to zero and then they will sum. Update all fields that are now null.  SignatureKARL DEWEY Build a little - Test a little > I have a form with the fields AR, LA, OK, TX, MS, Other... I enter miles in > each field. Somedays I wont enter anything in the field. How do I add all of > the entries and put them in the "Total Daily Mileage" field. I have it > working now by entering =[AR]+[LA]+[TX]+[MS]+[OK]+[Other] but when there is > nothing entered in one field nothing shows in Total Daily Mileage... First, a caution... If you have repeating fields (you appear to have multiple "state" fields) in your table definition, you have ... a spreadsheet! You won't get the best from Access' features and functions if you feed it 'sheet data. Is there a reason you aren't just doing this in Excel? Next, in Access, nulls propogate. As you've found out, having "nothing" in at least one of those fields (spreadsheet columns!) means the sum of them will be a nothing/null. If you insist on keeping this decidedly un-normal data structure, you can modify your formula/expression to use the Nz() function to convert nulls to zeros (Nz([AR],0) + Nz([LA],0) + ...). Be aware, however, if you ever add another state, you'll have to modify any/all formulas, your table structure, any queries involving that that table, your forms based on that table, any reports, any macros, any code ... -- what a maintenance headache! Seriously consider looking into the topic of normalization -- Access is designed to work with well-normalized data. Regards Jeff Boyce Microsoft Office/Access MVP >I have a form with the fields AR, LA, OK, TX, MS, Other... I enter miles in > each field. Somedays I wont enter anything in the field. How do I add all [quoted text clipped - 3 lines] > is > nothing entered in one field nothing shows in Total Daily Mileage... First- When I change the default value to zero nothing shows (0) in the fields? Second- =(Nz([AR],0)+Nz([LA],0)+Nz([TX],0)+([MS],0)+([OK],0)+([Other],0)) is the expression i entered and it says #ERROR. THe reason I am doing this is because my boss wants all of the data to be moved into a database.... What would you consider to be "normalized" data? Thanks >First, a caution... > [quoted text clipped - 27 lines] >> is >> nothing entered in one field nothing shows in Total Daily Mileage... First - I said "Update all fields that are now null." Second - You tables should look like this -- Trips --- TripID - autonumber - primary key TripDate - DateTime Traveler - text - name os person traveling/driving Travel -- TravelID - autonumber - primary key TripID - number - long integer - foreign key related to Trips.TripID State - text - state transversed StartDate - DateTime StartMile - number EndDate - DateTime EndMile - number Etc - SignatureKARL DEWEY Build a little - Test a little > First- When I change the default value to zero nothing shows (0) in the > fields? [quoted text clipped - 34 lines] > >> is > >> nothing entered in one field nothing shows in Total Daily Mileage... This is what it looks like now... Daily Log (Name of Table) Date - Date/Time Unit # - Text Vin # - Text Driver - Text Ending Mileage - Number Beginning Mileage - Number Total Daily Mileage - Number AR - Number LA - Numb TX - Numb MS - Numb OK - Numb Other - Numb Truck Type - Text Do i need to redo before i get to far into this...? >First - I said "Update all fields that are now null." >Second - You tables [quoted text clipped - 18 lines] >> >> is >> >> nothing entered in one field nothing shows in Total Daily Mileage... "Normalized" is a term describing a series of steps that condition data for use in a relational database (e.g., Access). You can learn more about normalizing via Access HELP and by searching on-line. A more normal design for your data might be something like: tblMileage MileageID State Mileage MileageDate With a design like this, adding a new state doesn't require any changes (you'd just add the new state to your tblState table). If you have a state and a date without any miles, you DON'T have a record for that combination. No Nulls, no worries! To add up all the miles for a date range, a simple Totals query. For a given state, a simple Totals query. Changing the default value on a table's field doesn't go back and "fix" existing records. It only applies to new records. Why are you using Nz() on only the first three? Regards Jeff Boyce Microsoft Office/Access MVP > First- When I change the default value to zero nothing shows (0) in the > fields? [quoted text clipped - 43 lines] >>> is >>> nothing entered in one field nothing shows in Total Daily Mileage... I figured that out while looking it over.. I have everything working like it should be now. BUT is this the best way to do it? >"Normalized" is a term describing a series of steps that condition data for >use in a relational database (e.g., Access). You can learn more about [quoted text clipped - 31 lines] >>>> is >>>> nothing entered in one field nothing shows in Total Daily Mileage... Define "best". Tell me what criteria you are using to evaluate, and I'll describe how I think this approach measures up. Regards Jeff Boyce Microsoft Office/Access MVP >I figured that out while looking it over.. I have everything working like >it [quoted text clipped - 38 lines] >>>>> is >>>>> nothing entered in one field nothing shows in Total Daily Mileage... |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.