hi everyone,
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.
Any suggestions?
Many thanks
Will.
I have 1 table with multi Text columns (location, coaches,ssn etc), and
multiple Num columns (pts and product counts)
I need to sum all the unit counts & points for each person, but only
bring back the location for each person that had the 'max' sales (pts).
ex. ssn xxxxxx has 3 sales codes : code 1&2 had less sales aquired
then sales code 3. Sales code 1 was location Vt, slsCd 2 was location
CA and slscd 3 was location FL. I need to bring back the sum of all
three codes but only the location FL as it had the most sales pointing
to it.
I have deadline of Monday to get this done.PLEASE help
Hi all....
Preface: Main table called tblMain. Here are three of the fields that I am interested in setting this up for.
tblMain
Field 1: [IncidentDate]
Field 2: [Address] (actually street numbers)
Field 3: [CityStreet] (actually street name via lookup field off another table)
Query based of the above tables.
What I want to query is this:
If location has an entry on July 4th, 2006 then it counts it, if the same location has has another entry it counts it. If it only has a incident on July 4th, 2006 an no other prior incidents do not display it... Does this make sense.
So it would look something like this in a report generated off of the query.
07/04/06 123 Main St
07/03/06 123 Main St
05/05/06 123 Main St
07/04/06 125 South St
07/02/06 125 South St
06/22/06 125 South St
Thanks
R~
I am attempting to build a small database for my firm to keep a track of equipment. The equipment can be in one of three places. In the warehouse, out on a job or at the repairers. I want to create a query that will let me know where a piece of equipment is at any one time. I'm sure my tables are set up correctly. I have use a union query to work out when equipment is on a job or in repairs but I need one to show me where all the equipment currently is.
View 1 Replies View RelatedI have a query that combines few different tables in order to create a View (Query) that is then used to by and Excel sheet to update a list. The Excel is dynamical updated when new data is inputted in the Access Tables.
But one of the fields in a query is combination of path name and report name (another filed in one of the tables) that crate a complete path to a file that contains some additional information.
Now since the Excel is updated dynamical and users of this Excel sheet are not very advanced. I waned to make it easy for them to just click on the location of the file and the file opens up. But I am not able to make the Query that contains the file destination hyperlinked.
I am using Access 2007
I am trying to export a query or table to a location that the user selects. So each time the export button is clicked the folder will change. But I want to be able to select the folder.
The code I have below is saving but not to the folder I want it to.
Private Sub CommandBtn_Click()
Dim fileSelection As Object
Dim strPath As String
Set fileSelection = Application.FileDialog(4)
With fileSelection
.AllowMultiSelect = False
If .Show = True Then
[Code] ....
Despite Google I can't seem to figure this out.
I have some data in a format similar to:
Name / Style / description / speed / distance
john / driver / careful / 80 / 5500
mary / driver / careful / 70 / 7000
pat / racer / reckless / 100 / 6000
anne / driver / careful / 75 / 1000
peter / racer / reckless / 110 / 6500
don / snail / slow / 60 / 6000
I want my report to total by style, without details and to look like:
driver careful 13500
racer reckless 12500
snail slow 6000
How do you get a report to sum the group items by a specific item and to hide the details of that group summing?
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?
View 5 Replies View RelatedHello,
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.
Any help would be great!
Thanks,
Steve
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.
EMP#------TRXDATE------TS-DATE----TYPE-----HRS
1----------01/28/07-------01/28/07----100------44.00
1----------01/28/07-------01/21/07----100------44.00
1----------01/28/07-------01/28/07----300-------7.00
3----------01/28/07-------01/28/07----100-------1.00
what i need the query to show for this data would be
EMP#-----TRX DATE----TS-DATE-----REG(100)-OT(300)--TOTAL
1---------1/28/07------1/28/07------44--------7---------51.00
1---------1/28/07------1/21/07------44--------0---------44.00
3---------1/28/07------1/28/07-------1--------0----------1.00
what it basically is would be merging everything so that each TS-DATE would get it's own line in the Query,
any and all ideas and recommendations would be more than greatly appreciated!
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?
Any help would be greatly appreciated.
John
Hi,
i am new using SQL statements,
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,
----------------------------------------
Serial_Number | Pass | Fail |
---------------------------------------
A_____________2______1
B_____________0______ 2
C_____________1______ 0
----------------------------------------
does anyone has an idea how to do this in Access?
Hi everyone,
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.
Table Data:
EmpID, FirstName, LastName, SupervisorID
111, John, Doe, 222
222, Jane, Doe, 444
333, Carl, Smith, 444
444, Sally, Gates, 555
Desired Query Results:
John Doe, Jane Doe
Jane Doe, Sally Gates
Carl Smith, Sally Gates
Sally Gates, etc.....
Would I need to use a GROUP BY in my statement?
Thanks,
CRhodus
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.
is this possible?
Ok, here's what I've got.
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;
Any ideas??
Thanks!
http://i45.photobucket.com/albums/f73/VolcomGrrrl79/access.jpg
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]));
Thanks
ex
columns
Name Day1 Day2 Day3 Day4 Day5 Day6
Jackie s s
Jackie v v
Jackie p p
I would like to be combined into
Jackie s s p v v p
all in one row.
The query below return this data in it's rows:
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’m having the simplest query problem but for the life of me I cannot not figure it out. I have a table it looks like this:
Name DatePriceLocation
Smith03/03/0512.00Dallas
Smith03/04/0513.00Dallas
Smith03/05/0512.00Dallas
Smith03/06/0514.00Dallas
Jones03/03/0512.00Ft. Worth
Jones03/04/0514.00Ft. Worth
Jones03/05/0512.00Ft. Worth
Jones03/06/0512.00Ft. Worth
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?
Marty
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.
Thank you for your consideration and time,
Marty
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
Background:
I have a database that keeps track of containers -what they belong to, and what is in them. Each container has a “Call #” which is labeled on the container and is a primary key in the database. The “Call #” field is set to only allow 5 characters in this field. A standard “Call #” for example is "A 152".
When a new container comes into the system, which has not been physically labeled yet, I enter it in the database a little different. So let us say container "A 152" just came in, and has not been physically labeled yet. I enter the “Call #” into the system as "MA152". The "M" tell me it still needs to be physically labeled.
When I go to label all the unlabeled containers, I print a report with all the "Call #''s that start with "M".
I have another field in the same table called "Mark". In my forms I can then go through and click a "Mark Button". The Report prints all containers where the "Mark" field = yes(True). This feature is used for a number of different reports, not just unlabeled containers. To keep the database clean and in order, all “Mark” fields reset to no(false) whenever it opens up.
Because, I used the “Mark” field for other reports, simply clicking the “Mark Button” when I enter the new container into the system will not work.
My Question:
Is There a way to have a Query search for the first letter in a field. So when I go to print my report, a query runs looking for all "Call #s" that start with the letter “M”.
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....
Hi everyone
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.
Any ideas?
Thanks! :)
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.