Tables :: Averaging - Excluding Zeros And Nulls
Sep 16, 2014
I'm using Access 2010 and I'm a novice with databases.I have a table that contains water flow readings taken 4 times a day from multiple wells. I am trying to create a select query that will generate the daily average of the 4 flow readings.
HoleID.....Date....Flow1....Flow2....Flow3....Flow 4....Average
1111.....9-8-14.......0.........null.........7..........4......
1111.....9-9-14.......0.........null.........9..........3......
2222.....9-8-14.......0.........null.........10........7......
Also, my table also has several 0 values and NULLS meaning the well was turned off - I want to exclude the zeros and nulls when I average. How to average these numbers? Do I need to restructure my table?
View Replies
ADVERTISEMENT
Sep 20, 2006
So, I have two tables that, cut down, look like this:
Table1:
Code budget
100 5
110 7
120 3
150 6
Table2:
Code actual
100 4
110 9
130 2
150 1
I have another table that is all the codes plus a description.
I thought I was being clever because I realised that there are items in Table1 that do not appear in Table2 and vice versa. I need a query that is, in effect, Table1 minus Table2.
I linked all three tables via “code”, created my query with the minus calculations and thought it had worked.
One problem. Where there is a “code” in one table but not in the other, the query puts a blank or “null” into that field. Then the minus calculation gives, say:
null - 2 = null
I’m used to Excel where:
blank - 2 = -2
Is there any way to get round this please? This must be a common problem, no?
Thank you.
View 1 Replies
View Related
Aug 13, 2013
In the following simplified query, in some months(MonthOf) there were no warranties so I have a null field for AcceptedWarranties:
SELECT qryWarranty.MonthOf, qryWarranty.AcceptedWarranties
FROM qryWarranty;
In order to make my Warranty Trends graph work I need 0s. Other postings show the following statement should get the results I need:
SELECT ISNULL(AcceptedWarranties, 0 ) FROM qryWarranty
But no matter how I try to work this into the original code, the compiler finds reason to reject it.
View 2 Replies
View Related
May 2, 2013
I have a table of around 6000 records comprising 4 fields (A,B,C,D).
- Each field can contain numbers or Nulls.
- Each record can comprise all numbers, a mixture of numbers or Nulls, or all Nulls.
I'd like to build a query that excludes all records that contain any number from a small list of numbers.
This sounds very simple but I am having problems when trying to include records that have Null's in my query output.
For test purposes I tried to exclude all records that contain the numbers 1 or 9 (these numbers can be present in any field).
This works perfectly, in isolation, on Field A (i.e. 1 or 9 but not Null are excluded from field A):
Code:
WHERE Table.A Not In (1,9) OR Table1.A Is Null;
When I try to copy the above, referencing fields B-D, I run into problems - no matter how I try to alter the Boolean operators.
View 2 Replies
View Related
Feb 5, 2015
Access 2010 database.
I am trying to get an average from several numbered fields, which some may occasionally be blank.
=Nz([JanUS],0)+Nz([FebUs],0)+Nz([MarUS],0)
I get an error message that says "The expression =Nz([JanUS],0)+Nz([FebUs],0)+Nz([MarUS],0) cannot be used in a calculated column. This is a hypothetical expression being used.
The real expression is ...
([GC A1 - 2]+[GC B1 - 2]+[GC C1 - 2]+[GC D1 - 2]+[GC E1 - 2]+[GC A2 - 2]+[GC B2 - 2]+[GC C2 - 2]+[GC D2 - 2]+[GC E2 - 2])/10
This expression works great until a field is left blank, and then the average box is left blank.
The fields I am trying to add are Long Integer. Would that affect it?
View 1 Replies
View Related
Jun 14, 2006
Hi,
Got the following question.
I have got two tables with the following values
Table X with Field ULX:
XAUUSD
XAGUSD
CAD
AAD
Table Y with Field ULY
AUDUSD
XPT
XAU
XAG
XAUUSD
XAGUSD
Now I want to select the values from table X that correspond witht the values in Table Y for which I use the INNER JOIN operation, wich is working and gives me the results I am looking for.
But now I want to select the values from Table Y that do NOT correspond with the values in Table X, when I rewrite the INNER JOIN operation but now with the <> operator, my results still show the values XAUUSD and XAGUSD from table X.
This is my query:
Select distinct Y.ULY from Y
Inner join X on Y.ULY <> X.ULX ;
What am I doing wrong?
Thanks.
View 1 Replies
View Related
May 2, 2014
I have an alphanumeric primary key that goes
REF0001
REF0002
REF0003
e.t.c.
When i get to REF9999 and enter REF10000 it does not store it after REF9999 but stores it after REF100. Why is this ?
Is there a quick way that i can add leading 0's onto my records. So it will read
REF000001
REF000002
REF000003
View 5 Replies
View Related
Apr 24, 2013
I have calculated fields on my tables and used zero values as default to show totals. But when I view my report, all the zeros appear. Don't want those zeros to appear on the report.
View 1 Replies
View Related
Feb 4, 2015
I've been using a series of 5 tables to export an XML file using Access's native XML export. In order to keep the XML intact, there are relationships setup to link each sets of elements together via an ID number(link) field in each table.
Access does a great job at exporting, but then we need to rely on another program to delete these link fields that links the tables together so our XML validates. Additionally, we use this program to reorganize some of the XML, as one of our tables puts itself at the end of the XML element and not in the middle where it should be. This is not a problem when I'm dealing with a few hundred records, as the 3rd part program does the trick. The problem is when I have enough data to generate a 30+MB file that my other program chokes on when trying to manipulate the XML.
So my question is...how can I export these 5 tables to XML while omitting the linking member fields and organizing it how it should be? I have examples of code below. How Access Exports it
Code:
<MASTER-RECORDS>
<RECORD-ID-NUMBER>5648743524654</RECORD-ID-NUMBER>
<RECORD-ID-NAME>JOHN SMITH</RECORD-ID-NAME>
<link-for-pseudonyms>123456</<link-for-pseudonyms>
<PLACE>USA</PLACE>
<DAY>MONDAY</DAY>
[Code] .....
View 5 Replies
View Related
Aug 19, 2006
I have the following situation:
1. A Homeroom table which includes student id, academic year, and homeroom.
2. A Student Grades table which includes student id (linked to Homeroom table), term, academic year, progress grade (15 pts), and progress grade (A-F).
The contents of these tables are imported from excel, and that works fine except for one thing: in order to keep the field [progress grade (15 pts)] numeric, I had to put a 0.0 as a grade for any student who got an "I" (Incomplete) or "N/A" as his or her progress grade.
The problem arises when I want to produce an average progress grade for the student for that given term/year. How do I not include the 0.0's of students who got I's or N/A's?
Any help would be much appreciated.
View 7 Replies
View Related
Nov 5, 2007
Not sure if this belongs in Queries/Form/Reports because it has to do with calculating formulas within all of the above but here goes anyway:
1- I've collected data that is entered by week number via a form.
2- The data utilizes fields that are to be used in calculating formulas, which I am familiar with.
3- I WANT to average the most recent 2-x weeks, (primarily, I will use the most recent 3 but would like the ability to calculate any given number), of the CALCULATED FORMULAS mentioned above to be used in reports/queries.
4- In other words for example, field a/ field b = X for one given week. Now I want to know the average of field a/ field b for the most recent x weeks.
Is there a way to perform this action? If so, can anyone help me on completing it?
Thanks,
Doug
View 5 Replies
View Related
Jun 7, 2007
Ok, here's the scoop. I have the following record setup for tracking shipments:
[Shipment_ID_#], [Date_Loaded],[primary_silo], [secondary_silo], [2_silos]
I need to retrieve average values from another table for certain chemistry values based on the [Date_Loaded] and whether or not one or two silos were used when loading the shipment (as indicated by the [2_silos] checkbox).
Scenario #1 - one silo
I need average values of the last four samples based on the [date_Loaded] and the appropriate silo.
Scenario #2 - two silos
I need average values of the last two samples from each silo based on the [date_loaded], [primary_silo], and [secondary_silo].
Would anyone have any thoughts on this? I've tried crosstab and select queries, but I can't get anything to work.
My ultimate goal is to have a form where users select the shipment and the appropriate chemistry averages automatically populate the data fields.
Any help is greatly appreciated.
Thanks,
Greg
View 1 Replies
View Related
Aug 2, 2007
a request came accross my desk to find holidays sales in relation to the same days of the week for other weeks. For example, Thanksgivings sales compared to each of the 2 thursdays prior and the 2 thursdays after. Anyone know of a way of pulling this data so I have it all in one query - Thanksgiving, Thanksgiving+1, +2 and -1 and -2 (the numbers representing weeks from holiday
Thanks.
Brian.
View 1 Replies
View Related
Nov 15, 2006
We have a billing scheme in which we partial-bill ceratain customers based on certain events: 30% when they sign the contract, 30% a number of days later, 30% when we ship the goods and the last 10% upon installation. This is a common process, known in many indistries as 'progress billing.'
What I am trying to do is compare the collection performance on the invoices based upon which trigger sent them. For example, I'd like to find the average and standard deviation (plus some percentiles) of the time it takes to collect invoices that are sent... when we ship, let's say.
I have a table that shows order number, invoice number, invoice date, amount and payment date. So, I can have the order number show up to 4 times with a chronology of invoice dates.
My question is this: How do I group for analysis all the FIRST invoices, the SECOND ones, etc...?
Thanks for any help! I can do this in excel... but I was given this extract of 100000+ lines, and hope to do it all in Access. Thanks again.
View 2 Replies
View Related
Apr 15, 2008
I have a table in which each record includes a field showing the time that an event started and then another field which stores "how long did it take", these two fields are both defined as a short Time. I then have a query that runs through the "how long did it take" fields to produce an average time for the whole sequence of events. Two problems:
1. The average comes out as a decimal like 39553.367942 instead of several hours and minutes.
2. When I try to access this value using DLookup on a form I get "#error"
Any suggestions?
thanks
View 2 Replies
View Related
Nov 15, 2005
Hi there,
I have 4 fields in a table(Call_Freq, Call_Week, Call_Day, Call_Sequence)
I have to make a query that finds records with Null values in these fields.
If they are all null that is fine so I only want to return records with either one, two or three of the columns with Null values.
Any help would be appreciated.
View 3 Replies
View Related
Feb 15, 2007
Hey guys,
I'm doing a pretty basic query that
Selects about 5 fields
From Table1
Where
(value=0 AND name <> HOT)
OR
(value=Null AND name <>HOT)
I also have some records (4 specifically) that have a name = nullvalue. When I run this query it picks out all the records with a name, and skips HOT(which is correct), but skips the nulls which I also want to include. Can someone help me in finding the reason for this error?
View 5 Replies
View Related
Feb 23, 2005
I already posted this problem in the query section, but got no where with it... here is the problem now...
I have a form with three combo boxs - cboStatus - cboFunder - cboResource on my form. Depending on what you chose as an option the subform, which is based on a query will show the data with the chosen criteria.
Only the Status field is a required field, so there are lots of nulls in the funders and the resource fields.
In the all the fields I have "ALL" or "*" as an option.
So in the criteria in the query I use this...
Like fCboSearch (forms!form name!combobox)
This calls up this function...
Public Function fCboSearch(vCboSearch As Variant)
If IsNull(vCboSearch) Or vCboSearch = " " Or vCboSearch = "*" Then
fCboSearch = "*"
Else
fCboSearch = vCboSearch
End If
End Function
This works great to show me all the records or just records with a certain criteria selected, BUT BUT BUT I cannot get it to show my the NULLS, they are no where to be found. How can I change this to show me NULLS??
Thanks. Joye
View 5 Replies
View Related
Jul 7, 2005
I have a table which records are appended to in batch format. I would like to update a column called batch number for newly appended records with the Max of that column + 1 but I keep getting errors such as "operation must be an updateable query". Anybody have any suggestions. Mind you I am not doing this in VB or SQL formats, I use the query design format. Thanks, Jim
View 3 Replies
View Related
Jan 2, 2006
hi,
i am having trouble with a criteria expression that seems to have an issue when it runs into null data in my table.
the query column is as below ...
DateSerial(Year([SomeDate]),Month([SomeDate]),1)
and the criteria for this column is ....
DateSerial(Year(Date()),Month(Date()),1)
So basically I am looking for those fields that match the first date of this current month.
for some reason when there are null values in the table this thing chucks back data type mismatch error but when i take the criteria away its all good
any ideas why this is happening?
View 1 Replies
View Related
Oct 25, 2005
In my Accounts table I have a field called Status. This field is populated by single letters. A=Active, P=Paid and so on. I have a status (R) that is occasionally used. It won't show up in my crosstab queries since we currently do not have an account that is in R status. How can I get my crosstab to show the R status as having 0?
View 3 Replies
View Related
Dec 24, 2005
I've been struggling with a problem and I hope one of you can please help.
I created a crosstab query that displays group names on the vertical axis and dates on the horizontal axis. I use a Count for the values. I use a pop-up dialog to get parameters for the dates (between... and), which are organized as Quarters. The query works fine and I can insert it into a report, which also works fine... with one problem. The dates only work when the date parameters request a year (4 quarters), and only work when each quarter has at least one entry. (Only work = I get an error message when one or both of these conditions are violated.)
Sometimes, I want to look at less than a full year, but when I use parameters for three or fewer quarters, I get an error. Same thing if I request a quarter that doesn't have any data. I assume the solution is to convert Nulls to Zeros and have wrestled with both Nz function and IIf, but have not been able to convert the values. I get the correct values in the quary's dataset if they are numbers, and blanks if no data, but no zeros anywhere. I also don't know how to make my report flexible or dynamic, so I can limit the number of quarters if I only want to look at a specific quarter or quarters (less than four).
Any ideas? Thank you so much for your time, and, Happy Holidays!
Stu
View 8 Replies
View Related
Feb 7, 2008
is there any way to make a cross tab query default to zero instead of nulls for unpopulated cells
i know i can Nz all the resultant fields, but it seems like treating the symptoms and not the cause...
:confused:
View 5 Replies
View Related
Feb 7, 2008
So, I've been doing doing reading on Append Queries and Nulls. I have the following SQL statement
INSERT INTO tblTestResults ( EnrollmentID, TestType, Result, CompletedDate, BoldFace )
SELECT tblEnrollment.EnrollmentID, tblTests.TestsID, [Forms]![frmHome]![subfrmTesting]![txtResult] AS Result, [Forms]![frmHome]![subfrmTesting]![txtCompletedDate] AS CompletedDate, tblOptionsBF.OptionsBFID
FROM tblEnrollment, tblOptionsBF, tblTests
WHERE (((tblEnrollment.EnrollmentID)=[Forms]![frmHome]![EnrollmentID]) AND ((tblTests.TestsID)=[Forms]![frmHome]![subfrmTesting]![cboSelectTest]) AND ((tblOptionsBF.OptionsBFID)=IIf(IsNull([Forms]![frmHome]![subfrmTesting]![cboSelectBF]),0,[Forms]![frmHome]![subfrmTesting]![cboSelectBF])));
and I can't for the life of me figure out.
IIf(IsNull([Forms]![frmHome]![subfrmTesting]![cboSelectBF]),"",[Forms]![frmHome]![subfrmTesting]