Finding Missing Classes, Pt II

Jul 18, 2006

First I want to thank everyone who sent me a response to my original problem. Unfortunately I still have not been able to get the results that I need. So for those new individuals who may have missed my original thread I will try again:

I have 1 table (Student Records) that looks like this:

Last Name, First Name, Class Title, Completion Date

And I have a 2nd Table (Course List) that looks like this:

Class ID, Class Title, Frequency

What I am looking for is a query to tell me which of the classes in the table Course List are not in Student Records Table. Basically I needto know which students haven't completed a course.

Any help will be appreciated. Thanks

View Replies

Find Missing Classes

Jul 11, 2006

I have two tables. The first one lists all of the courses that a student has taken. The second table list all available courses. I need a query that will tell me which courses the student has not taken. I tried using the find unmatched query to no success. Any help will be greatly appreciated.

View 5 Replies View Related

Finding Missing Numbers In A Sequence

Feb 1, 2005

Hello, I have no idea what to search for to see if someone else has already asked this question. I've tried several things with no luck. So here goes...

I work in a library. We deal with thousands of journal subscriptions--we cancel our subscriptions and order new journals all of the time. Every paper journal we have is assigned a unique number, called a ZP number. When we throw out a journal, that ZP number can be assigned to a new journal.

I would like to find the earliest missing number in the sequence without having to check manually. Is there a way I can do a query or something to find missing numbers in the sequence? The ZP numbers start with 1 and go up to 9999.

So, I have two fields: Title and ZP#.

How can I do this?

This is a cloned table, used solely for assigning ZP#'s, so it can be altered in any way--including adding records with blank titles for each missing number.

Thanks,
Siena

View 8 Replies View Related

Finding Numbers Missing In Sequence

May 8, 2007

Hi

I've searched the forum and only found one thread which covers this but doesn't help me.

I have a table with peoples individual information in it.

Every person has a unique ID number.

I want to find the missing ID numbers when I delete a person so I can keep the numbers in sequence.

I am not using autonumber. The reason for this is coloured wristbands. Each wristband has a unique number and they are broken into 4 colours. each colour has a number range 1 - 300, 301 - 600, 601 - 900, 901 - 1200. Each person in the table has to have an ID number matching a wristband. The people are also broken into 4 colour categories. This means that not every range is filled before i need to start using the next range.

Hope that makes sense.

Snab

View 7 Replies View Related

Adding Students To Classes

Sep 2, 2015

I am in the process of setting up a DB for my partners school.

I have student and class tables. I have these linked in a many to many with a junction table.

I am making a student data entry form and I cant work out how to add the student to the class.

Ideally I would have a combo box to select the class and then when one is selected a fresh empty combo box would appear beneath it.

View 4 Replies View Related

Students, Lessons, Classes Problem

Oct 31, 2006

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

View 6 Replies View Related

Forms :: Enter Multiple Classes To A Record

Jan 21, 2014

I have a new scenario today! I have searched database design to try to figure this out and have an effective database as well as adding multiple records to one table that is linked to 1 record in the other table.I have a master student list with their information that has the fields

Student ID
LastName
FirstName
Address
City
Zip
Community

I have another table that has the fields

ClassName
Date
Community
Instructor

What I would like to do is be able to create a form that can add multiple classes for each student. For example, I have Student 1. Student 1 has attended class A, B, C, and D. I would like the form to have the student's name and ID with a way to add multiple classes linked to that student.

View 9 Replies View Related

Register A Student To Multiple Classes (forms, Combobox, Checkbox, Listbox, Query)

Oct 7, 2005

Hello everyone,

I tried to post this yesterday but it didn't go through for some reasons.

I've attached my database that contains student, classes, and couple other tables. I also created couples of forms and I wanted to register a single student to multiple classes at once.

For example: There're 3 classes (Math, English, History) on 1/3/2006. Student A wants to register 2 classes, Math and English. I should be able to check those two and submit and I'm done.

Currently, I have to select Math and assigns to student A, then select English. That's not good at all. What if there're 10 classes in one day and a student just want to register 8 classes? I have to do eight times.

It'd be great if you could help me. Thank you in advance.
Isabel

View 2 Replies View Related

Register Multiple Classes For A Student (form, Combobox, Checkbox, Radio, Listbox)

Oct 6, 2005

Hello,

I'm interested to know how I can select a (1) student and assigns to multiple classes.

Attached I have the database with some tables and forms. Please take a look. I can assign a student to a class, on a specific day but I couldn't figure out how to select multiple classes at once.

Thank you for your generous help.
Isabel

View 10 Replies View Related

Missing Records (and Missing Updates)

Oct 7, 2005

Hi all

This is an ongoing problem I have had for 4 weeks now.

I have made a a system thats acts like a clock In/clock out Out system.

the structure is somthing like this
ID
Username
tblDailyLog
TimeIn
MorningBreakOut
MorningBreakOut
LunchOut
LunchIn
AfternoonOut
AfternoonIn
TimeOut

All fields apart from ID (autonumber) and username (String*255) are Date field (there are a few others like DateOfTimesheet etc but they arnt important here)

When a user arrives in the morning they make a record which they use for the day

They then have a form with a whole bunch of buttons which simply updates the correct field. For example they click the "Sign in for the Day" button and it updates the correct field with the current time.

Everything was going fine until people noticed that every now and again a sign in time dissapeared.

I have hacked myself to death trying to solve this problem but still the updates go Astray.

Now each time a time is updated the process goes somthing like this

1. the user opens their timesheet for the day (the RS is SNAPSHOT and no locks)

2. User Hits a sign in/out button
3. The record source is changed to "" and all buttons hidden (to ensure the record isnt locked and to make sure you dont do two things at once)
3. The table is updated with the new time (using some dynamic SQL)
4. The table is repeatadly checked using a DO loop to make sure the the correct time went in.
5. when the returned time value of the field matches the varaible used to update it, the form is returned to normal and the user carries on his/her merry way (if it never matches the screen should crash but this never happens).
6. A New record is added to another table called "tblbugfixinglog" which records which field was updated and when. This is so that I have two records in two different ways (figured if one went astray I could pull it back off the other)
7. Another new record is added to yet another table called tblSQLRecord, which simply logs all .RUNSQL statements that are executed.

I thought that the two extra tables (and the check that the record had been updated) would help me track down where the records are going missing, but this isnt the case.

Now it appears that some records arnt being added to tblBugFixingLog and to tblSQLRecord either and some of these tables are getting quite a few #ERROR's in them..

None of the tables are related to any other and i've no idea how #ERROR lines are appearing in a table that has 1 function... to recieve new records ... no editing, no viewing, no deleting.

Does anyone have any idea how these updates/inserts can go missing or create #ERRORs.
I've built plenty of Databases in my time and have never come across this.
__________________________________________________ ______________

This is the function I use to add a record to tblBugfixingLog and tblSQLRecord

Private Sub AddBugLog(ByVal TimesheetNumber As Long, ByVal FieldUpdating As String, ByVal NewFieldValue)
Dim TempSQL As String
TempSQL = "INSERT INTO tblBugFixingLog (TimeAndDateOfEntrySERVER,TimeAndDateOfEntryPC,Fie ldUpdated,NewEntry,UserID,TimesheetNumber,Computer AssetNo) VALUES (" & _
"#" & Format(ServerGetTime(Environ$("LOGONSERVER"))) & "#," & _
"#" & Now & "#," & _
"'" & FieldUpdating & "'," & _
"'" & NewFieldValue & "'," & _
"'" & GetNTUser & "'," & _
"'" & TimesheetNumber & "'," & _
"'" & fOSMachineName & "')"
' MsgBox TempSQL
DoCmd.RunSQL "INSERT INTO tblSQLRecord (Username,DateAndTime,Screen,TheSQL) VALUES('" & LoginInfo.sUsername & "','" & CStr(Now) & "','Add Bug Log function','" & CleanData(TempSQL) & "')", False
'CleanData is a function that removes ' and " from the SQL string so i can easily add the SQL string into the table
DoCmd.RunSQL TempSQL, False
End Sub

Public Function CleanData(ByVal DataToClean As String)
Dim TempData As String
Dim i As Integer
TempData = ""
For i = 1 To Len(DataToClean)
Select Case Mid(DataToClean, i, 1)
Case "'"
TempData = TempData & "`"
Case """"
TempData = TempData & "`"
Case Else
TempData = TempData & Mid(DataToClean, i, 1)
End Select
Next i
CleanData = TempData
End Function

__________________________________________________ ____

I have no idea how this can create #ERROR lines in the table when it is just added to and nothing else.

Does anyone have any clue to what may be happening here.

(Oh yeah and no matter how hard I try, I can't replicate the problem.... works for me every time no matter how harse I am to it!)

Please save what little hair I have left and give me some hope

Cheers
Homer

View 1 Replies View Related

Finding Help?

Aug 21, 2007

Hi all:)

I am looking for some advise, I work for a small service company and we our trying to computerize our service jobs through the use of access 2007, We have completed 80% of the database but are having a few problems completing, for example the search function on some forms etc, I have searched the web for help and have posted the problem on this forum a number of times but I am still unable to get it to work myself as I am new to access , the problem is I need the database completed asap before we take on a bigger contract and I am running out of time, so I was wondering if anyone knew of anyone / or any company out there that could have a look at the search form, I would obviously pay for it, I have tried to approach a few companies but they will not take on such a small job, they only want to take on the development of the entire database?
Anyone know of anyone?

Jackie :)

View 2 Replies View Related

Finding #

Nov 4, 2005

I'm searching through some text fields like the following:
Ann12 #1
Ann12 #2
Ann12 #3
Ann1 #12
Ann2 #14
ect...
I'm doing a like query
Like "*#*2*"
However, the # is the keyword for Date, so what do I use instead?

View 1 Replies View Related

Finding ABC

Aug 17, 2006

Hi

I hope someone can help with this as I can't seem to get this query to work.

I am trying to use a query to search for the highest value in a column but based upon a specific criteria.

i.e. in the column will be ABC1234, ABC1235, ABC1236 and BBC1234 and I want to get the highes 'ABC' value

I looked on the forum and found this code

Dim strSQL As String
Dim MyValue As DataType 'Replace the proper datatype of field
Dim rs As ADODB.Recordset, Cn As ADODB.Connection
Set rs = New ADODB.Recordset
Set Cn = New ADODB.Connection
Set Cn = CurrentProject.Connection
strSQL = "SELECT tableName.ColumnName FROM tableName ORDER BY tableName.ColumnName DESC"
rs.Open strSQL, Cn, adOpenStatic, adLockReadOnly
MyValue = rs!ColumnName
rs.Close
Set rs = Nothing

I've modified the query to this

strSQL = "SELECT tableName.ColumnName FROM tableName WHERE tableName.ColumnName Like 'ABC*' ORDER BY tableName.ColumnName DESC"

This doesn't bring anything back but I was expecting 'ABC1236'

I'm new to using 'Like' so this could be the problem.

Your help is much appreciated.

View 6 Replies View Related

Finding BAC-5, BAC-8 And BAC-12???

May 3, 2007

Greetings all,
It's been a while since I've had to turn to you for help. I'm usually able to find what I need in searching through previous posts, but no luck this time around.

I am trying to write a query criteria that will return

BAC-5, BAC-8 And BAC-12

I can get the -5 and -8 with:

Like "BAC-[58]"

But I can't figure out how to get the -12...

The actual part numbers are quite a bit longer and there are hundreds where I only need specific dash numbers returned.

I'm sure this one will be simple, I just couldn't seem to figure out how to search for it.

Thanks in advance!

View 3 Replies View Related

Finding Something Thats Not There?

Sep 27, 2007

Hi guys

I have a DB for insurance permits.

tblSubtractors
ID
NAME
ADDRESS
PHONE

tblPolicy
ID
SubbyID
POLICYID
Insurer
PolicyNumber
ExpiryDate

tblPolicyTypes
ID
Name

Most subcontractors will have a policy number for each type of policy in tblPolicy. But is there a way I can run a query to show each subby that doesnt have one of the policies? As an example, the query would show me that ABCcontractors doesnt have Marine Transit insurance. XYZContractors doesnt have Public Liability, etc

I know how to show all the policies that have expired, but I'm looking to identify which ones dont have a policy at all

:confused:

View 7 Replies View Related

Finding ID

Aug 6, 2006

Hello,
I have this code that I use to update a record:

Code:<%Dim idid = session("loginID")Dim rsSaveSet rsSave = Server.CreateObject("ADODB.RecordSet")rsSave.open "log", conn, adOpenDynamic, adLockOptimistic, adCmdTablersSave.find "id=" & idDim Datumlogout, Uurlogout, loggedoutOKrsSave("Datumlogout") = datersSave("Uurlogout") = timersSave ("LoggedoutOK") = ("true")rsSave.updatersSave.closeset rsSave=Nothing%>

Can someone help me please, I would like to have the code changed to:

When the ID not excists, that there is a new record written.
Thanks in advance!

View 1 Replies View Related

Finding An Average

Aug 8, 2007

I have a customer concerns database that contains the dates for when the concerns were reported and tyhe dates for when the concerns were resolved. I am trying to make a query that finds the average of how long it takes for the concerns to be resolved. How can I do this?

View 1 Replies View Related

Finding The Last Row In Worksheet

Feb 29, 2008

Now this is not like me, posing a question, but I thought its about time I codes the app correctly.

My app opens up any given spreadsheet. The first task is to establish the last row used in the spreadsheet. Normally you would loop until there is nothing in a cell in column A, whatever.

However the way the spreadsheets are provided there may be pleanty of blank rows. I have tried using the Edit GoTo Special LastCell and converting it to a macro

But when I run this VB it does not like it for some reason.

I am sure someone out there has come up with a solution by now.

CodeMaster::cool:

View 4 Replies View Related

Finding Current_Date?

Nov 2, 2005

I have a table, with 4 records in it. 2 records have the "date" field for 01/11/2005, and 2 dates are for 02/11/2005.

Is there a way to just query the "current" date? Weather it be in a pop-up box once the query is selected, asking for the date? or is there a restricted word within access2003 that lets you find the current date?

I would essentially like a command button within a form to allow the user hit teh button, and it will automatically find those records within the DB with the current date(set to system clock) and export it to excel(i have this portion, the exporting to excel complete)

Any help would be greatly appreciated.

Connor

View 7 Replies View Related

Finding Max Date

Jul 21, 2006

ok ihopefully i wont make this too complicated to under stand.

i ahev a form which will populate a form from a query. the query is pulling info out a table included int hti sinfo is the date when it was entered. in the criterai of the query i hve added this code
Between [Forms]![Boxes Reviewed]![txtFromDate1] And [Forms]![Boxes Reviewed]![txtToDate1]
whcih means that the date must be between the two differnt dates that must be entered on the form.

the problem is, is that a value can be entered more that once with different dates. i woul dwant the report just to shouw the most recent date. i considered doing a query which requested all max dates from the table and then having a query calling forma query. but i do not know enough on how to get just the most recent date.
so if i wrote a query which held

box num, shelf num, resp_person, and date. what would i have to put int he criteria to only bring back the entery witht he most recent date.

sorry if i have rambled and look forward to your help

View 1 Replies View Related

Finding First Records Only

Nov 1, 2006

lots of customers. lots of orders. i need to query for the first order of each customer only. thoughts?

View 1 Replies View Related

Finding A New Record

Feb 24, 2005

Hi

I apologise if this is really basic - I am learning as i go along. I have designed a database. Most of the info is stored in the clientinfotbl. On the main form, I would have provided an option for users to search for a record based on its ID number and then pull up various forms accordingly. I am using the "find and replace" option to do this. My first question is, is there a better way? This feels a bit clunky!

Second question is, once I have entered a new record in the "newclientfrm" and go back to the mainfrm and search for that record, it cannot be found (once I have closed db and gone back in, it can then find it). Is there a way round this?

Any help would be hugely appreciated :)

View 2 Replies View Related

Not Finding Other Form

May 12, 2005

I would like to make a value equal to another value in another form, but it keeps telling me that it can't find the Form.

newID = Forms![frmLogIn]![UserID]

Error message is 'Access can't find the form 'frmLogIn' referred to in a macro expression or Visual Basic code'.

I have rechecked the spelling and caps on the form name 5 times so that's not it :(

James

View 5 Replies View Related

Finding The Subform????

Sep 1, 2006

Here's a real stumper... I have a form with a subform based on the account number in the main form. This form has been running without a hitch for maybe two years! Now, while updating an unrelated form in the same database, I test the new additions. I get an error message that the main form cannot find the subform, and it goes on to display a blank white box instead! Here's what I have already tried:

1. Checked the name of the subform. It's correct, just like it's been for 2 years.
2. Running compact/repair in case of corruption
3. Deleting, then re-placing the subform on the Main Form.

One other wierdism - after getting the white box where the subform belongs, and after being told that the [subform name] can't be found, I can right-click on the white box, select the "Form" option, and the subform appears! To me, that means that the "can't find" error message is a misnomer, but I'm at a loss as to where to look from here.

I guess what I'm asking is if anyone has seen this behavior of a form "not finding" its subform, yet it can be forced to display it with a popup menu? Also, any experiences with this or any related information would be appreciated.

Thanks-
Scott

View 3 Replies View Related

Finding The Last Day Of A Month

Oct 4, 2004

I need to create a function that will return the last day of a month, regardless of which month it is and, in the case of February, which year it is. (Gilbert & Sullivan got it right - the powers that be played a cruel joke on us with leap years!) I could hard-code in the months into my code, but there's got to be a way to do it without typing in everything manually...right?