I am using Access 97 and I am getting very stuck when I am making a group by query.
Basically I have a table which has for engineering jobs (and a few other collums):
[id], [Site Code], [Ref Number], [Job Size], [Date Origionated] and [Date Completed]
I then have made a query (called [MG - CWD SLA]) which works out for all jobs between 2 dates, how long they have been running for and if they have met the Service Level Agreeement:
SELECT Sheet1.[SITE CODE], Sheet1.[Ref Number], Sheet1.[Job Size], Sheet1.[DATE ORIGINATED], Sheet1.[JOB COMPLETE], (CalcWorkdays([DATE ORIGINATED],[JOB COMPLETE])) AS Expr1, IIf(IsNull(Sheet1.[JOB COMPLETE]),"Not finished",IIf([Job Size] Like "M*",IIf(Expr1<=10,"Y","N"),IIf([Job Size] Like "S*",IIf(Expr1<=5,"Y","N"),iif([Ref Number] LIKE "ID*",iif(Expr1 <= 1,"Y","N"),"N/A")))) AS Expr2
FROM Sheet1
WHERE (((Sheet1.[Help Desk Type])="CWD") AND ((Sheet1.[DATE ORIGINATED]) Between [Type the beginning date:] And [Type the ending date:]))
ORDER BY [DATE ORIGINATED];
I then have made a summary query based on the above query which gives me for every site, between 2 dates; the number of jobs who have met the SLA or not as the case may be.
SELECT [MG - CWD SLA].[SITE CODE], [MG - CWD SLA].Expr2, count(*)
FROM [MG - CWD SLA]
GROUP BY [MG - CWD SLA].[SITE CODE], [MG - CWD SLA].Expr2;
What I would like to do is for every like outputted by this query, give the number of jobs for this site between those 2 dates, so then for every line of the query give the percent of jobs which met the sla/not met the sla or not finished.
I have been messing around with distinct and I cannot get this to work! Also been trying to do a join or a subquery, but not sure how to integrate this with working with a query based on a query.
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 everyone. I'm having some issues creating a query i need for work. I'm able to do this on the report level with a group and footer to get this total. however due to further complexity and Queries that will call upon this one, it needs to be in the first level query i create.
What i have is a database. There are Employees, Dates Paid, timesheet dates, Pay TYPE and hours. here is an Example of a few lines of data. this is how the database is created. it creates a new record for every different pay type paid that week.
I am working on a project for work. I need to display in a report the age groups and how many injuries they have had in each age group. I have a table tblAccidents with a field "Date of Birth".
I would like to display in the report how many accidents were within each age group such as;
Age 10-20 23 injuries Age 20-30 10 injuries Age 30-40 50 injuries Age 40-50 8 injuries Age 50-60 1 injuries
I have been able to get the age from the "Date of Birth" field but how do I split them to the correct age groups?
i have a simple Table something like this from a test station: -------------------------------- Serial_Number | Status | -------------------------------- A_____________Pass B_____________Fail C_____________Pass A_____________Fail B_____________Fail A_____________Pass
and i need to get a query that separate 2 colums from the Status and count the records for each Serial_Number, some thing like this,
I'm having problems with a query. I've got a table with the following fields in my Employee table: EmployeeID, FirstName, LastName, SupervisorID
I'm trying to create a query that will pull the following information about all the employees: FirstName, LastName, {Supervisor LastName}, {Supervisor FirstName}
The supervisor first and last name comes from finding the correct EmployeeId that matches the SupervisorID in the employee table.
Hello I have a text box in my database that i would like to be about to search and group the text field if is had similar. for Example if I had a record like" called in puking" and "Up all night puking", I'd like the query to recognize "puking" (without me specifically telling it to look for "puking") and group them.
I have an access database that links to some SQL Server tables. These tables hold time tickets which keep track of the time that an employee is logged into a job and records the associated product code and work code. All jobs have a work code, and some have a product code. The product coce is the most accurate way to track time, so if a product code is present I need to use that. If it's not present I use the work code.
I currently have a query called 'main' that gets all records in a specified date range and creates a 'code' column. This is a hybrid of the product code and work code using the method described above.
IIf([ProdCode],[ProdCode],[WorkCode])
Everything looks fine there, but when I go to a new query to get total hours by code ('TotalHoursPerCode') by grouping code and getting the sum of ManHour, it doesn't work.
Instead, it lumps all lines that have a product code together but then combines all lines that have no product code into a single line at the top. Instead of using the 'code' column to group.
Here's the SQL from the 'TotalHoursPerCode' query.
SELECT Main.Code, Sum(Main.ManHrs) AS SumOfManHrs FROM Main GROUP BY Main.Code;
I've gotten the medianf fuction that is pasted on these boards working in the attached db example. The problem I am running into now is the medianf function (correctly) takes the median for an entire table at once. My dataset example looks like:
typenum t11 t12 t13 t16 t16 t1100 t21 t22 t2200
I'm trying to end up with median t1 = 4.5, t2 = 2. With the way that function works, it sets them both to 3 which is the median of the entire set. I have the query saved in here already setup so far. Any ideas on getting the grouping to work properly?
I have been struggling with this for a while now and cant seem to get this update to work.
I have a table that holds a project id, a line ID and then the percent allocation to each service line. I am trying to calculate the percent allocation based on the number of repeat project id's IE: 1 project id appears in the table you get 1 for the percent allocation, 2 you get .5, 3 you get .3333333 and so on.
Here is my current query:
UPDATE rlnAPOppServLine AS A, (SELECT rlnAPOppServLine.SAPOMID, 1/Count(rlnAPOppServLine.SAPOMID) AS AutoPercent FROM rlnAPOppServLine GROUP BY rlnAPOppServLine.SAPOMID) AS B SET A.PercentAlloc = [B].[AutoPercent] WHERE (((A.SAPOMID)=[B].[SAPOMID]));
I have a table with persons age and location and I want to generate a report of all persons by location and age group. The age groups are 0-9, 10 - 19, 20 - 29..., 90-99. The location codes are 10,20,30,...90,99.
Rank # Name Div Sex Score 1 8 Bird Twitty 52Kg Female 684.69 2 12 du Toit Carin 52Kg Female 608.77 3 13 Colbert Jackie 52Kg Female 607.62 4 11 Wade Jenna 52Kg Female 595.41 1 7 DeDee Jenna 67.5Kg Female 702.27 2 10 Diva Meisie 67.5Kg Female 550.46 1 5 Man Super 70Kg Male 655.09 1 9 Doe John 82.5Kg Male 480.3 1 3 Man Spider 90Kg Male 537.63 1 6 America Mr 100Kg Male 406.42 1 4 Hulk Incred 110Kg Male 645.8
SELECT (SELECT COUNT(*) + 1 FROM History H INNER JOIN Lifter L ON L.uidLifter = H.uidLifterRef WHERE L.uidClassRef = Lifter.uidClassRef AND H.ScoreTotal > History.ScoreTotal) AS Rank, Lifter.LifterNumber AS LifterNumber, Lifter.Lastname AS Lastname, Lifter.Firstname AS Firstname, Class.Description AS CDescription, Activity.Description AS GDescription, History.ScoreTotal AS ScoreTotal FROM Class, Lifter, Team, History, Meet, Activity WHERE (Meet.MeetDate = DATE ()) AND Team.TeamStatus = 0 AND History.ScoreTotal > 0 AND Class.uidClass = Lifter.uidClassRef AND Lifter.uidTeamRef = Team.uidTeam AND Lifter.uidLifter = History.uidLifterRef AND Team.uidMeetRef = Meet.uidMeet AND Lifter.Gender = Activity.Code GROUP BY uidClassRef, Lifter.LifterNumber, Lifter.Lastname, Lifter.Firstname, Class.Description, History.WeightScoreC, Lifter.Gender, Activity.Description, Activity.Activity, Lifter.LifterStatus, History.ScoreTotal HAVING (Activity.Activity = 'GenderStatus') AND (Lifter.LifterStatus = 0) ORDER BY VAL(Class.Description) ASC, Lifter.Gender DESC, History.ScoreTotal DESC
I would like to see the query only returns the top three of each group to help me determine the GOLD,SILVER and BRONCE position in each division. I know that I need a select in the WHERE part but how?
I want to do a Group by Name, Date, Max(Price), Location. The results of the Query I want would look like this: Smith03/06/0514.00Dallas Jones03/04/0514.00Ft. Worth
What keeps happening to me is: I can do a query that will Group by Name and Max(Price) and I get: Smith 14.00 Jones 14.00 But if I add any other field – say – Date then I get an output that looks like this: Smith03/03/0512.00 Smith03/04/0513.00 Smith03/05/0512.00 Smith03/06/0514.00 Jones03/03/0512.00 Jones03/04/0514.00 Jones03/05/0512.00 Jones03/06/0512.00 …meaning everything. I’m at the point I want to scream! I cannot not believe I can’t figure this out. Can anyone help?
I have a query I need to do but I'm not sure how. I have a DB that has Latitude, Longitude, Signal. What I need is a query to Group By Latitude and Longitude and outputs the Max Signal level for that Lat/Lon group and all records within 4 of Max Signal. I can do the group by lat/lon and max(sig) but I'm having trouble getting the records within 4 (or -4 of the max). I know I can run this in two queries well enough but I'd like to do it in one. If you need the DB please let me know.
I received an error: "You tried to execute a query that does not include the specified expression 'Field1' as part of an aggregate function". This is the query I wrote.
SELECT Max(Table1.field3) AS Maxof, Table1.Field1, Table1.Field2, Table1.Field4, Table1.Field5, Table1.Field6 FROM Table1 GROUP BY Table1.Field5, Table1.Field6;
I saved query as q1 then created another query:
SELECT Table1.* FROM Table1 INNER JOIN q1 ON Table1.field5 =q1.field5 AND Table1.field6 = q1.field6 AND Table1.field3 = q1.maxOf
Any suggestions? Query q1 is the problem but I do not understand what the problem is. TIA Marty
I have a query which I want to based on a group selected on a form using a selector button. EG if a person is a member of the accounts team that button would be selected. I could then just run the query to get members of the accounts team.
I am using the following SQL, but find when I run it "every" record is selected....
I need to run a query that returns the number of bookings made for each week so far this year. However the date is held in DD/MM/YY format. I had a similar query that worked fine for monthly grouping using Month([Start Date])AS FilterMonth but tried replacing month with week, this didn't work.
Hi Folks, I have a table that has Student_ID, Course, and Grade. So each student has multiple records, one for each course they took. But what I want to return is only those people who have All F's. If I just put "F" as a criteria on the grade field then it will pump out those records that have an F, including those who maybe only have 1 F, but all I want is a list of Student_ID's of all of those people who have nothing but F's. Any ideas? HELP..i'm really in a bind with this one.
My table looks like this: Sales(a)||Date(a)||Sales(b)||Date(b)||Sales(c)||Da te(c) 1) 50 05/06 75 06/15 100 08/15 So I want to show all sales with the criteria - Date >05/01 <07/01. The final query totals will be Sales||Date 50 05/06 7506/15 So the issue is that there are 38 rows with similar data in each column I have the table in this format because I also have a form that feeds off this table and it needs to be in this format-but I would also like to run a query/report from this table that summaries sales. I also know that since there is more than 1 date value in the row, this is going to make the query difficult to run (each date is linked to a certain sales total). I guess what I want to do is transfer the horizontal data to vertical data (with certain criteria) Please help!!!
I hope someone can help, i'v searched but i still couldnt figure out how to do this. =(
The screenshot i've attached is what i have so far. What i want to do is add a column within that query that counts the number of times the ComplaintShort is the same within the Query and displays it. I'd also like to make the ComplaintShort Field Distinct so it would only show it once and show the number of times it has really appeared as a duplicate entry.
Heres the Query I currently Have to work with.
SELECT Cities.P_Names, Complaints.precinct_id, Complaints.ComplaintShort FROM Complaints INNER JOIN Cities ON Complaints.prec_id=Cities.P_prec WHERE Cities.P_Names=Forms!Form1!comType ORDER BY Complaints.ComplaintShort;
Im trying to develop a query which will show me players with the same 'player number' in a team.
I have two tables; players and teams.
Each player in a team should have a unique player number, so I want it to show records if eg Jim and Bob are both in Team X and they are both number 6.
I'm having trouble using the "group by" function in my query. The option in design view is not showing up and I'm not able to figure out what I'm doing wrong. What is there in design view is Field, Table, Sort, Show, Criteria, Or. I seem to be missing the "Totals" option.
I have a table with a list of records, each record has a timeFrom this I have a query, what I would like the query to do, is group the records in the query by hours.
