How Do I Get The Average Speed Answered Based On The Sum Of Two Columns?

Oct 19, 2005

I am trying to get the Total average speed answered (ASA) based on two columns sums. (i.e. sum of calls handled and sum total speed answer). I need to get the total avg speed ans (formula is sum of total speed answer (tsa) divided by sum calls handled). I did an average query for asa but it gives me the avg for the rows(records) of the asa and not of the two columns. I need it for the two columns above. I was trying to change the formula in report design view, but I am not coming out with the correct answer. Are there any suggestions?

Please help!

View Replies


ADVERTISEMENT

Average From Multiple Columns

Sep 25, 2007

Hi,

I've got all the columns i require in my query and and i've averaged these, however i need to create a final column which contains the Average of all the other Column Averages.. if you get what i mean!
Put it this way i have columns A, B, C. All the numbers containd within each column have been averaged so now each column contains a single value. ie.

A = 10
B = 20
C = 9

So now i have to create column D which will contain the average of those results above i.e.:

D = 13

I would preferable like to contain this within a single query rather than having to write a vb module and having to add extra tables ect.. but that might be the only way!

So if anyone could tell me the best way of achieving this it would be much appreciated. I've tried a couple things like concatenation:
Average: [Score1]&[Score2]&[Score3]

but this hasnt work.. no surprises really!

Cheers

View 14 Replies View Related

Reports :: Take Total Of Columns And Get Average

Jan 13, 2015

I have two columns in my report that at the bottom it gives me the following total, as I put below. I am trying to take the two totals that is gives and get the percentage. 1058/3024 = 35%.

=Sum([TotScrwBlank]) =Sum([TotalAmount]) .349 or 35%
1058 3024

I had did the equation as =Sum([TotScrwBlank])/([TotalAMount]), but this is giving me the answer of 46% which is not correct.

View 3 Replies View Related

Simple Question Probably Asked And Answered Previously

Sep 12, 2005

Trying to fix a simple problem with some tables.

In my department, we recently switched servers and from Windows 98 to Windows 2000. We had a database that got converted somehow during the transition. One person didn't get their computer switched until about a month later.

In essence we had 2 databases of the same information going for a while. Now everyone has the same database. Problem is the following:

Simply put:

We have 10,000 records autonumbered in one of the databases. The other database has 9,500 with the last 100 or so being information that the 1st database does not contain. How can I update my main table to have all 10,100 records? I have tried an append query and it doesn't seem to be working right. I have also tried to 'find unmatched' but can't seem to make that work either. There has to be an easy solution to make it work in one step.

Any help would be great. Thanks in advance. :confused:

View 1 Replies View Related

Ranking Of Student Based On Their Marks And Average

Aug 26, 2013

How to query the example in the image below. My problem is:

1. If the student get a mark 49 below in any of their subject they will not be included in the ranking.

2. If all of the student mark is above 49, I need to rank the student base on their average.

View 1 Replies View Related

Running Average Quote Based On Part Number

Apr 28, 2005

I've been searching all the forums and unable to find my answer. What I want to do is show on a form the average quote for the specific model number.

On the form there is customer info, product info, etc. I want to see the average of what we have quoted every other time we have seen this product (we do repairs). The identifer for the product is the model number, Model#, the quoted cost is PRICE. I've used this function before, DAvg("[Price]","Repairs","[Model#]='013003020'") - which of course gives me the average for one specific model number (013003020). How would I use a similar function but have it do the average for the model number that I am viewing on the form?

If I am able to do that - I also need to find a way to not include nulls and zeros from the Price field.

View 3 Replies View Related

Reports :: Average Contents Of A Field Based On Another Field

Oct 2, 2013

I have a report that has multiple fields - 3 of which matter for this discussion -

1.) employee
2.) employer
3.) rateofpay

The employer field contains 1 of 2 options. For the sake of this conversation, lets call it ABC123 and XYZ123

I need to be able to calculate the average rate of pay for ABC123 company, and exclude the rate of pay for XYZ123 company in my report.

I am calculating this in a section footer.

View 2 Replies View Related

Sum Query Based Off Two Columns

Apr 15, 2008

I have a problem trying to sum a numeric column based off another column in the same table.
What I am trying to accomplish is to sum a numeric column if a corresponding column is Not Null.

I have uploaded my sample DB that to illustrate my problem. The query I am testing with is called Query1 and I am looking at summing point values from the task table grouped by the test case those tasks are in. I can easily sum the total number of points for each test case. But when I add another field to the query to sum the point values of the task in the test case that have a date entered into its corresponding date field it will not sum them correctly.
The result I get is the same sum total is shown for all three fields in the query.

I have tried several attempts at making criteria for this to work but nothing seems to work the way I want it too.

Here are my attempts:
IIf(Not (Sum([Task].[Points]))=IsNull("Attempted_Actual"),Sum([Task].[Points]),0)

IIf(Not (Sum([Task].[Points]))=IsNull("Completed_Actual"),Sum([Task].[Points]),0)

Not IsNull("Attempted_Actual")

Not IsNull("Completed_Actual")

My question is:

Is it possible to do want I want all in one select query or would this be something to code in a VBA module and then just call that module in a report?

My plan was to do this all in a query then build a report to display this query.

I have attached my dummyDB file and you can look at the Test_Case Table and expand each test case to see the data I have populated.

Any guidance would be appreciated.

Thanks,

View 4 Replies View Related

Combo Box Based On 2 Columns

Feb 15, 2006

Hello everyone,
I have a combo box on a form tha displays information based on 2 columns from a table.What I want the combo to do is to allow the user to select the row he wants and for the field that is bound to the combo box to store the outcome of collumn1 + collumn2 (string values).
Is this at all possible and if so how can I do it cause I can't find a way.
Thanks for all your time and effort :)

View 3 Replies View Related

Combo Box Based On 2 Columns

Feb 15, 2006

Hello everyone,
I have a combo box on a form tha displays information based on 2 columns from a table.What I want the combo to do is to allow the user to select the row he wants and for the field that is bound to the combo box to store the outcome of collumn1 + collumn2 (string values).
Is this at all possible and if so how can I do it cause I can't find a way.
Thanks for all your time and effort :)

View 3 Replies View Related

Searching Database Based On 3 Columns

Aug 23, 2011

I have a requirement of searching the database based on 3 columns. I have attached the search form and the code.

Private Sub cmdSearch_Click()
Dim strSQLHead As String
Dim strSQLWhere As String
Dim strSQLOrderBy As String
Dim strSQL As String
Dim strJoin As String

[Code] .....

View 12 Replies View Related

Modules & VBA :: Create Columns Based On Subgroupings

Nov 19, 2013

What I'm trying to do is create columns based on subgrouping. See attached example in excel.

It's almost like transposing but not it.

The names of the columns don't really matter. It's to be used in a dynamic excel workbook for looksups.

View 5 Replies View Related

General :: Calculated Columns Based On Another Table

Aug 18, 2012

Is it possible to have a calculated column based on another table. i have a 1 to many table. the table that is the parent hold a start time. all of the 'many' have an individual time length. i would like a calculated column in the parent table showing the full length. is it possible or will i have to use an update query?

View 1 Replies View Related

Finding And Removing Records That Sum To Zero Based On Two Other Columns?

Apr 3, 2013

I have a dataset of about 70 columns but would like to remove any two rows that sum to zero (based on two columns) if the column "Units" are the same for the same "Bar code" thus to say "for the same 'Units' (for any two lines), check to see if their respective 'Bar Codes’ are the same and if so remove if their amounts sums to zero".

I have the following code which seems to be working but for relatively large data ( about 5k), it does not select all the data that meets the criteria:

Code:
SELECT *
FROM datatable
WHERE ((([datatable].[Unit] & [datatable].[Bar Code] & Abs([datatable].[Amount]))
In (SELECT datatable.Unit&datatable.[Bar Code]&abs(datatable.Amount)
FROM datatable
GROUP BY datatable.Unit&datatable.[Bar Code]&abs(datatable.Amount)
HAVING count(*)>= 2 and sum(datatable.Amount)=0)))
ORDER BY [datatable].Unit & [datatable].[Bar Code] & Abs([datatable].Amount);

identifying the bug in this code that makes it not pull all the data meeting the criteria.

See sample data below:

# Unit bar Code Amount
1. AAB Mac1 2.75
2. AAB Mac1 -2.75
3. AAB Mac1 1.24
4. AAC Mac3 35.00
5. AAC Mac3 -20.00
6. AAC Mac3 20.00
7. AAD Mac3 16.11
8. AAC Mac2 11.00
9. AAC Mac2 -11.00
10 AAC Mac3 12.05
11 AAF Mac3 -12.05
12 AAD Mac3 -16.11
13 AAC Mac4 35.00

I want to select records # 1, 2, 5, 6,7,8,9 and 12 since they are same units having the same bar code and their sums total zero.

View 6 Replies View Related

Calculations Based Columns In Entry Form

Jun 8, 2012

I've got a data entry form that is used to enter incoming waste consignment data. (I've attached a screen grab of the form) Each screen represents one record and the data is recorded in a table called "IncomingWaste". The top of the form (light blue) refers to the customer/producer of the waste. Most of that detail is held in another table on the db. The bottom (Dark Blue) part is used to record the consignment data. It gives the user a grid to enter the individual waste components within the consignment (choosing from Drop down menus for the various descriptive elements) Most crucially, the weight of each component is recorded.

I have a calculated field at the bottom which gives a Total weight for that consignment. It simply adds the weight fields together and delivers a total in the box at the bottom of the screen which displays the total weight in that consignment using "Nz([weight1])+Nz([weight2)]+ ...etc - which works fine on the form. However, I can't seem to get this calculated total recorded and appended to each record in the table and I assume it must be re-calculated each time the screen loads (?)

The main issues is.... I need to create a report which adds the total weights from each customer over a monthly/quarterly period and I simply cannot seem to do it.

I have created the report which groups all the consignments from each customer and lists them in date order, but I can't identify or define the method I need to use to collect that Total Weight calculation from each consignment record and add them up to give a grand total for that customer for a particular period. I'm assuming if I can crack the first part and get the total recorded in the table, then I can call the value into the report.

View 8 Replies View Related

Can You Create New Column In A Query Based Off Of Data From Two Columns Already There

Feb 4, 2008

I will need to create a new column in a query with data based off of two columns currently in my DB.

Current fields are: [TestType] & [TestReason].
New TEMP field will be [TestCombo]

TestType has 4 possible option via drop down.
TestReason has 21 possible options via drop down.
TestCombo will be one of 10 options depending on the data in TestType & TestReason.

examples: (here is an example of how the combo field will be populated. There will be ten total rules like this. one for each possible text option in the combo box.)
If [TestType] is "DRUG" & [TestReason] is "PRE" or "RAND" or "PA" then [TestCombo] would be "DOTDT".

If [TestType] is "ALCOHOL" & [TestReason] is "PRE" or "RAND" or "PA" then [TestCombo] would be "DOTAT".

If [TestType] is "DRUG" & [TestReason] is "N-PRE" or "N-OTHER" or "N-FUP" then [TestCombo] would be "NDDT".

The data does not need to be saved anywhere at all in the database, just generated when pulling this query. It will then be exported and dumped into another system.

Thanks. Here is a temp copy of the database with some junk data to maybe make it a little more clear.

View 9 Replies View Related

General :: Hide / Show Columns Based On Combobox

Jan 28, 2014

I would like to know if the following is possible: I have a table, I have a form based on that table (datasheet view) and I would like for certain columns to visible or not based on the input on a Combobox (list of countries) that is based on another form.

I have searched on the web and I found that the following:

On a button based on the main menu form:

Code:
Private Sub Go_Click()
Dim stDocName As String
Dim stOpen As String
stOpen = Me!cboDest
stDocName = "Datasheet"
DoCmd.OpenForm stDocName, , , , , , stOpen

and then on the event "on open" of the form Datasheet I have inserted the following:

Code:
Private Sub Form_Open(Cancel As Integer)
Select Case OpenArgs
Case "USA"
Forms![Overflow]![Tasks].Visible = True
Forms![Overflow]![Constant Number (Min)].Visible = True

[Code] ....

I have done it as a test, but it appears that it does not work. It opens the forms, but it does not hide or show the columns based on the option, it brings all the columns.

View 14 Replies View Related

Queries :: Query That Uses Different Columns Based Upon User Input

Sep 10, 2013

I have a database table in which I'm trying to pull sales data and generate sales reports from. The problem I face is that the sales data is recorded into a table with this structure:

Year | Customer | Sales_Month_1 | Sales_Month_2 | Sales_Month_3

Rather than having a single field "Month" in which I could set criteria or prompt the user to select a month to derive sales data from, I need to write a query that through user input (or through some code within the query) the right field will be selected. An example of this would be generating a report for the 1st month of the year, obviously.

View 1 Replies View Related

Reports :: Dynamically Include Columns Based On Check Box

May 28, 2013

I have a report with many columns in the detail listing data, and their associated titles above in the page header.What I would like to accomplish is the ability for the user to check boxes off on a form which will remove/add specific columns to the report. The form will essentially be a "build your report" style, with a button on it opening the print preview of the custom report.

Im assuming this is accomplished by the "on load" part of the form through VBA. How is the sizing handled? If the user un-check's a box to remove a column of data, how is the header and detail data "moved over" or re-arranged to accomplish a relatively decent looking layout? Any example or example code to deal with one column/setup (which I can copy and modified)??

View 4 Replies View Related

Getting Data From Rows To Columns Based On Common Fields

Jul 30, 2013

I have a big Excel file with payroll information about employees. Per employee per date, there are a couple of rows with mostly identical data (such as the employee's home address) but 2 differences: one will have paycode A with amount B, another will have paycode C with amount D, etc. I want to simplify the file to have 1 row per employee per date: in the example given that row would have paycode A with amount B as well as paycode C with amount D, in 4 separate columns.

I think I can technically solve this by creating Excel files per pay code/amount, and then linking them together by person and date, 1 by 1, through Access. This is feasible because there aren't that many different pay codes. But still, I was wondering if there was anyway to solve my problem in Access itself.

View 1 Replies View Related

Queries :: Filter Query Based On Some Criteria In Multiple Columns

Jul 25, 2013

I am working in MS access 2007.

What I am trying to do is fairly simple i just dont have the ability to correctly code what i want to do.

I want to filter my query based on some criteria in multiple columns. But i only want the query to filter based on the specific criteria if a checkbox has been selected.

Basically i want the criteria for one of the columns criteria to read

IF a check box "Check0" is selected THEN filter the column to only records that = 1 and if "Check2" then filter all records that = 2

View 5 Replies View Related

What About This Average?...Field Average...

Feb 16, 2007

Hi guys!!!!

I try to find an answer in the forum about "Average Fields",but ican't
I am confused:(

I wan't to export Avg Of the fields like in the panel below:

View 2 Replies View Related

Forms :: Hiding Columns On Subform Based On Multi-Value Lookup Field

Aug 7, 2013

I want to use VBA to hide columns in a subform based on what is checked in a multi value look up field.

I am creating this DB for use with sharepoint as a web database, which is why I am using the lookup field to begin with. There will be a client DB to use with some VBA code

So what I have is a lookup field with tests "Test 1, Test 2, etc" on form sample.

There is a subform called results, and I want to hide certain columns based on what tests are performed.

I tried using an If Then statement (code is being run on subform load)

If Me.Parent.fieldTest = "Test 1" Then
Me.Test1Col.ColumnHidden = False
Else Me.Test1Col.ColumnHidden = True

That is basically the code I was trying to use. I am getting an error 13, which I assume is because fieldTest can not = something since it is a multi value look up field.

View 3 Replies View Related

The Need For Speed

Oct 18, 2005

Hi,

I have a growing Access database in a multi-user environment over a 10/100Mb network. The database is all in one file at the moment, on a shared directory of our XP-Pro 'Server', and the workstations have a mapped drive to it and are W98SE machines. All the machines are 1.2Ghz Fujitsu Siemens machines.

It is still under development, but is also in constant use, and I therefore have to develop on a copy, then get everyone out so I can copy in the changes. I would love it to be a client/server setup and split the db to Tables only backend on the server and progams on the client, but when I tried, the result was a dramatic slow-down in the system...it became unusable.

I do have a budget for this, and could get a proper 'server' or maybe an Ethernet Disk, but what is the best config for speed and admin purposes. Anyone doing something similar??

Thanks

View 14 Replies View Related

Speed Of Calculation

Apr 5, 2007

Hey,

I noticed something strange in access 2000: sometimes it takes a long time to calculate a report and other times it goes rapidly. I don't see any process taking a lot of CPU %.

When i do the same thing in Access2003, it goes rapidly every time.

Can anyone help me?

Rik

View 1 Replies View Related

Query Speed

Nov 2, 2005

I have a number of queries which build two or three Union queries which looks at 35,000 records, and when you open the Union queries or run reports it takes forever for them to open.

I have indexed all the tables which have the common fields to see if this speeds it up the queries, but there still slow. The table they look at are in two different tables which are linked into a front end where the queries are!

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved