Finding The Lowest Average
Oct 24, 2006
Let's say I have a table with the following data:
Customer:
----------
name | city | salary
john| Boston | 14000
billy | Boston | 32000
sam | Boston | 12000
jj | Dallas | 6000
greg | Dallas | 8000
josh | LA | 1200
ally | LA | 600000
bill | LA | 12000
bob | LA | 9800
how can i list the city with the lowest average salary?
I need to find the average salary of each city(should be 3), then choose the lowest from those 3 averages.
Can anyone help me?
View Replies
ADVERTISEMENT
Aug 8, 2007
I have a customer concerns database that contains the dates for when the concerns were reported and tyhe dates for when the concerns were resolved. I am trying to make a query that finds the average of how long it takes for the concerns to be resolved. How can I do this?
View 1 Replies
View Related
Feb 18, 2013
I work in the Insurance Industry and I am having a hard time trying to find the formula for finding the answer to the below issue I am having.
In a table I have 4 columns:
1st: Total # of Claims (Claim Count)
2nd: Total # of Items (Item Count)
3rd: Item % of Claim Count
4th: Average # of Items per Claim (This is the number which I am trying to get)
Is there a formula which could give me the "Average # of Items per Claim"?
View 3 Replies
View Related
May 24, 2006
Hello there...
I have a data set as follows:
Product CompanyA CompanyB CompanyC .... CompanyZ
aaa 2.3 2.4 2.5
bbb 3.4 - 3.1
and so on...
The value represents the price of products...
I want to make a query that will show me results in following heads..
Product MinimumPrice CompanyName
aaa 2.3 CompanyA
bbb 3.1 CompanyC
How can I proceed??
Looking for some help
Regards,
View 1 Replies
View Related
Jan 16, 2008
hi,
I have a table that contains itemcodes and prices. An example of the records would be:
<Item Code> <Price>
Product001 £34.56
Product001 £49.23
Product001 £23.22
Product001 £98.43
Product002 £12.45
Product002 £54.34
Product002 £25.51
Product002 £76.84
Product002 £14.97
I would like to run a query that returns the lowest value price for each Item code. So in this example it would return:
<Item Code> <Price>
Product001 £23.22
Product002 £12.45
Does anyone know the criteria code in the query design view for this problem?
I know it sounds silly, but there are unique codes in another field, i just need to query against these fields.
Thank you for your help.
Dean
View 3 Replies
View Related
Feb 2, 2008
I'm using a filter on the form load of one of my forms that potentially would have 3000 records. The idea is to speed the process up a little.
I'm using:
If IsNull(Forms!frmProjectMain!ProjectID) = False Then
DoCmd.GoToRecord , , acFirst
end if
Me.FilterOn = True
Which works fine. However, I'd rather it was the ID with the lowest numeric value that was displayed and filtered rather than the last record chosen. i.e. 1 instead of a random number.
Does anyone know how i'd go about writing code that would tell access to go to the lowest ID to filter?
thanks for any help I receive here
View 5 Replies
View Related
Nov 3, 2006
Hello,
I have searched through the forum, but cannot find a suitable answer to my question, so here I am.
I wonder if you could help me?
I have a list of Customers each of which has a Customer ID then I have a list of accounts which may have more than one customer ID.
For this senerio I would like to only show the record that has the lowest customer ID.
I.E. If one account has two customers take the lowest customer ID.
Does anyone know how to do this in SQL or VBA?
Thanks for all your help.
View 2 Replies
View Related
Feb 23, 2006
Hi all - I have a table that contains Client ID, Supplier Name, Order Number, Order Date, and Price.
Example -
Client ID -- Supplier Name -- Order Number -- Order Date -- Price
1111111 Supp A 222222 1/2/06 1,000
1111111 Supp B 222222 1/2/06 2,000
1111111 Supp C 222222 1/2/06 4,000
1111111 Supp D 222222 1/2/06 500
1111111 Supp E 222222 1/2/06 1,200
I need to determine the 2 lowest prices per order number. In this case that would be Supp D - 500, and Supp A - 1000.
I am using the Top function and sorting the price in ascending order to get the lowest two prices. The problem is that it selecting the lowest 2 prices of all the orders on the table, not each individual order. I tried grouping on the other fields, but still came up with incorrect results.
Any ideas? Thanks!
B
View 3 Replies
View Related
Mar 13, 2008
I have a table that has a list of order information, there are multipule records per order that have information about when the order was processed. There is a day tied to the process time of each record, I need to filter out the duplicate records and get one record for an order, but that one record must be the first process time entry for that order. Anyone know how to make this query? Let me know if this doesnt make sense.
View 4 Replies
View Related
Mar 17, 2014
I have a relatively complex update that I need to perform on a table from a form. I have a system that has "games" and "game copies". The game is simply a name of a particular game and the copy is something that has a stock number but a foreign key of the game catalogue number. This means I have several catalogue numbers that are the same in the GameCopy table.
The problem is that I have to reserve a game - not a game copy. When I have made a game available (it has been returned) I have to indicate this in the reservation table. I could have many different reservations for the same game so I need to only update the oldest reservation and indicate that a game copy is available now.
Summary:
One "Game"
Several "GameCopy" using "Game" as a foreign key
Reservation table with possibly several reservations for the same Game - not GameCopy(s)
In other words I have an "Available" field in the reservation table and a "date reserved". I need to create an "Available" (Date()) entry for the oldest DateReserved entry on that reservation table. I could have done it as a boolean but I decided to use a date instead for logging purposes.
Would I use some kind of "Once only" action to make sure that only one of the reservation entries are updated? I really do not know how to proceed with this.
Obviously if I simply:
UPDATE Reservation SET Reservation.Available = Date()
WHERE Reservation.CatalogueNo=Forms![Current Reservation].CatalogueNo;
...then it will update all of them. I believe there must be a bit more SQL I have to add or something else maybe.
View 5 Replies
View Related
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
Sep 4, 2006
I must admit I am a "newby" to Access but I have bought my Access 2000 bible, as I am running Access 2000, and attempted to create my desired database. I have linked to 2 tables that I download on a daily basis. This is my inventory from two different suppliers. I then have created two queries that filters each of these files to only show positive quantity items, filters out item specifics, etc.What I want to do is join the 2 queries with their data already filtered. Both of the files from 2 different suppliers contain some of the same data. Once joined if there is an item that is the same I want to delete the higher cost item and only show the lower cost item, I also want to show the rest of the items that do not match. It is easy to identify the same items as every item contains a 12 digit identifier called a upc.I can create a UNION ALL query that shows every item from the two queries. However, I am unsure as to how I go about deleting the higher cost item and only showing the lower cost item if the item is the same. On a side note after I get the results desired I will then be createing an append to query and append all this information to another file that is then uploaded to another system.I will try to attach some sample data that I am using from the two queries I want to join, this data is only a sample of a much bigger file.Thank you and any and all comments or suggestions is much appreciated.
View 3 Replies
View Related
Sep 9, 2014
I have attached a sample of a database.
Table 1 has all the items I am trying to sell with sell by date after which I cannot sell this item. Then in Table 2 I have forecasted sales. So now I am trying to calculate stock consumption to see if I will be left with any stock that I cannot sell.
So now somehow I need to deduct sales forecast from my stock holding but it needs to go by date i.e. consume all stock for Item 1 with date 16/09 before moving to Item 1 with sale by date 23/09.
So based on the attached example, I can see that on 16/09 I will consume only 5 cases from sell by date 16/09 and another sale is 18/09. So that would give me information that I will be left with 95 items dated 16/09, which I cannot sell because they will be out of date.
Ideally I would like also to include the logic that if Item is out of date it would move to the next sell by date.
So in this case sale of Item 1 forecasted for 18/09 (94) would consume the whole stock (50) with date 23/09 and another 44 from date 01/10
For Item 2 I can see that units with Sell by date 30/09 will be consumed on 25/09 and I will start taking stock from next sell by date which is 14/10.
View 8 Replies
View Related
Dec 15, 2005
I have a access table with 32 columns and 42,000 rows of numbers. I need to find the MIN number in the row and if the MIN number has duplicates then I need them all placed into another column by column name.
Example:
Starting file
DEST,ORIGIN1,ORIGIN2,ORIGIN3,ORIGIN4, ETC
05512,3,2,4,2
ENDING table needed
DEST,ORIGIN1,ORIGIN2,ORIGIN3,ORIGIN4, NEWCOLUMNname
05512,3,2,4,2,2 ORIGIN2 ORIGIN4
Where the new column name contains the MIN number in the row and all of the associated duplicates column names.
View 1 Replies
View Related
Jan 11, 2008
I have a query that finds an average. How can I get the average to only show two numbers after the decimal?
View 4 Replies
View Related
Dec 10, 2005
I apologise for my ignorance, but I’m very new to Access.
I have a database of dates, that I need to analyse.
I have created a Form called "DateRange" with 2 date fields;
Text1 = Date From
Text2 = Date to
Command1 = Preview Report
My Query has 2 fields;
Slotdate = all the dates (show as 20051210)
Actdur = Actual Duration (show as numbers 1 or 12 or -3 etc)
The SQL View is;
SELECT slotapp.slotdate, slotapp.actdur
FROM slotapp
WHERE (((slotapp.slotdate) Between [Forms]![DateRange]![Text1] And [Forms]![DateRange]![Text2]));
I just want to calculate an average of Actual Duration
So that my report displays the average duration between the date ranges.
Any assistance in this matter would be greatly appreciated
View 1 Replies
View Related
Oct 2, 2007
I've looked thru a lot of posts, but can't seem to find the solution. It seems like this should be something I could figure out, but so far have not.
I have a table that is showing a production number for each day. What I'm trying to show is the best 5 day average production over a period of time.
Thanks,
Tom
View 4 Replies
View Related
Feb 18, 2005
Could someone please tell me how to work out the age of someone using a query or report and the average age of everyone?? I also need to know how to put on a report the total number of people satisfying the search criteria. It also says i must obtain a single record for each person and to do this i need to change a query property to allow only unique records to be displayed? do u know what this property is??
Please help!!
Thank You
View 6 Replies
View Related
Sep 15, 2005
Is there any way i can calculate a rolling average for a field in a record, based on the 10 previous records?
Cheers,
Ben
View 3 Replies
View Related
May 3, 2006
Hi,
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
......
Thx,
1.8T
View 5 Replies
View Related
May 21, 2007
i have a date field with time and date each record is entered. results look like this
Date_Complete
4/9/2007 8:26:11 AM
4/9/2007 8:31:25 AM
4/9/2007 8:34:14 AM
4/9/2007 8:34:21 AM
4/9/2007 8:34:29 AM
4/9/2007 8:34:36 AM
4/9/2007 8:34:49 AM
4/9/2007 8:41:27 AM
4/9/2007 8:41:49 AM
4/9/2007 8:42:32 AM
4/9/2007 8:42:39 AM
4/9/2007 8:42:49 AM
4/9/2007 8:43:36 AM
4/9/2007 8:44:21 AM
4/9/2007 8:45:48 AM
I want a query or report to give me the average entry time per record. Something like: Average time between orders 1:25 (one minute twenty five seconds)
View 2 Replies
View Related
Sep 26, 2007
I want to create a running or moving average of the most recent 5.
can anyone help here?
see attached file
Mix IDTest Date 7 Day1 Avg of 5Ave 28 Day
SF227
2/1/2007 3870
2/1/2007 2160 5415
2/7/2007 3580 5505
2/7/2007 3510 4955
2/12/2007 2990 32204965
2/19/2007 2800 30085500
2/19/2007 3330 32424920
View 2 Replies
View Related
Jan 2, 2008
I need to make a query which counts the number of days between "Date of Complaint" and "Effective Date". This is what I have so far:
[SELECT [Customer Complaint Log].[Complaint Number], DateDiff("d",[Date of Complaint],[Effective Date]) AS Expr1
FROM [Customer Complaint Log]
WHERE ((([Customer Complaint Log].[Effective Date]) Is Not Null) AND (([Customer Complaint Log].[Date of Complaint]) Is Not Null));
I need to make it so that it uses todays date for "Effective Date" if there is no "Effective Date". Any help would be appreciated. Thanks.
View 3 Replies
View Related
Jan 15, 2008
I am running a query that returns the minimum, maximum, and average mileage of a list of cars. I have set decimal places to zero in all places I can think of, but of course the average returns with a long decimal output. These figures are then displayed on a report.
How can I truncate the 'average' display so it rounds to show no decimals?
I have tried using Round([YourNumberFieldName],3) in the query but that doesn't seem to work.
The numbers are stored in a table as double numbers. Again the decimal places are set to 0.
Thanks.
View 2 Replies
View Related
Jan 29, 2008
I have a file with hundreds of home builders. It has three fields for this problem.
Table = Permit
field = Per_date (Date Field) Date BLD record appears in table.
field = BLD (Char) Builder number or Name
field = SQFTArea (N) Size of home.
1. I need to be able to get the average for (SQFTArea) for all records greater than 950 (SQFTArea).
2. For year 2007.
3. Grouped by Builder (BLD).
Example: Per_date - BLD - SQFTArea
Record: 2006 - 012 - 0500 Not becouse of 2006 and 0500
Record: 2007 - 012 - 2500
Record: 2007 - 012 - 3500
Record: 2007 - 058 - 2000 Not becose of BLD=058
Answer is for 2007 BLD 012 has an average of 3000
BLD 058 would be figured with it's self at average=2000 if the only record with this number or used with any other records that are BLD 058.
I have asked for help in the past but must likely my examples not that great. Here’s hopping. Hopping?? I hope I got that right being as I'm not a rabbit. Spell checker no help on this one.
Bob
View 2 Replies
View Related
Mar 9, 2008
First of all I consider myself to have Intermediate knowledge of Access. I am comfortable building tables, queries, reports, macros, etc. but get a little lost when needing to manually code something in a query.
I need to create a database to document quality reviews of certain reports the plant creates. Typically each report gets reviewed by 2 to 6 people and each section is scored. So lets say the database table has the following fields
Report_No
Reviewer_Name
Review_Date
Section1_Score
Section2_Score
Section3_Score
Total_Score
I need a query that will average each of the Section Scores and Total Score so I can build a monthly report showing the report and the average grade for each section and the average total grade.
Any suggestions on how to do this is appreciated.
Thanks,
Jim
View 1 Replies
View Related
