Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jun 2009
    Posts
    19

    Unanswered: Access 2003 int average ignoring 0's

    Hi, im working on reports that are taking months worth of numbers and displaying the min, max and average.

    Problem is, when no data is entered, the average takes into account the 0 as the data is entered in through a combo box.

    how can i take an average of a number and ignore the 0 in a query but still work with the other data?

    Code:
    SELECT qry_StationStats_Beach_Breakdown_Data.[SDate By Month], 
    qry_StationStats_Beach_Breakdown_Data.SdateMonth, 
    qry_StationStats_Beach_Breakdown_Data.StationID, 
    qry_StationStats_Beach_Breakdown_Data.AvgOpen, 
    qry_StationStats_Beach_Breakdown_Data.AvgRips, 
    Int([AvgWaves]) AS Waves, 
    qry_StationStats_Beach_Breakdown_Data.MinWaves, 
    qry_StationStats_Beach_Breakdown_Data.MaxWaves, 
    Int([AvgInWater]) AS InWater, 
    qry_StationStats_Beach_Breakdown_Data.MinInWater, 
    qry_StationStats_Beach_Breakdown_Data.MaxInWater, 
    Int([AvgOnBeach]) AS onBeach, 
    qry_StationStats_Beach_Breakdown_Data.MinOnBeach, 
    qry_StationStats_Beach_Breakdown_Data.MaxOnBeach
    FROM qry_StationStats_Beach_Breakdown_Data;
    this query is based on another query simply to get a whole number for the average using int, the original query is the same as this one bar it's just taking averages.

    The results are displayed in a combo box that shows (for one) wave height and has to of course be an whole number.

    Pickle?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    zero has a specific value, and it will be reflected in the average (and other domain functions). if you want these values to be excluded then they should be NULL.

    NULL means there isn't a value, or we don't know the value as yet, if ever.

    so its a data problem
    perhaps you should set NULL as the default value
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'll also make a temp-table from the avg/tot query and then use that temp table to further filter/join in another query. Sometimes this makes it easier.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Jun 2009
    Posts
    19
    damn, didn't realize that this wasn't a common thing. Will look into changing the field to accept null, is a number field at the moment as I intended to use calculations, will see if it's happy with string perhaps?

    Not really sure how to make temp tables, just queries etc

  5. #5
    Join Date
    Jun 2009
    Posts
    19
    Thought there had to be a simple way, adjusted the query, it wasn't actually 0 that I was trying to avoid, it was 1 (not that it makes much difference)

    If I understand this correctly, it only takes the average of numbers that are larger than 1, then the next query rounds it up or down to make the number suitable for a combo box's index. The combo box's data is liner so it references properly.

    Avg(IIf([Waves]>1,[waves],Null)) AS AvgWaves

    thanks for putting me on the right track, Null that is

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well you could use a yucky IIF statement to set the value to NULL where the column is 0.
    however that is a kludgey work around. the problem is that your design is flawed, and you may well hit similar problems again.

    Avg(IIf([Waves]>=1,[waves],Waves,Null)) AS AvgWaves

    I'd strongly reccommend that you chaneg the existign data
    update mytable set mycolumn=NULL where mycolumn=0

    then change your table design so that the default value is NULL
    the change your form(s) to make certain that NULL is the default value. because of the way Access/JET works it will not automatically pickup changes to the table values after the fomr has been designed.

    the issue is you are trying to patch up a basic design flaw, y'can't expect a cheap or quick solution.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2009
    Posts
    19
    Didn't realize that numbers in a table "could" contain a null value.

    If that is the case, perhaps I should be changing the insert statement as well as the combo box's bound column.

    If time permits, I will change it and learn from it.
    Code:
    qryInsert = "INSERT INTO tbl_stationstats_beach (sDate, stime, StationID, open, waves, tide, rips, other, winddir, windspeed, inwater, onbeach ) " _
                & "VALUES (date(),'" & txtSTime & "'," & cboStationID & ",'" & chkOpen & "','" & cboWaves & "','" & cboTide & "'," _
                & "'" & chkRips & "','" & txtOther & "','" & cboWindDir & "','" & cboWindSpeed & "','" & txtInWater & "','" & txtOnBeach & "')"
    
    CurrentDb.Execute qryInsert, dbFailOnError
    Changed it to a string assigning a null value (integer didn't work).

    "run time error 3464" data mismatch (which i'm sure is related to the string)

    I suppose the question is, "how to insert a null value with an integer field?"

    by the look of this older code using the direct controls in the sql statement I am open to sql inject attacks? I remember fixing this problem in the VB version.
    Since the table does support a null value in an integer field, i'm sure it's just me that's the problem.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    NULL in database terms means no value
    there is a problem in using null values in many languages for string values.

    whilst you are at it store a time as a date time value, not a string, bear in mind that as in most database systems Access/JET stroes date and times as a single datatype. if its a numeric value store it as a numeric value, if its a date/time then store it as a date/time, if its boolean store it as a boolean or yes/no column.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jun 2009
    Posts
    19
    thanks healdem,
    txtSTime is actually a date\time field, formatted as short time,
    My naming convention for controls is just what the control is on the form, this one is a text box so users can manipulate the time in case the time isn't the current one.

    That's one lesson that I learnt a while ago (except staff numbers, stored them as strings as they are so long and don't need any calculations)

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in which case why store the time as string and why store the time separate to the date (unless of course its an elapsed time or say wave period)
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Jun 2009
    Posts
    19
    I use the date and time for different things, plus i really hate merged date time fields. for me, queries are easier for me to reference a date if it is just the date and no time.

    Back to the question though.
    How to insert Null with my query anyone?

    Code:
    qryInsert = "INSERT INTO tbl_stationstats_beach (sDate, stime, StationID, open, waves, tide, rips, other, winddir, windspeed, inwater, onbeach ) " _
                & "VALUES (date(),'" & txtSTime & "'," & cboStationID & ",'" & chkOpen & "','" & cboWaves & "','" & cboTide & "'," _
                & "'" & chkRips & "','" & txtOther & "','" & cboWindDir & "','" & cboWindSpeed & "','" & txtInWater & "','" & txtOnBeach & "')"
    
    CurrentDb.Execute qryInsert, dbFailOnError

  12. #12
    Join Date
    Jun 2009
    Posts
    19
    I use the date and time for different things, plus i really hate merged date time fields. for me, queries are easier for me to reference a date if it is just the date and no time.

    Back to the question though.
    How to insert Null with my query anyone?

    Code:
    qryInsert = "INSERT INTO tbl_stationstats_beach (sDate, stime, StationID, open, waves, tide, rips, other, winddir, windspeed, inwater, onbeach ) " _
                & "VALUES (date(),'" & txtSTime & "'," & cboStationID & ",'" & chkOpen & "','" & cboWaves & "','" & cboTide & "'," _
                & "'" & chkRips & "','" & txtOther & "','" & cboWindDir & "','" & cboWindSpeed & "','" & txtInWater & "','" & txtOnBeach & "')"
    
    CurrentDb.Execute qryInsert, dbFailOnError

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can extract the time as and when required.

    in my books the 'correct' solution is to fix the data. using an IIF is a kludge, kludge fixes tend to bit you down the track.

    so run an update query that sets the value to NULL where appropriate
    change the default value on the column(s) in the table(s)
    change the default value on any controls in pre existing forms

    you may be able to run your average statement if you exclude values of zero "...where mycolumn >0"

    you may be able to construct an IIF statement
    Avg(IIf([Waves]>=1,[waves],[Waves],Null)) AS AvgWaves
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I personally might separate this into 2 queries and make the first query your IIF expression and then make your 2nd query the AVG query off the 1st query.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Jun 2009
    Posts
    19
    I can change all that, but I am stuck with the insert query not wanting to insert a null value. query below.
    Code:
    qryInsert = "INSERT INTO tbl_stationstats_beach (sDate, stime, StationID, open, waves, tide, rips, other, winddir, windspeed, inwater, onbeach ) " _
                & "VALUES (date(),'" & txtSTime & "'," & cboStationID & ",'" & chkOpen & "','" & cboWaves & "','" & cboTide & "'," _
                & "'" & chkRips & "','" & txtOther & "','" & cboWindDir & "','" & cboWindSpeed & "','" & txtInWater & "','" & txtOnBeach & "')"
    
    CurrentDb.Execute qryInsert, dbFailOnError
    @pkstormy, that is how i ended up doing it, one query to get the average (with the iif statememt), then another reading the first to get the whole number.

    The funny thing is about this query is that it is not actually required by the customer, I just threw it in to an extra report to show some stats for the month. No other reports use average (grateful for that too)

    So now I am asking for help on an insert statement that will insert a null value. Either that or I will have to make 2 statements, one for null skipping the field all together, and one for a value. (which I'm sure isn't really that great of a practice making 2 queries for insert.)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •