Hi,
In my database, I have clients and client hours. I need to calculate average client hours. My total client hours expression is: TTotals: Sum((Nz([SessionHoursCompleted])+Nz([OptionalHoursCompleted]))). I need to divide this number by the number of clients.
Any suggestions ie sum of client number, sum of clients, count of clients.
I've tried these and the results are incorrect.
Thank You
I am trying to calculate the average patients age from 2671 records using this SQL:
SELECT tbl_Customer_Details.DOB, CalcAge([DOB]) AS Age, DAvg("[Age]","qryAvgAge") AS Average
FROM tbl_Customer_Details
GROUP BY tbl_Customer_Details.DOB, CalcAge([DOB]);
why am I getting the result:
68.1131066106
I would have thought that it would have been 68 a whole number, has anyone got any suggestions why this should be.
thanks
I have developed the following query to summarise a blast design at a mine site.
Pattern Depth m Subdrill Burden Spacing BCM/hole #Holes BCM
1.00 7.00 1.00 3.00 3.00 54 10 540
1.00 7.00 1.00 3.50 3.00 63 10 630
2.00 7.00 1.00 3.50 3.00 63 10 630
2.00 7.00 1.00 4.00 3.00 72 10 720
Now there will be many patterns, the above however only contains one (called 1). Now what i need is the average Depth, Subdrill, Burden, Spacing, bcm/h. In MS Excel I would have following:
Average BCM per hole = Sum of BCM / Sum of Holes
How to do this in Access? Then just to make it more difficult I want to have the average BCM for each different pattern.
Hi there,
once again I have a problem for which I am looking for some hints...
I still have one table, called tblTransactions, which contains security market transactions. For each buy and sell order, respectively it contains one data set with columns Date, Ticker (i.e. the unique identifier of each security), Quantity (positive for buy, negative for sell orders) and Price (at which the trade was executed).
The following code gives me all stocks, which are no longer part of the portfolio since they have been sold out completely for any arbitrarily chosen date (here 1/30/07) together with the date, on which the last position in a certain stock (identified by the ticker) were sold:
SELECT T.Ticker, max(T.Date) AS SellDate
FROM tblTransactions AS T
WHERE T.Date<=#1/30/2007#
GROUP BY T.Ticker
HAVING sum(T.Qty) =0
ORDER BY T.Ticker;
Now it becomes complicated: What I am looking for is a sub-query, which I want to add to the code above and which gives me the weighted average price at which the stocks were bought and sold, respectively if there have been more than one buy or sell transaction.
That is, for the following sample data of tblTransactions...
Date --- Ticker --- Quantity --- Price
01/01/07 --- AAA --- 50 --- $50
01/01/07 --- BBB --- 25 --- $75
01/10/07 --- BBB --- 75 --- $100
01/15/07 --- AAA --- 30 --- $60
01/20/07 --- BBB --- -100 --- $100
01/25/07 --- AAA --- -40 --- $120
01/26/07 --- AAA --- -40 --- $100
...the query I am looking for should give the following result as per 01/30/07:
Ticker --- SellDate --- WeightedAvgEntry --- WeightedAvgExit
AAA --- 01/26/07 --- $53.75 1) --- $110.00 3)
BBB --- 01/20/07 --- $93.75 2) --- $100.00
Notes / how to calculate the weighted averages:
1) (50*$50 + 30*$60) / 80 = $53.75
2) (25*$75 + 75*$100) / 100 = $93.75
3) (40*$120 + 40*$100) / 80 = $110.00
I do not have any clue at all how to solve that problem. I would be very happy for any hint that could lead to the right direction.
Best regards
JapanFreak
My database has 8 clients. During a sample date range, between 1/1/05 and 1/1/11, they worked a total of 348 hours. I need to query them for hours divided by client by date range. 3 clients, for instance, worked a total of 162 in the sample date range but the query is dividing the 162 hours worked
by all 8 clients. I need it to divide the hours in this case by 3. Needless to say, these numbers will change when a different date range is inputted but if the expressions are correct...
Currently, to calculate this number I am using this expression :
Averages: Sum((Nz([SessionHoursCompleted])+Nz([OptionalHoursCompleted])))/DCount("IDOC","spise clients_OLD_OLD").
A copy of the query 'Current Average/Total' is attached.
Thank you for your assistance.
Is there a way to calculate three different rolling averages in one query?
I just inherited a database where someone is using three queries to capture the same information only with different time frames. They were calculating a rolling three month average, six month average, and twelve month average. I would like to combine these queries into one to reduce time spent running reports from the database. All three queries are based on one table. One of the columns in that table is called "Month Start Date". That field shows the first day of the month when a call was entered. I can get the query to tell me the first month in the three month period and the first month in the six month period, but I can't get it to calculate the averages of the calls that fall in those time frames. Here is the SQL for the query I have now. When I try to run this, I get the error message that my formula is not part of an aggregate function.
Code:
SELECT DISTINCT DateAdd('m','-2',(Max([Month Start Date]))) AS ThreeMonthStartDate, DateAdd('m','-5',(Max([Month Start Date]))) AS SixMonthStartDate, Max([Month Start Date]) AS MaxStartDate, IIf([Month Start Date] Between [ThreeMonthStartDate] And [MaxStartDate],Avg([All Call Rate]),' ') AS ThreeMonthAverageCallRate, LIST_WITH_TNC.Device, LIST_WITH_TNC.Model, LIST_WITH_TNC.[Item Num]
FROM LIST_WITH_TNC;
Is there a way to make this work?
I have a database containing values in 16 fields. the fields are filled in over a period of three years. I would like to be able to calculate the average of the last four values entered, regardless of when in the cycle the value is required. I have tryed to use quereies but connot find away to assign the four fields to the expression so that it is the last four values and if four don't exist, avearage what values there are.
View 2 Replies View RelatedHi guys...
I have a problem...
In a query i calculate averages.How can averages looked like
Number,NumberNumber for example: like 2,35 and not like 2,346789?
Thanx in advance...
Johann
Right, So i've got a table which has records for the Top 40 Music Charts.
I want to be able to find out the averages for each song taken from it's positions in the chart over the weeks:
http://www.playfm.orcon.net.nz/Chart4.GIF
So essentially I get something that tells me
[Lenny Kravits] [American Woman] [24.5]
[Silverchair] [Ana's Song (Open Fire)] [41.1]
and so on...
Any ideas?
Cheers guys,
Alex.
I have a table containing about 120 records of 40 fields containing integer values. The values are 0 (for 'no experience'), 1 - 5 (for evaluation of experience) and 9 (for question not answered). I would like to generate a row of averages for the 40 columns.
Access includes the '0's when using the Avg function. (So 1,0,3,0,1,4 yields 1.5 (1+0+3+0+1+4 / 6) rather than the accurate 2.25 (1+0+3+1+4 / 4)). I can tackle this in two ways: I either convert all zero's to NULLs, as Access will not count NULL in an Avg function call, or I can do each column in a seperate query using a WHERE clause. I also have the problem of screening out the 9's. I'm reluctant to create 40 queries and then another to amalgamate the results as this seems a very silly way to solve this problem. I cannot convert both the zeroes AND the 9's to NULL as to do so would lose valuable data.
Can anyone suggest how I can obtain a full row of averages for the 40 fields, ignoring 0's and 9's?
Simple query for the right person!!!
Hi Guys,
Im in need of some help with a query that i am trying to set up. I have a table with data shown roughly below. And need to extract the average [call length] for each heading of [Call type] on a given date.
Ie. The query will be run and the date can be inputted. It would the display each category only once and the average of that category.
Call type |Call Length (Shown in Minutes)|Submitdate
Split Billing | 4 |27/10/07
Split Billing | 8 |27/10/07
Split Billing | 9 |27/10/07
Split Billing | 1 |27/10/07
Split Billing | 4 |27/10/07
Tariff changes | 2 |27/10/07
Tariff changes | 3 |27/10/07
Tariff changes | 8 |27/10/07
Tariff changes | 5 |27/10/07
Tariff changes | 5 |27/10/07
Billing Cycle | 1 |26/10/07
Billing Cycle | 2 |26/10/07
Billing Cycle | 3 |28/10/07
Billing Cycle | 20 |26/10/07
Billing Cycle | 15 |27/10/07
I need to create a query where in the end, I will have four rows of data based on based on two combinations of WaterSourceType and Crop.
I need the query to bring back the results of the average Top N (lets say Top 10%) for each combination.
I have tried this every which way and I can't seem to get it grouped like I want it. I NEED to have four distinct rows with the average of the ProfitPerBushel for each grouping.
Basically, what this does is show me the average profitablity of the top 10% in each grouping.
WaterSourceType | Crop | ProfitPerBushel
Irrigated | Soybeans | ProfitPerBushel
Non-Irrigated | Soybeans | ProfitPerBushel
Irrigated | Corn | ProfitPerBushel
Non-Irrigated | Corn | ProfitPerBushel
My database is set up to track call evaluations with 4 fields for number data (S, A/C, C/E and B) each of these have a possible point total. I also have a percentage field to track out of total possible points.When I run my query I get a list of each of the totals for each of the evaluations with the associates names (as expected).I take that query and try to run a report wizard to give me an average socre for each associate. and the system returns averages of 0 or an odd number that does not make sense.when I use the =Avg([fieldname]) process I get an accurate average of the total but can not get it to do a "subtotal" for each associate.
View 1 Replies View RelatedI'm not sure if this is the right forum. If not let me know and I'll move the thread.
Table 1
Date
Measurement A
Measurement B
Measurement C
Table 2
Table 1 ID Link
Data 1
Tables are linked 1-to-many from table 1 to table 2 by ID.
I would like to average the Data1 data per Table1 ID and report it with the Table 1 Measurements.
I am designing queries to return averages for quality test data.
I have this query that functions as I want it too [URL] .....
It returns the averages of all the values received for different tests for a lot number (the lot number criteria should be filled out as well)
When I want the query to be more specific and average only certain box numbers in the lot (that start with the prefix PB") the query does not return an average for box numbers starting with PB but splits them up, showing an average for PB1, PB2 instead of combining the data for those boxes into a single unified average ...
[URL] .....
Let's say I have a table sort of like this one: [URL] .... (Table 1)
What I want to do is make another table that references the first table: [URL] ... (Table 2)
I want the cells in the Average field in Table 2 to calculate an average of all the values for records in Table 1 with Color fields that correspond to the Color field in Table 2 (this makes a little more sense if you look at the pictures). I could do this in Excel, but then problems would arise whenever I would add a new entry to the database, or re-alphabetized the data, since Excel math is depends entirely on the positions of cells, and I want these averages to be continually calculated correctly and to change whenever I add related records to the database.
What is the best way to impliment a query in a form so that the user can view the query records, and have the option to print or save the selected record using command buttons?
I tried subforms but I could not get the command buttons to work in the subform after it went into the form, it wanted to print the entire form instead of the selected record from the subform.
So in a nutshell I have 3-4 queries that are built, and I want to have them show up on my form in a format that the user can scroll through the results and select a single record of the results and then print or save that individual record from the form, if such a thing is possible.
Thanks in advance
Todd
I am trying to do the good 'ol sales report (query) to include customers with no sales.
I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)
If I run a query to show customers (in the customer table) with account numbers, that works
An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).
How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?
I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?
In some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.
All is well and fine until source data changes and the pass-through query runs too long and times out.
If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table
Is there a way to open an Append or Make-table query in Design view without invoking the source query?
Hello All,
I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".
Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.
I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66'))
AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
UNION
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet)
Set db = wrkJet.OpenDatabase("DW", _
dbDriverNoPrompt, True, _
"ODBC;DATABASE=DW;DSN=DW2")
'Set rs1 = db.OpenRecordset(strSQL)
I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)
I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.
I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
I have a query where these are the fields:
ProductRevType
RevLag
RevFlowThru
CloseMoYr
ProjRevDate
CurrentMRC
ProjRevMRC
The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)
When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?
I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
View 9 Replies View RelatedI have a form which will be used as the basis to print a label.
It is bound to a query and when I open the form I pass over a 'where' condition to return 1 record. I then use the query to produce a report/label.
What I want to do is to update the form/query without updating the underlying tables to the query.
:confused:
I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....
First goal: to COUNT the number of times a TYPE of visit is made.
There are several different TYPEs but only interested in tracking 2 of them.
When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)
The SQL was written by Access not by me. :)
Here is an example of the Crosstab SQL (which is using a previous query):
TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE
SELECT qryTest2.CID
FROM qryTest2
GROUP BY qryTest2.CID
PIVOT qryTest2.TYPE;
-----------
qryTest2 SQL: (Grouping by to remove dups)
SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#]
GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In"))
ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;
Thanks for you time! :)