Queries :: Return List Of Students Attended Certain Number Of Sessions At Gym
Sep 2, 2013
I'm trying to return a list of students in a particular team who have not attended a certain number of sessions at a gym, between two dates. For some reason i get the error. At most one record can be returned by this subquery. (Error 3354).
SELECT [Student ID], Count(*) AS ["Number of Times Attended"]
FROM Gym_Attendance
WHERE [Gym Date] BETWEEN [Forms]![SV_Attendance]![txtStart].Value AND [Forms]![SV_Attendance]![txtEnd].Value
AND [Student ID] Like
(SELECT [Student ID]
FROM Student_Sports
WHERE [Sporting Team] = [Forms]![SV_Attendance]![lstTeam].Value)
GROUP BY [Student ID]
HAVING Count(*) < [Forms]![SV_Attendance]![txtNo].Value;
I'm looking to move an excel sheet to access because the row counts are too much.The main thing it does is compare the supplied data against a list I hold in the sheet.There are not duplicate records, however..Some data is a direct lookup for a full match, but much of it is a count to see how many records contain a certain string.
I have 500 keywords which have a countif function in using wildcards.I need to create a query/report which will return a list of records from the original list which contains each keyword featured and how many times it features.I was going to do it in PHPmysql but the time it took to parse a million records for every keyword made it pointless.
eg: keywords: look billy magic
list: "have a look and see" "spanish dave" "who is billy brag" "looky looky I go hooky" "who's the man from argentina" "could it be magic now"
my spreadsheet would return a 1 next to ""billy" and "magic" and would put a 2 next to "look".
the sheet has the keyword in each row and next to the column: =COUNTIF(list,CONCATENATE("*@",B13)) where "list" is the external data.
i have a query with an inputbox for numbers [Enter Number]..if i want the query to return multiple numbers how would i do this? so the user could have 3 5 and 8 to search for, currently the [enter number] allows them to search for one at a time.
I have two tables, TBL_Students and TBL_Email. Each of these tables have a field called Category, which allows multiple values from a list.
Is there a way to return records from TBL_Students where at least one value in TBL_Students!Category = at least one value in TBL_Email!Category.
E.g. If Student A has categories Maths, Physics and Computing;Student B has categories Maths, English and History;Student C has categories Physics and Geography
I would expect: when TBL_Email!Category = Maths,Physics : Students A,B,C to be returned when TBL_Email!Category = Maths : Students A,B to be returned when TBL_Email!Category = Physics,Geography : Students A,C to be returned
I would like to run a query which takes a code which contains a mixture of number and letter and returns all the number before the first letter. I was using the left function (=Left(([Codes],2)) but sometimes there is one number sometime two. The desired results are shown below;
I have tried to use a bit of code so return the Last Year and WeekNo in my database, to select some records but�. It currently fails, I think this because it returns the values as text and will then not compare to a numeric field. Is there an easy way to change this code so it returns numbers.
I have a query which looks at a linked table (ODBC to SQL Server) in order to return a list of sub-accounts associated with an account. If I run it as a pass-through query it executes correctly, returning a list of the sub-accounts. If I run it as a query in Access it return the correct number of records but each sub account has the same name as the main account, as follows.
In a query I'm trying to return a list of rows sorted by Service Type Ascending and then the last item in the list should be a row called "Add Edit Value".If I 'ORDER BY 2' then the "Add/Edit" row appears at the top which is not what i want.
My SQL: SELECT '' As ServiceTypeID, 'ADD/EDIT VALUES' As ServiceType FROM ServiceTypes UNION SELECT ServiceTypes.ServiceTypeID, ServiceTypes.ServiceType FROM ServiceTypes ORDER BY 1 DESC;
I have a database of staff members. There is a table with the details of staff; name, dob, address etc.
I need to set something up that will record the training that staff attend and then be able to print a report of who attended the training session and also a report of training sessions individual staff members attended.
I need to set up fields in a table for date, topic and presenter and then I need to somehow mark off staff members who attended.
My application is protected with username and password. According to the user who has done the log in, some options will be available and some not.
Then, are there sessions in access?? How can I know who is loged in with which role otherwise?
My idea is to have a table, and say, loggin ok for the user X, but there may be one or more users at the same time. Then, how can I know which user is who if two are logged in in the same application by remote connection?
The first column is auto-incremented (primary key). We would like the last column to be a counter which identifies each session regarding a port number and a connexion number.
Is there a SQL or Access function which would allow us to do that?
Hi guys and gals, I have compiled a very simple database to track staff and training audit requirements for my company. I am 57 thick as 2 short planks and need some help please. I wish to ask a text box called "Score" to return a number 2 if a yes/No box is "NO" and a number "1" if the Yes/No box is "YES". I have looked at the help sections of Access and thought maybe an "IIf" statment in the Validation Rule section of the "Score" box might do the trick. However, i haven't got a clue as to what the coding should be written like. Can anybody help me please.
Basically i want to say "If YES/NO = NO then SCORE = 2 but If YES/NO = YES then SCORE = 1" Told you i was thick
I'm trying to create some VBA that will loop through my listbox, de-select all of the items, and then return to the top of the list without selecting the first item. Below is the code I am using based on what i was able to find through google, but the top item still remains selected.
Code: With Me.AvailableAttendees For SelRow = 0 To .ListCount - 1 .Selected(SelRow) = False Next SelRow End With Me.AvailableAttendees.SetFocus Me.AvailableAttendees.ListIndex = 0
In short, I want to know which records fail to append based on violating the primary key. (Win 7, Access 2010 32bit) Similar to how Access creates import table errors, but for append violations...
The DB takes a daily data dump. Failures to append (in this DB) indicate a status of "complete" that needs to be re-examined. (A true complete will not be present in the daily file). The daily file also contains all new daily work, thus it isn't just a list of "completed" tasks to re-examine.
Hi All, We're currently rolling out 2003 Runtime and I've had a few users requesting multiple open seesions of the same database. I thought sure no problem, and then found that it doesn't look like it's possible with Runtime. Can anyone confirm whether there is a way around this?
It's just that they all had full version of 97 before and now were limiting them all to Runtime! :D
After long, long, long hiatus of doing other stuff, I wanted to get around to update the database. One thing I had set up wrong was the attendance rosters.I just need to be able to create a new class, which may have more than one sessions, then make a list of registrants for a particular class, then be able to track their attendance per session for this class.However, my Google-Fu is lacking as every templates I've looked at doesn't really answer my question, which how three entities relate to each other.I know for sure that Class-Attendees is a one many relationship. Same attendee can register for more than one class, so that's no brainer. Likewise, Class-Session should be also be one many relationship for obvious reasons. However, if I relate Session to Attendee, it would create a circular relationship between the three entities. I've toyed with the idea of having a hierarchial tables of Class -> Attendees -> Sessions, but that does not relate the sessions to the class, which is necessary. I wonder if I should create a fourth table to contain attendances and use Session and Attendees as FK, but that still wouldn't fix the possiblity of a circular relationship?Any insights will be greatly appreciated. TIA
I am wanting to make a text box on a form, to return a number (amount of records returned by a query)basically so if the query returns 5 records, the text box on the form will show"5",
Lets say the form is called "A", and the query is called "B" .How do I put this in the source control of the textbox ? < if this is right too ?
I'm using Dmax() to return a max number in a field which I then want to inc by 1 for a new record. Dmax is returning 999. I believe Dmax therefore thinks it's a text field. So where do I change this to a number field?
I am trying to make a DLookup function to return the ID number of an entry that matches 2 or 3 criteria but I am struggling to get the syntax correct for the second and third criteria.
Here is what I have so far:
1 criteria, works fine =DLookUp("[timedata]![id]","timedata", "[processdone] =" & Forms![Mainform]![p11] )
2 criteria, works fine =DLookUp("[timedata]![id]","timedata", "[processdone] = " & Forms![Mainform]![p11] & " And [timedata]![BGSnum] = 1001" )
BGSnum is a numerical value but it changes for each form I load, so what I want to do is use the form location value as the criteria.
I have a module in my database that creates an appointment based on a table of sessions. The module works in all respects, except two:
1) When I declare optional attendees it sometimes shows them as required attendees in the actual meeting in outlook, but not always
2) The .Send method does not seem to work. It creates the appointment on my calendar as the originator of the appointment, but does not send to the other recipients.
Below is the code:
Code: Private Sub SchedOutlook() Dim rsEmployee As DAO.Recordset Set rsEmployee = CurrentDb.OpenRecordset("Select * FROM Employee INNER JOIN tblSession ON Employee.EmpID = tblSession.EmpID;", dbOpenDynaset) Dim rsMentor As DAO.Recordset
