Is Conditional Averaging Possible?

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 Replies

Averaging In Queries

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

Query Averaging Help Needed

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

Averaging Totals Of Certain Days With A Month

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

Numerous Invoices On Orders - Averaging By Chronology

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

Averaging Date/time Fields With A Query

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

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 2 Replies View Related

Tables :: Averaging Calculated Fields While One Field Is Blank

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

Conditional Formating Help Please

Apr 17, 2006

Good morning, all...

I have an unbound box on a form and, within the box are 25 strategically placed "X" marks (text fields) named X1 - X25. I want only one of the X marks visible based on the value of two fields, i.e., if field "Impact" = 0.5 and "Probability" = 0.3 then X23 will be visible.

Could someone please get me started with the code to accomplish this?

Thanx in advance for your assistance.
Karen :)

View 2 Replies View Related

Conditional Formating

Jul 13, 2005

Trying to setup a conditional format for a field on a form based on conditions from another field.

I have a form with [Field1] and [Field2]

I have a 3rd field called [New SMF]

What I want to do is have conditions on [New Field] so that if the value is greater than Field1 or less than Field2 the background of new field turns red.

How can I do this?

Jon

View 4 Replies View Related

Conditional Formatting

Apr 18, 2006

Hi Guys,

I've a question and I hope you can help me out.......
I'm trying to use Conditional Formatting Options on a Text box called "Date". I'd like that this box become Red if its value is between value1 from Text box "Start Date" and value2 from Text box "End Date" or Green otherwise (in this way the color change dynamically every time I change the values of Text Boxes "Start Date " and "End Date"). How can I do that? What I need to type in the Conditional Formatting Windows?

Thanks a lot for you help!!!!

Ciao Ciao

View 1 Replies View Related

Conditional Formatting

Aug 13, 2007

Hello

On my Database I have used Conditional Formattion to change the colour of the qty to highlight when the qty is getting low however its not as good as I would hope as there are different type of definition of quantity (D of Q) e.g. Each (single items such as a screw) and Roll (such as cord and tape)

Conditional formatting doesnt work well because if I have it as less than 10 the qty is in red, its ok for the each items however not the roll as 10 rolls of tape is more than we would need.

I am just wondering if anyone knows what the code would be to change the colour depending on the qty and the D of Q

All i have got so far is:
If me.qty <10 and DofQ = EA then

Now im stuck for the change colour part

Any help would be great

View 13 Replies View Related

Conditional Formatting Help

Sep 2, 2007

Hi Guys,

I'm after an easy way on being able to change the colour of a field once it a certain figure is reached

For instance

Standard field colour for anything 1-99
100+ changes to red

Would any be able to help me out please?

View 2 Replies View Related

Conditional View

Mar 2, 2005

hi all,

how do i create view based on a condition.

For example my database consists of a Employee table
*Token ID
Name
AGE
Location
Division.

Now the users of this table would be employees from different divisions.
And i want to create a view for each division for records only belonging to that particular division. And use this view to update records of that division.

Or should i create tables for each division and then give access of each table to the respective division user.

Any response is welcome..

View 2 Replies View Related

Conditional Formatting

Sep 8, 2005

Does anyone know if there is a way in a report to display an entire row in a different color? I'm running a report based on a query and I need specific information to come up in RED. I read about conditional formatting but you can only apply it to a field in a report, but not to an entire row. Let's say I have a group of managers and I need to know the ones that are making more than $50,000 a year. I don't just want the salary to come in RED, I need the entire row (like name, last name, title, date of hire, salary, etc).
Any ideas?

thanks!

View 2 Replies View Related

Conditional Query Using Between

Jul 13, 2006

Hi - I wonder if anyone could possibly help, I have a list box that is populated by a query, which currently populates the list according to which site is selected within a combobox - what i would like to do in addition is to have a check box which controls how much data is shown, ie, if the checkbox is ticked show all data from April 1st onwards, if not ticked to only show the last 12 weeks (84 days)

I have tried to acheive this using the code below:

[Forms]![frmPccAnalysis]![cboSelect] --is used to select the site--

IIf([Forms]![frmPccAnalysis]![chkFilterLimit]=-1,(Between #01/04/2006# And Now()),(Between (Now()-84) And Now())) --is what i am trying to use to either restrict or show all data--

when i run the code i just get empty fields, if i manually enter the "Between #01/04/06# and Now()" statement in the criteria it works fine, just not with the conditial checkbox. Can anyone please point me in the right direction?

Many Thanks

Ian

View 4 Replies View Related

Conditional Join

Apr 14, 2007

I want to join two tables together base on two criteria.

Table Customers
(Ids, Name, ...)

Table Addresses
(Ids, Billing Address, Shipping Address...)

I want to join Customers Table to Addresses Table base on this condition,

If Billing address exit,then join Table Customers with Table Addresses,

If Billing address is NULL, then join Table Customers with Table Addresses.

Can someone help me understand the query syntax?

View 1 Replies View Related

Conditional Query??

Aug 24, 2007

My Access is very rusty and I really can't get my head round how to do this!

I have a simple caller i.d. system which detects incoming telephone calls. I have been running an append query where the incoming call number is a query parameter against a table of customer numbers and names. The append query basically adds call numbers, names and times to a call log table.

This all works fine! ...however I realise that if the incoming call number is not in the table of customers numbers and names, no log entry is created. What I wish to do is, if the number is not found to append it anyway together with the time of call. Basically if the query fails to find a result, append details anyway albeit without a customer name.

I'm sure this must be simple but I'm going round in circles. Any thoughts anyone?

Thanks

View 3 Replies View Related

Conditional Join

Oct 10, 2007

Hi,

I need a help. In my Store table I have Store# and MaxRange. In my CenterStore table also I have Store# and Max Range.
Data in Store table will be like
Store# MaxRange
10 1
20 2

Data in CenterStore will be
Store# MaxRange
10 A
20 B

The Store# is common between the 2 tables but the MaxRange differs.
Now in my employee table I have EmpId, Grade and Store#
I have SalaryRange table which has Grade and MaxRange.

My query is how to attain this with access

Select E.EmpId,E.Grade,MaxRange
From Employee E
Inner join Store S
On E.Store#=S.Store#
Inner join CenterStore CS
On E.Store#=CS.Store#
Inner Join SalaryRange SR
ON (E.Grade = SR.Grade AND S.MaxRange = SR.MaxRange)
OR (E.Grade = SR.Grade AND CS.MaxRange = SR.MaxRange)

I know this is possible in SQL. What is the equivalent in Access 2003?

View 2 Replies View Related

Conditional Calculation

Oct 23, 2007

I currently have two queries. One calculates emissionsperhour based on a results field and the other calcualtes emissionsperperiod based on the emissionsperhour query and the inspection date. I need to go a little deeper and seperate the emissionsperperiod into two categories. Category 1 will sum the emissions for the year up to 5 (Results). Category 2 will sum the emissions for the year from 5 and over.

My theory is this...in the emissionsperhour query create 2 more expressions which are Cat1lbperhour and Cat2lbperhour. Cat1lbperhour needs to calculate using a maximum of 5. Cat2lbperhour needs to first look for any results >5, subtract 5, then calculate lbperhour based on the difference.

Any ideas as to what this looks like in SQL?

Thanks

View 8 Replies View Related

Conditional Query?

Nov 30, 2007

i have a query from 2 table. let's say table a & b. i have one form to show the query. the query show data based on what enter on the form.

when someone enter a value, on form, the query use it as filter for displaying data. my problem is one field of my query show data from the second table.
if input is AA, the data will be shorted by AA plus showing first colum of table 2.
if input is AB, the data will be shorted by AB plus showing second colum of table 2. etc.

so far, i make several query for each kode to display the corect column.
can i use 1 query to do that? if not, what should i use? macro/module? please the example aslo.

thanks

View 1 Replies View Related

Help With Conditional Statement

Dec 27, 2007

Hi,

I have an access database that contains information on donations made to our non profit organization.

I'm wondering how I query for people who have donated in categories (a,b,c) between 01/01/05 - 01/01/07, but have NOT donated in categories (a,b,c) in >01/02/07.

In other words I'm trying to sort out the people who have donated to us in certain categories in the past, but have not yet done so this year.

There are fields in my tables for donor id, as well as donation type, and donation date.

Any help or info is appreciated!

View 7 Replies View Related

Conditional Formatting

Mar 13, 2005

I have 2 subforms on a main form. I would like to compare the value in field 1 og subform 1 to field 1 of subform 2. I can't seem to get the conditional foramtting between the 2 subforms to work. Any ideas?

Jim

View 1 Replies View Related

Conditional Formatting?

Mar 22, 2005

Good afternoon everyone
I am new to this forum. I have not done access development for about 6 years so I appologise for my rustiness.
I have created a form based on a query. The form is of type Datasheet. I have assigned Conditional formatting to the grid fields based on an expression evaluating a check box. This works fine and when I open the form the conditional formatting works great.

However, I am now trying to add this form as a subform in A.N Other form. The parent form has no datasource and is purely there to display some text values linked to unbound controls. I did this because apparently you cannot show form header for a datasheet view.
When I insert my child form into the parent there is no logical link. The behaviour of the form and subform works fine but the conditional formatting does not appear in the subform grid.
Why is this and how can I best sort this?
Any help would be appreciated, but please be aware I am not fluent in VB as I have been away for quite some time.

Bets regards
Philm

View 1 Replies View Related

Conditional Scrollbar?

Jun 17, 2005

I have a subform which shows records... usual stuff. However, when I turn on the scrollbar, it always shows the scrollbar... also when there are no or a few rows in the subform.

Can I make the scrollbar of a (sub)form conditional? -- so, if the form needs more than so many space because of records shows: it turns on the scrollbar.. else: no scrollbar.

Any help is appreciated,

Jazz

View 1 Replies View Related

Conditional FindRecord

Nov 3, 2005

I'm kind of banging my head against this one. I've searched the forums and outside, and think I have the idea, but Access say it can't find the forms.

I added an "Notepad" for end user to make notes of each clients if they wanted to. It is a pop up form, has its own table, with ClientID FK to the primary table's PK of same name.

The code I tried is

DoCmd.OpenForm "Notepad", , , Forms![Notepad]![ClientID] = ClientID Or acNewRec

It wouldn't accept an If... Else statement which makes it problematic, because I only want one note per client, so need to make sure Access check whether there is already a record that corresponds to the client, pull that one up. If there isn't any, then create a new record.

The other code I used allowed me to run and can get the ClientID, but does not create a new record when there should be a new one for a new client. It was

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm "Notepad"

Let Forms![notepad]![ClientID] = ClientID

End Sub

If I add an Do.cmd.Gotorecord, , acNewRec, it returns a error message that there would be a duplicate record, which is expected.

So how do I get Access to simply

1) Check if there is a note already written for so and so client then pull it up
2) if there isn't any for that client, create a new record

View 3 Replies View Related