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 have a table called StockTable with the following fields Location, Status, Serial, Make, Model, LastDate, DotNumber
I also have a table called FCDateRange with three fields
DateStart DateEnd and DotNumber
For example
3/7/13 - 3/13/13 - 1 3/14/13 - 3/20/13 - 2
Im trying to figure how to write a query that if the lastdate from the StockTable falls during the DateStart and DateEnd fields it will assign it the number in the DotNumber field
I'm building a quick database, but some of the data to go into the database is quite... odd...
Anyway, the point is, I have one sheet that says
"Job Number 1200-1245" which would have all the same details, dates, etc.
Is there a quick and easy way to put in a range, say J1200-J1245, enter the data once, then be able to query... for example J1212 would return the same data.
I am creating a query which determines the number of days a payment is delinquent. Is there a function I can use in a query field that would look at the numeric field and if the value of the subject field was: < 60 Days "30-59 days" < 90 Days "60-89 Days" <120 Days "90-119 Days" >=120 Days "120 Days +"
It was my understanding that the IIF function tests for a condition and can only return yes/no results. I seem to remember that SQL had something like a least or most function that would allow me to set maximum value for range of days. I apologize that my question is confusing and hope you can understand what I am trying to accomplish. Thanks for any help
I am trying to make a query that finds all records by the "Month Number" and have problems.
What I did: Month(DateField) I got month numbers like I wanted but when I typed in the criteria below, I got February thru December Records, but when I search on anything "up to" 9 it works fine.
I want: Between{month1] and [month2] Month1= 9 (example) Month2= 12 (example) Need results to be from the requested months but any year
I have to imagine it's how the date is converted to an integer, but not sure.
I'm trying to create some code that will create a new folder depending on the ID that is currently being added to the database.What happens at the moment is a new ID is generated which in turn is job reference.When this is added to the database a folder is created with a prefix of this ID number and a 20char test specified in a text field by the user.
What I am trying to achieve is this:If the ID = 57...Then a folder is create called 1 - 500 (and ignore if one has already been created, which it should have at ID number 1)...When the ID Number 501 comes along another new folder is create called 501 - 1000 etc etc.The idea is not to have 3000+ folders in just one folder making it look a bit messy and lengthy to look through.
When using the Format:'yyyy mm dd' in access queries. When trying to show a week is there a way to show the date range instead of week number.
Week 01 = Jan1-7. If the results are only week numbers that makes it hard for people to understand when it actually is. Bottom line is: I would like to convert Week number to actual date range. Can anyone help with this?
I was wondering can MS Access do a range compair look up. I have 2 tables. One is the Info table and the other is Rate table. The Info table has 3 columns like this:
[code]...
I want access to read the Info table and pick up the values in the FICO, LTV and Type and compair it. Like with the above example. In the Info table FICO = 622. So then I want it to carry the number 622 down into the Rate table and compare that the number 622 is in FromFICO and ToFICO column to find where does 622 falls in between. In this case there are nine rows in the Rate table that 622 is between 620 and 629. The next criteria is the LTV number in the Info table. It's 76. So back into the Rate table looking for the number 76. And 76 is happen to fall between 75.01 and 80.00 in the FromLTV column and ToLTV column. Next back to the Info table is the Type column which is 2. Now back to the Rate table to look up the Type column for Type = 2. So from FICO = 622 , LTV = 76 , Type = 2...So the rate I want to populate is 0.25...So is this possible to do in Access?
I am looking for a way to use vb, or any other way, to allow my users to enter a railcar initial and then a number sequence and add new records to the end of the table.
For example in a form the user can enter the car initial and the number sequence Railcar initial: GATX Number sequence : 290001 - 290100
I would like a way to create a temp table that then has 100 records GATX 290001 GATX 290002 GATX 290003 etc etc.
I can then use this temp table in an append query to add them to my main table.
Ok, not sure if this is even possible or where to even start..
I've got a form that has all the info for a client, eg.. Client First Name Client Last Name Client Hours Client WE/CS/EE Client Day And Times Client Phone Client Comments
Begin Date & End Date (2 boxs) on there for date input..
and what i'm trying to do is limit that any date entered between begin date and end date, it'll check and make sure there is only ever 20 clients on any 1 day..
i thought Datediff would be the way to go, but then again how do you get it to search each day and make sure theres only 20 clients on there..
Code: ' count records in query Dim rs As DAO.Recordset Dim db As Database Dim strSQL As String Dim beginDatum As String Dim eindDatum As String Set db = CurrentDb
Ihave created a table that contains student name, last name address and GPA. THe instructor asked :You must find all the students that have a GPA of 3.0 or greater. Sort them so the highest GPA is the first student in the query.
I've read through many of the threads relating to sending Email from Access but can't seem to find quite what I'm looking for. I work as a Tutor Coordinator at a local college and I've set up Access to handle a lot of the day-to-day chores that our administrative assistant has to deal with.
One feature that would be absolutely wonderful would be to create and send Emails to students at the click of a button. I have several types of Email I'd like to be able to set up, but the one most needed (and probably most complex) is this:
Students come in to the office and request a tutor. If we have a tutor available, we assign them to an available tutor. If there is no tutor availabe, then every Friday we send out an Email to each individual student saying something like this:
"Dear John Doe - We currently do not have a tutor for MATH 101 at this time, but when one becomes available we will let you know... blah, blah, blah..."
I'd like to set it up so that the body of the message comes from a template - such as a text file (that is easily edited by a user) and populated with fields from a query.
Probably do something like this:
Query the Student_First_Name_field, Student_Last_Name_field, Department_Name_field, Course_Number_field, and Student_Email_field. Send an Email to each Email address pulled from the Student_Email_field and tell them the following - "Dear <first name, last name> we currently do not have a tutor for <department name, course number> at this time... blah, blah, blah..."
Currently the way the administrative assistant handles this is to send each student an Email individually - manually creating each Email with the specific data needed. As you can guess, this is quite time-consuming.
I'm rather new to Access but have been a RDMS programmer (in PICK) for several years. I know what it is I want to do, but don't know how to do it in Access.
Any help?
Would the best way be to use text files for templates?
Can text files be set up with field codes (Access recognizes "<LAST NAME>" to be Student_Master_Table.Last_Name_field)?
I'm guessing this will likely have to be a Visual Basic loop that works with data from a query.
I have a simple database with 2 tables, students and progress. I need to set up my database so that when I create a new record for a student (using a form I've created) it automatically creates 4 new entries in the progress table using the ID I have generated in the form and a task number (1-4) for each of these entries.
Additionally, once all tasks are set to complete = true, I need to set the field "all tasks complete" to true. I'd like to do this all without vba if possible.
I can't quite get my head around this - any help is much appreciated.
I have (amongst others):
tblStudents (containing basic info. name etc) tblClasses (containing a list of studentIDs with classes they are in, so, for example, there may be 3 entries of studentid "1", each with different classes next to them, to show that studentid 1 is in classes: a b and c for example. tblGrades (containing studentID and masses of fields with different grades for different subjects in.)
What I would like to do, is select the class name from a list (which I can do, no problem). Open up a form listing all the students in that class, with all their grades next to them.
This SOUNDS simple - but I don't think it really is! I could just be having a VERY simple moment though too - if so, I apologise!
I've been designing a database for a private school. Every Student attends some Lessons in specific Classes.
To my knowledge I can meet the above requirement by using a join table which holds StudentID, LessonID and ClassID, or to have a join table StudentsandLessons which holds StudentID and LessonID and another table with a one-to-one relation with StudentsandLessons which holds the ClassID for every Student-Lesson record.
The first way has the disadvantage of not allowing null values for records. This is a problem for me, because in the school the want firstly to enter Lessons and afterwards - when Schedule is ready to - enter Classes. I thought a solution to the above restriction could be the Classes field to have a default value e.g. NoClassYet but this isn't aesthetically correct for me. The second way overcomes the above restriction, but it doesn't seem "programmatically " correct to me.
Any ideas would be far more than just helpful. Thank you in advance
I am creating a Stock investment Profit and Loss Query, therefore I need to get the prices from the last day. The problem is that the last day can be a holliday or weekend when the market is closed and when it happens the query returns no price.
My question is: Is there any function or any other way I can get the last day DATE from a range of dates?
I used UNION ALL to get results from two queries and I Succeeded.Now I want these results to be in a date range, so I want to enter the "starting date" then the "End Date" to have may results in specific date range.This is the original code out of UNION ALL which is working fine:
SELECT Count(Patient.PatientID) AS CountOfPatientID, Patient.CauseOfAmpLowerLt FROM Patient GROUP BY Patient.CauseOfAmpLowerLt HAVING (((Patient.CauseOfAmpLowerLt) Not Like "")) UNION ALL SELECT Count(Patient.[PatientID]) AS CountOfPatientID, Patient.[CauseOfAmpLowerRt] FROM Patient GROUP BY Patient.[CauseOfAmpLowerRt] HAVING (((Patient.CauseOfAmpLowerRt) Not Like ""));
And this is what I tried:
SELECT Count(Patient.PatientID) AS CountOfPatientID, Patient.CauseOfAmpLowerLt FROM Patient GROUP BY Patient.CauseOfAmpLowerLt HAVING (((Patient.CauseOfAmpLowerLt) Not Like "")) UNION ALL SELECT Count(Patient.[PatientID]) AS CountOfPatientID, Patient.[CauseOfAmpLowerRt] FROM Patient GROUP BY Patient.[CauseOfAmpLowerRt] HAVING (((Patient.CauseOfAmpLowerRt) Not Like "") AND (PatientService.[Date of Service]) BETWEEN [Start Date] AND [End Date]);
I'm trying to produce a query that shows all records of patients that have a 'non-active' status (stored in the 'Patient Details' table) and haven't had any deliveries after 31/10/2011 (date stored in the 'Deliveries' table). I've tried a few different ways including using NOT IN (which access didn't like!) but I'm still no closer to getting the correct records.
I have two tables. One is about 160 thousand records, which is a part number, a contract price and a day the price took effect. The items appear several times. Prices go up and down over the course of the data, and items are added and dropped.The second table is a list of dated sales and quotes of those items over the past 14 months.(About 10 thousand lines)
I need to match the items with the contract price that existed on the day the quote or the sale was created, so as to demonstrate we were always at or below the contract price.