I apologize, I know this has been covered. But I just spent half an hour reading old posts and still can't quite decide how to apply it to what I'm doing.
I have a db that logs surgeries and all their details. One of the new things they want to do is be able to run a list of average cost for a certain surgery, since patients are always asking ahead of time how much it will cost. I have a query (and report that runs from it) that will list all the surgeries and total charges for individual ones for a date range the user specifies. But I can't figure out how to make it calculate an average charge for each surgery. I could if there were always a certain number to divide by, but of course there could be 2 of this type of surgery and 57 of that type.
I'm trying to create a query that returns 10-min average wind speed.
I have the logging date,time and the wind speed per second in the wind log table. Date and Time Wind Speed(mph) 28/04/2006 2:17:01 PM 10.5 28/04/2006 2:17:02 PM 10.6 28/04/2006 2:17:03 PM 10 ...
And I would like something like this from the query: Date and Time Wind Speed Ave 28/04/2006 2:17:00 PM 10 28/04/2006 2:18:00 PM 7 28/04/2006 2:19:00 PM 5 ......
I have a query with the fields dtDate (Date/Time in Short Date format), NC "Number of Chats" (Long Integer), and TCT "Total Chat Time"(Date/Time in hh:nn:ss format) from tblChats. Each date will have multiple NC and TCT values. This query is totaling them by date with the following SQL.
SELECT DISTINCTROW tblChat.dtDate, Format$([tblChat].[dtDate],'Short Date') AS ChatDate, Sum(tblChat.TCT) AS SumOfTCT, Sum(tblChat.NC) AS SumOfNC FROM tblChat GROUP BY tblChat.dtDate, Format$([tblChat].[dtDate],'Short Date') HAVING (((tblChat.dtDate)>=Date()-7)) ORDER BY tblChat.dtDate;
The query works fine. Now my question.
I would like the query to figure the average time per chat for each day.
I want to know if it is possible to get an output that adds a ranks the CompanyIds by average cost per each policy #.how companyID24 ranks, and I have the query set up where it only outputs Policy#'s that company 24 is ranked in (has sold product in).
I have a form that I want to use in order to work out a global figure for average travel time for my engineers (data coming from another database).
I have a form with two text boxes, which allow the user to define a date range. This links into my query which works fine and returns all records from that date range.
Now comes the troubling part!
On the click of a command button I want the label to change and display the average for my work time column form my query (which by the way is in decimal format).
Can anyone help me? I have tried searcing but to no avail
I am currently designing a dashboard for my team which works on projects, I have a main table which has main fields which i use for my dashboard
Project Type,Project ID (Primary Key), Submitted by, Project Start date, Project end date, Status
The tricky part is that the raw data has projects which got started couple of years back some got closed in the previous fiscal year but some are still being worked upon..I was able to put in a where clause to only display projects whose close dates either have null value ( projects which are still being worked) and where the close dates are >= 1/1/2013. My Dashboard needs to have the below fields
Project Type, Count of Projects, Count of Closed Projects, Average days to complete, Count of Active Projects & Average Active days
I was able to get it correct except the last field Average Active days as it is displaying Average no's even against the project type which doesn't have any active projects or where the active project count is zero.
SELECT TblWO.[Operational* Categorization Tier 2], Count([TblWO]![Operational* Categorization Tier 2]) AS [Count], Avg([TblWO]![SLA in Days]) AS [Internal SLO in Days], Count([TblWO]![Closedate]) AS [No Closed Projects], Round(Avg([TblWO]![Closedate]-[TblWO]![Work Order Submit Date Time]),2) AS [Avg Close Days], [Count]-[No Closed Projects] AS [No Active Projects], Round(Avg(Date()-[TblWO]![Work Order Submit Date Time]),2) AS [Active Avg Days] FROM TblWO WHERE ((([TblWO]![Closedate]) Is Null Or ([TblWO]![Closedate])>=#10/1/2012#) AND (([TblWO]![Status])<>"Cancelled" And ([TblWO]![Status])<>"Rejected")) GROUP BY TblWO.[Operational* Categorization Tier 2] HAVING (((TblWO.[Operational* Categorization Tier 2])<>" "));
I made a query to calculate the average of a column, suppose that I have 5 records in that column (46,35,0,19.3,12), when the query calculate the average it sums the total of the column and divide by 5 (that's 112/5 = 22.4), what I need the query to do is to divide by 4 because one record is zero (that's 112/4=28). I put in the criteria the following (Not Is Null And <>0), yes this will not show the column that has zero but it still divide by 5.
Can someone help me please???:rolleyes: I have to query for the avg balance from customer table, custbal field from a certain state... I've tried everything and cannot seem to get the expression correct. How do I do this???? any help is appricated :D
i have a quick question... I am having a slight problem with a query that i am using in my database... i am trying to calculate miles per gallon. The query i have set up looks at the previous records odometer value and subtracts the current odometer value to get the miles driven since the previous fill... Here is my sql code.
SELECT fuel_use_tbl.unit_ID, fuel_use_tbl.fuel_use_ID, fuel_use_tbl.fuel_date, fuel_use_tbl.gallons, fuel_use_tbl.odometer, (SELECT TOP 1 Dupe.odometer FROM fuel_use_tbl AS Dupe WHERE Dupe.unit_ID = fuel_use_tbl.unit_ID AND Dupe.fuel_date < fuel_use_tbl.fuel_date ORDER BY Dupe.fuel_date DESC, Dupe.fuel_use_ID) AS PriorValue, [odometer]-[PriorValue] AS [Miles Driven], [Miles Driven]/[gallons] AS Expr1 FROM fuel_use_tbl;
The problem with this code is this... lets say i drive a brand new vehicle (its first record and it has no prior odometer value... the PriorValue reading shows up as nothing (NULL) when i then try to take [odometer]-[PriorValue] it shows up as nothing (NULL) while it should just treat it as [odometer]-0... i need help so that it shows an average mpg for every fill... Thanks for your help.
For a better example this is exactly what it looks like
Fuel Use ID is my primary Key and it is autoindexed... ive just been messing around with fake data and have changed the dates... thats why it looks a little strange
I obtained data for earnings by industry for men and women over time (5 years). I developed crosstab query that showed the average earnings for men and women for the 5 years.
Now I want within this query to calculate the differential between men and womens average earnings and calculate a percentage within this crosstab query. I tried to use Expressionbuilder with little luck.
SELECT Costs.Costs_Per_Capita, Costs.Period IIF (Period = 1, (Select AVG(Costs_Per_Capita) From Costs Where Period = 1), (Select AVG(Costs_Per_Capita) From Costs Where Period = 2) AS result FROM Costs;
But get "syntax error (missing operator) in a query expression ..."
I found the attached example a while back (can't find the site again though ) and it calculates a moving average. I've hacked out the parts I need for my own work and I can create my moving average query without an issue.
However, I need to extract the MA data into a table so planned on using append. I kept getting type errors so I tried make table to see what type it was creating and it appears to be Short Text rather than a number.
I've added an extra button and Make Table query to the example.
As far as I can tell from the code, the moving average value when calculated is a Single. However, when I write it to the table, its a Short Text.
How do I make the created Table use Number Type for my calculated moving average?
I am trying to construct a crosstab that averages a calculated field from a previous query. It is returning a "Data Type Mismatch" message.
The field I am trying to average is a subtraction of dates to find total days. I assume my field is not a number so I have tried to wrap it in CDbl() to change the type.
The formula is
Code: CASE_DAYS: CDbl(IIf([Actual Close Date]-[Creation Date]>=0,[Actual Close Date]-[Creation Date],""))
Hi there, I have a really simple question. I have a field in a table that contains comments (text format). I want to run a query where I can get only the records that contain the word "high" in the comments. There may be some typos so I would like records returned that also contain something like "high". High is not the only word in the comments.
ItemType is a list of items (Table, Desk, Bookshelf, Chair) Style is a type of style for the item (Circular table, Radial Desk)
I want to create the Items.Style field based upon a query of the StyleTypes. So if a user types "Desk" in Items.ItemType then the Items.Style field will be limited to only those Styles which match that selected item within the StyleTypes table.
Think thats clear enough, but my attempts have all failed, hope you guys can give me a push in the right direction, thank you!
I have very little knowledge of databases and even less about writing queries.
The problem: I have a database that lists cities (such as Paris, London, New York etc) as rows and employment status (Full-time, Part-time, Unemployed etc) as columns. I need to go through each city and record which employment status is greatest (for each city) and present it in a new column. I can do this manually but I’m sure a query can be created to automate the process.
Hope that all made sense. Can anyone suggest ideas on where to start?
hi have just designed a rather simple music database for my dad to use at home as he has large collection. have a table with the heading"artist" and another one with "album" ...my problem is that on the table there maybe 1 or more instances with the same artist but with different albums...when i run a simple query for ..say artists starting with the letter"a" i may get about 10 cases of the same artist appearing in my query when all i want is one...(depending on if i may put in say one artist like abba having 10 albums etc)...i am entering each artist with the album each time so i may enter the artist in say 10 times....depending on how many albums etc
if you understand all that...i would be grateful if someone could help me with a simple query alan
I have a query that returns the uncompleted jobs from a single table ( its a calls database) all i need is query that returns the amount of uncompleted jobs onto the switchboard I.E whatshername has X amount of uncompleted jobs , i have done a search and most of the solutions are very complicated for what i want to do .....anyone any ideas ?? pls
SQL and Access are both new to me. I have purchased some books (which are still in the mail), and have gone through this forum (which seems to be a little more advanced for me at this moment).
I have a database that contains Business_Unit (Store Numbers), Post_Date (Last Date of Inventory), and other misc. stuff. Anyways, My goal is to pull the latest date of inventory for each individual store before the last ninety days. What I have so far:
SELECT DISTINCT informix_shrink_head.business_unit, informix_shrink_head.counting_event_id, informix_shrink_head.post_date, informix_shrink_head.delta_cost, informix_shrink_head.delta_count, informix_shrink_head.count_reason_cd, pcw_loc_master.closed FROM informix_shrink_head, pcw_loc_master WHERE (((informix_shrink_head.post_date)<=Date()-90) AND ((pcw_loc_master.closed) Is Null)) ORDER BY informix_shrink_head.business_unit;
However, I get the same store multiple times and for multiple post dates. I do not even know if this can be done in one simple swoop, or if I need to create another table and re-query that? Any assistance or comments would be much appreciated.
I have a database that collects information on products when engineers return them, what I need to do is when they have submitted the form, email it to 3 people so that the paperwork can be raised.
I understand that I will need to create a query to display just the last record and then email it.
So the idea is when a record is created and the engineer clicks submit the last record then gets emailed to the three people.
Please be gental with me im still quite new to all this.
How do i create a query that just displays the last record?:confused:
I am sending ZIP file in attach with files in it. I would like some help to change the 'Q_test' query so that I can get the result shown in the excel file.
I want all the records to appear which have some text in that particular field. So if there is no text in that field, it won't come up. What would I need to write under that field in the query?
