I'm looking to return different average values for each GICS Sector using this query:
Code:
SELECT [GICS Sector], DAvg("GM","tbl0"," [GICS Sector] = [GICS Sector] AND GM Is Not Null") AS GmMedianByGicsSectorFROM tbl0
WHERE [GICS Sector] = [GICS Sector]
GROUP BY [GICS Sector];
Health Care 0.469556278179105
Industrials 0.469556278179105
Information Technology 0.469556278179105
Materials 0.469556278179105
Telecommunication Services 0.469556278179105
Utilities 0.469556278179105
My query includes a few new columns I have added and I need to use GROUP BY so each new column is grouped per store.
When I try to use: GROUP BY newColumn1, newColumn2, newColumn3;
I get the following error: 'You tried to execute a query that does not include the specified expression 'Store Nbr' as part of an aggregate function'
All I want to do is have each new columns distinct value show up once for each particular store, not repeated multiple times. When it is repeated multiple times, it also shows for each store department which messes up the metrics of the report.
I am trying to calculate annual percentiles of a large set of data and I have only been successful at retrieving the percentile of the entire data set (and not by the grouping). See provided example database for code/query. Query1 is what I want to happen to make the Percentiles table.
Is there a way to have an expression in the control source of a text box in a report, that re-starts or is exclusive for every group within the report?
Hi, Can anyone please help me out with the IIF concept of MS-ACCESS. I have a table with the following data: Name Age Place Dept AAA 13 Mumbai Accounting BBB 12 Pune Banking CCC 15 Delhi Finance DDD 30 Mumbai Accounting EEE 25 Delhi Finance
Now i need a query output like Expected output: --------------- Dept Total-count Mumbai Pune Delhi
Accounting 2 2 0 0 Banking 1 0 1 0 Finance 2 1 0 1 Now i m using the query: ******************* SELECT [Dept], sum(test-table.dept) AS ['total count'], Sum(IIf([place]="Mumbai",1,0)) AS Mumbai, Sum(IIf([place]="Pune",1,0)) AS Pune, Sum(IIf([place]="Delhi",1,0)) AS Delhi FROM [Test-Table] GROUP BY [Dept]; ******************* and the output which i am getting is: Dept Total-count Mumbai Pune Delhi
Accounting 2 0 0 Banking 0 1 0 Finance 1 0 1 can anyone let me know as to what change should be there in the query so that i get the correct values in Total-count field with the sum of horizontal counts?
The user will input the JobId, PositionId, ActivityId. I want to return all the TaskName ,CompetencyId, CompetencyName Where the following: The highest priority within the same keyword Basically group by keyword then pick the highest priority I have done this:
SELECT Max(Competency.Priority), Competency. Keyword FROM Competency INNER JOIN ((ActivityTask RIGHT JOIN (positionTask RIGHT JOIN (JobTask RIGHT JOIN task ON JobTask.TaskID = task.TaskID) ON positionTask.TaskID = task.TaskID) ON ActivityTask.TaskID = task.TaskID) INNER JOIN TaskCompetency ON task.TaskID = TaskCompetency.TaskID) ON Competency.CompetencyID = TaskCompetency.CompetencyID WHERE (((JobTask.JobID)=[job])) OR (((positionTask.PositionID)=[position])) OR (((ActivityTask.GroupID)=[Activity])) GROUP BY Competency.CompetencyKeyword;
It works fine, it returns the all the keyword, and its highest priority. But I can’t return the Competency.CompetencyId, Competency. CompetencyName with the aggregate function Max
Hello! .... I think I posted this in the wrong section of the forum, so here it is again and hope someone's kind enough to help. Basically, I've been asked to show (in a form as well as a report) results on a Monthly, Quarterly and Year-to-Date basis. The table structure I currently have is as follows (additional fields and/or tables can be added if it would help get the results desired):
Part of the trick with this is to be able to show these aggregate values (quarterly, YTD, and on a 13-month rolling basis) as users enter new data for whatever the current month is. Any assistance in helping me get this accomplished would be enourmeously appreciated.
I was wondering if anyone can help me: I have for example Pupils that are being entered into a table (called log). A query then counts the number of entries for each pupil I then want it ONLY to show the pupils that have been entered in 10 times on todays date (using DATE()). I know this doesnt work but this is what I want to do:
SELECT log.pupilNo, log.Forename, log.Surname, Count(log.pupilNo) AS Demerits FROM log, Tally WHERE (log.Date)=Date() AND COUNT(log.pupilNo)>9 GROUP BY log.pupilNo, log.Forename, log.Surname, log.pupilNo;
Apparenty you cannot have an Aggregate function in a where clause. Does anyone know a solution, any help would be appreciated Cheers Bikeboardsurf
I have a table namely, leave_details wherein there is a column called DAYS and TDAYS, DAYS columns represent number of leaves an employee has taken in a single leave application and TDAYS represents Total number of leaves an employee has availled till date.
To calculate the TDAYS i need to SUM the DAYS column for every employee.
When an employee again log in the application he should be aware of the total number of leaves which he has already availed.
Please help me for the same as I am unable to do so.
Kindly revert back for any queries or any further information.
I am working on a marketing database. I have two tables that are in use called tblListInfo and tblResults.
In tblListInfo the fields I am using are DropDate, ListName and Marketcode. In tblResults the fields I am using are JoinDate and Marketcode.
I am attempting to write a query that the end result will display the number of records within the 1st week of the promotions drop date. For every combination where the ListName value AND the DropDate value are unique, I want to count the number of records in tblResults that have JoinDate BETWEEN DropDate AND DropDate + 6.
I tried writing the code for this in pieces starting with:
SELECT DISTINCT tblListInfo.[ListName], tblListInfo.[DropDate] FROM tblResults INNER JOIN tblListInfo ON tblResults.[Marketcode] = tblListInfo.[Marketcode]
This gave me my first ideal part of the output I was seeking. I listing of all the possible marketing lists used with duplicate names occuring only when there were different drop dates. for example:
Listname Drop Date List - 1 1/1/08 List - 1 1/15/08 List - 2 1/1/08 List - 2 2/5/08
From there I wanted to modify the process to include a third field that included the # of records in tblResults that have the JoinDate field BETWEEN tblListInfo.[Drop Date] AND (tblListInfo.[Drop Date]+6). The ideal output would be like this:
Listname Drop Date Week 1 List - 1 1/1/08 15 List - 1 1/15/08 8 List - 2 1/1/08 32 List - 2 2/5/08 12
My attempt at getting to this result is below:
SELECT DISTINCT tblListInfo.[drop date], tblListInfo.[ListName], count(tblResults.[join date]) AS [Week 1] FROM tblResults INNER JOIN tblListInfo ON tblResults.[MarketCode] = tblListInfo.[MarketCode] WHERE tblResults.[Join Date] BETWEEN tblListInfo.[drop date] AND (tblListInfo.[drop date]+6)
I receive an error at this point saying: "You tried to execute a query that does not include the specified expression 'ListName' as part of an aggregate function."
Can anyone offer suggestions/corrections to my approach and logic?
I have a subform (in continuous record view) which displays products on an order items form. Each record has a DMin control to find the lowest price for its product (each product can have many suppliers and therefore many prices). I need to put a control in the subform footer containing the sum of the minimum prices.
Access doesn't seem to want to sum calculated controls, so could someone please point me in the direction of how to go about doing this or achieving the same result through a different method?
Manifest Weight StopNo State ------- ------- ------- ------ 71545 1000 10 VA 71545 1000 20 TN 71545 500 30 AL
Some Manifests are repeating There are multiple stops within each manifest (eg.: 10, 20, 30) and each stops has itsown weight info and State. What I am trying to figure out is the number of manifests, total weight within manifest, and the last stop and state of the manifest
Query: SELECT ManifestNo, Count(ManifestNo), Sum(Weight), Last(Stop No), Last(State) FROM RBP WHERE ShipmentType="ROAD" GROUP BY ManifestNo;
My Problem: Everything works fine, but the state of the last stop is not displaying properly..
My result is - 71545 3 2500 30 TN Correct output should be - 71545 3 2500 30 AL
I tried to remove the "last" from State but its gives me the aggregate function error. Any suggession ? Thanks in advance.. binjos
Is it possible to add a calcualted field to a query that already uses 'sum'? I want to take the value returned by sum and add the value of another field to it.
Is there any way to lookup items using DLookup or other functions that will return an array of values not just the first value found. Example: I have three employees in a complaint query. I am using Dlookup to identify them on my employee form. When I get to the same employee/employee id on my employees form, I have a label that flashes red to flag me that this employee appears on that complaint query. The problem that I am having is that it only flashes for the first employee found in the query, and the label only flashes for that one person. I would like the label to flash for the other two employees because the function should see that they are in the query results as well. Help please?!!!
I have quite a few aggregate functions within a report I produced based on 2 queries. I need to do a mail merge with this information, but it is not all in one single query, because I cannot get an expression using totals in a field. How do you get these aggregate functions to be a part of the query, so it can eventually be part of the mail merge? It is unfortunate that you can't just use a report to mail merge, because all my equations are in the report.
I know SQL well enough, but I cant seem to get my query to work. Basically, I am trying to display a series of posts, with the number of comments on the side.
I have two tables, Entries and Comments, which look like this: Entries --------- IDTitleContent (memo field) 1CatsSomething about cats here 2DogsSomething else about dogs here 3RodentsMy pet rat runs in a wheel etc.
Comments -------- IDPostIDComments 11My cat's name is mittens 21I taught my cat how to throw a curve ball 33Rats like squeak toys 42Nobody likes dogs 53Bunnies make good pets 61Cats are witches in disguise Comments.PostID corresponds to Entries.ID on a many-to-one relationship.
I want to run a query that will join the number of comments (which is a Count of each row in the Comments table where Comments.PostID = Entries.ID) to the existing table, so that it will look like this:
Entries (with #ofcomments joined) ---------- IDTitleContent (memo field)#ofcomments 1CatsSomething about cats here3 2DogsSomething else about dogs here1 3RodentsMy pet rat runs in a wheel2 etc.
Usually, this would be a really simple SQL statement, but as I am using a Memo field, I am getting a "cannot use Memo field in aggregate function" error.
I'm not sure what to do, any help would be appreciated :)
I have an aggregate query that creates a report of "A" by "B". Each "A" can have many "B"s under it. The report works fine. I need to count and display on the report the number of "B"s for each "A". I can't seem to manipulate the "count" property of the query to generate the correct number. Little help??? Thanks....
I am working on a Sales Analysis db, which pulls from:
tSales - Sales Figures (40k records) tBuildUnit - Bom Build Units (8k records - one per product) tBom - Bills of Material - All Components (71k records) tUom - Units of Measure for Products (80k records - 10 per product)
It is quite basic in theory, it looks at a product sold, goes to tBom to get the cost of the raw materials.
I have a query that sums the components by their group, distinguishing between material and labour cost by the component group (cGrp) which is 'WORK' for labour and <>"WORK for materials. I can then sum the cost of the materials for a given product.
Anyway to cut to the issue in hand.
I export from our system (LM4 on btrieve on novell 3.2) into CSVs then manipulate those
we have 220 customers in this recordset. so i would like just 220 rows.
My data that i export shows every transaction (so i have 40k records)
So i used a aggregate query, grouping by customer account and summing the value, material, and labour fields, however this gives me each customer three times, one per year e.g.:
SELECT tblTempProject.project_number, tblTempProject.Project_name, tblTempProject.subsidiary, tblTempProject.division, tblTempProject.unit_number, tblTempProject.client_name, tblTempProject.resourcing_contact, tblTempProject.project_cat_type_code, tblTempJob.headline, tblTempJob.contact_name, tblTempJob.contact_staff_number, tblTempJob.contact_unit_number, tblTempJob.resourcing_team_email, tblTempJob.creation_date, tblTempJob.nominatio_date, tblTempJob.decision_date, tblTempJob.sar_status, tblTempJob.publication_level, tblTempJob.sar_number, tblTempJob.last_modified_timestamp, tblTempJob.last_updated_by, tblTempJob.requirements, tblTempJob.positions_filled, tblTempJob.closure_description, tblTempJob.other_inf, tblTempJob.description, tblTempJob.qual_exp, tblTempJob.Tag1, tblTempJob.Tag2, tblTempJob.Tag3, tblTempJob.Tag4 FROM tblTempJob, tblTempProject WHERE tblTempJob.project_number = tblTempProject.project_number AND tblTempJob.project_category_type_code = tblTempProject.project_cat_type_code AND tblTempJob.project_company_code = tblTempProject.subsidiary AND tblTempProject.subsidiary='NL' AND (tblTempJob.sar_status='FIR' Or tblTempJob.sar_status='HOL' Or tblTempJob.sar_status='PRO25' Or tblTempJob.sar_status='PRO50' Or tblTempJob.sar_status='PRO75') AND tblTempJob.project_company_code=[tblTempProject].[subsidiary] AND tblTempJob.project_category_type_code=[tblTempProject].[project_cat_type_code] AND tblTempJob.project_number=[tblTempProject].[project_number] AND tblTempJob.external_resourcing=-1 GROUP BY tblTempProject.project_number ORDER BY tblTempJob.sar_status;
When execute this query then I get the following error: "You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)"
When delete the line: GROUP BY tblTempProject.project_number, then it works. I don't know what I must do to get this right so that I have every project one time in the result.
I am trying to run a query with a field called "Age". The field Age is supposed to determine the age of the record based on what's in the remaining amount field and the cash date, the disp date, or the run date of the download (typically the last day of the month). So my expression for the Age column is as follows:
AGE: IIf(Min([REMAINING AMT])=0,[CASH DATE]-[DISP DATE 1],[CASH DATE]-#1/31/2005#),
which is saying of the remaining amount = 0, then calculate the age based on the difference between the cash date and the disp date, otherwise, calculate the age based on the cash date and the run date of the download, right?
Well, when I run the query, I get the following error message:
You tried to execute a query that does not include the specified expression 'IIf(Min[REMAINING AMT])=0,[tablename].[CASH DATE]-[DISP DATE 1],[CASH DATE]-#1/31/2005#)' as part of an aggregate function.
What does that mean and how can I get this to work? My "Total" line is set to "Expression". I even tried it with "Group By" and it gave me another error message. Please help me!
