Microsoft Access
--
Questions
--
Followers
Top Experts
Dlookup problem
Based on the following print screen provided i am trying to do a Dlookup on that query (the tables used are from an external database which i cannot edit). Is it possible to construct a Dlookup based on the following...
I need the student refernce numbers displayed in a text field (set to datasheet view) . They are currently stored in table UNITE_CAPD_STUDENT where the E_REFERENCE stored in table UNITE_CAPD_MODULEENROLMENT
question.JPG
I need the student refernce numbers displayed in a text field (set to datasheet view) . They are currently stored in table UNITE_CAPD_STUDENT where the E_REFERENCE stored in table UNITE_CAPD_MODULEENROLMENT
question.JPG
Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Ok ive found the Student references in the table UNITE_CAPD_MODULEENROLMENT
=DLookUp("E_ID","UNITE_CAP
but when it just produces the error '#Name?' am i calling the combo box correctly?
=DLookUp("E_ID","UNITE_CAP
but when it just produces the error '#Name?' am i calling the combo box correctly?
Appologies if this is an obvious question, but I take it the E_ID and E_REFERENCE fields exist in the UNITE_CAPD_MODULEENROLMENT
It would be well worth double-checking the spelling too - it has to be EXACTLY right for the DLookup to work.
It would be well worth double-checking the spelling too - it has to be EXACTLY right for the DLookup to work.
The other problem you may have is the data type of your E_REFERENCE field - if this is a text field you'll need to enclose the value in quotes, like this:
=DLookup("E_ID", "UNITE_CAPD_MODULEENROLMEN
=DLookup("E_ID", "UNITE_CAPD_MODULEENROLMEN
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
By the way, it'd also be worth giving your controls names that reflect how they're used - it makes forms a LOT easier to maintain!
In your case, I guess you should rename 'Combo41' to something like 'cmbReference'.
In your case, I guess you should rename 'Combo41' to something like 'cmbReference'.
You said ...
>>>>>I need the student refernce numbers displayed in a text field (set to datasheet view) . They are currently stored in table UNITE_CAPD_STUDENT where the E_REFERENCE stored in table UNITE_CAPD_MODULEENROLMENT
After reviewing your Query Grid in the picture you attached ..... Unless I'm totally missing your question here seems like all you have to do is replace the Criteria line for the E_REFERENCE field to the following ....
[Forms]![klookup]![Combo41
Basically you are saying you want to display the S_STUDENTREFERENCE field From the UNITE_CAPD_STUDENT table where the E_REFERENCE field in the UNITE_CAPD_MODULEENROLMENT
ET
>>>>>I need the student refernce numbers displayed in a text field (set to datasheet view) . They are currently stored in table UNITE_CAPD_STUDENT where the E_REFERENCE stored in table UNITE_CAPD_MODULEENROLMENT
After reviewing your Query Grid in the picture you attached ..... Unless I'm totally missing your question here seems like all you have to do is replace the Criteria line for the E_REFERENCE field to the following ....
[Forms]![klookup]![Combo41
Basically you are saying you want to display the S_STUDENTREFERENCE field From the UNITE_CAPD_STUDENT table where the E_REFERENCE field in the UNITE_CAPD_MODULEENROLMENT
ET
I got the impression the query was just to illustrate the table structure - it's the '=DLookup(...)' control source expression giving a value of '#Name' that's the problem.
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
>>>>I got the impression the query was just to illustrate the table structure - it's the '=DLookup(...)' control source expression giving a value of '#Name' that's the problem.<<<<<<
Originally I got the same impression until I looked at the Criteria line in the Query Grid for the E_REFERENCE field. He has Like "7*" in there which does not make sense if he is using the value of Combo41 to determine which values are selected.
I think he is trying to get the Select Query to return the values from the table based on Combo41. Therefore, he really doesn't need a DLookup.
ET
Originally I got the same impression until I looked at the Criteria line in the Query Grid for the E_REFERENCE field. He has Like "7*" in there which does not make sense if he is using the value of Combo41 to determine which values are selected.
I think he is trying to get the Select Query to return the values from the table based on Combo41. Therefore, he really doesn't need a DLookup.
ET
Hi guys,
Thanks for the replies, the E_ID and E_REFERENCE fields do exist within the table.
The E_REFERENCE field contains letters and numbers. So would i need the following?...
=DLookup("E_ID", "UNITE_CAPD_MODULEENROLMEN
and just insert '[Forms]![klookup]![Combo4
The following is exactly what i am trying to achieve....
'Basically you are saying you want to display the S_STUDENTREFERENCE field From the UNITE_CAPD_STUDENT table where the E_REFERENCE field in the UNITE_CAPD_MODULEENROLMENT
Thanks for the replies, the E_ID and E_REFERENCE fields do exist within the table.
The E_REFERENCE field contains letters and numbers. So would i need the following?...
=DLookup("E_ID", "UNITE_CAPD_MODULEENROLMEN
and just insert '[Forms]![klookup]![Combo4
The following is exactly what i am trying to achieve....
'Basically you are saying you want to display the S_STUDENTREFERENCE field From the UNITE_CAPD_STUDENT table where the E_REFERENCE field in the UNITE_CAPD_MODULEENROLMENT
Are you trying to get a QUERY to work or a FORM?
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Form
Have you tried my solution???
On the Criteria line for the E_REFERENCE field just replace the Like "7*" with [Forms]![klookup]![Combo41
ET
On the Criteria line for the E_REFERENCE field just replace the Like "7*" with [Forms]![klookup]![Combo41
ET
Appologies guys, this is part of a larger problem, if you need the whole story then its here....
https://www.experts-exchange.com/questions/23273226/Storing-external-and-local-table-data.html
https://www.experts-exchange.com/questions/23273226/Storing-external-and-local-table-data.html
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Just to be 100% clear, you've got the Control Source of one of the controls on your form set to the expression "=DLookup(...)', but when you display the form the value '#Name?' is displayed.
Have I understood your problem correctly?
Have I understood your problem correctly?
etsherman, i tried your suggestion but it breaks the dropdown box.
Thx.
Thx.
Yes Jez that is correct.
I need the various comments to link to a table called risk, i need to pull other values such as attendance % out of the unit E database. Obv if a lecturer chooses a course from the dropdown box and a student on that course has a comment saved in the table risk then i need it to display and also if there is no comment already entered they need the ability to enter one.
Sorry for the confusion.
I need the various comments to link to a table called risk, i need to pull other values such as attendance % out of the unit E database. Obv if a lecturer chooses a course from the dropdown box and a student on that course has a comment saved in the table risk then i need it to display and also if there is no comment already entered they need the ability to enter one.
Sorry for the confusion.
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
I which case what happened when you tried ID 21229873 & ID 21229892 above?
Ok, prehaps I misread your question. I was thinking you are trying to run a Select Query based on Combo41.
Where do you have the DLookup????
ET
Where do you have the DLookup????
ET
21229873 - They are names correctly
21229892 - #Name?
21229892 - #Name?
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Try temporarily simplifying the expression to just:
=DLookup("E_ID", "UNITE_CAPD_MODULEENROLMEN
This won't give you the right ID value, but at least it will tell you whether E_ID.UNITE_CAPD_MODULEENRO
What happens now?
=DLookup("E_ID", "UNITE_CAPD_MODULEENROLMEN
This won't give you the right ID value, but at least it will tell you whether E_ID.UNITE_CAPD_MODULEENRO
What happens now?
See attachment below. The S_STUDENTREFERNCE field works because the form is bound to the query. But obv i want the form to be bound to risk 2 so as it can save the comments (when i have comments fields) So the DLookup is currently the control source for text22
Oops, didnt attach image
printscreen.JPG
printscreen.JPG
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Jez, now all the text22 fields are 12388132
No attachment!
Sorry, looks like we're suffering from comment lag! :-)
So what happend when you tried ID 21230864?
So what happend when you tried ID 21230864?
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
It now has all the text22 fields set to 21230864 :-)
OK, so that confirms that the field 'E_ID' DOES exist in the table 'UNITE_CAPD_MODULEENROLMEN
Now change the Control Source tto:
=DLookUp("E_ID", "UNITE_CAPD_MODULEENROLMEN
Now change the Control Source tto:
=DLookUp("E_ID", "UNITE_CAPD_MODULEENROLMEN
It now has all the text22 fields set to 12388132
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
So that confirms that the field 'E_REFERENCE' DOES exist in the table 'UNITE_CAPD_MODULEENROLMEN
Now change the Control Source to:
=[Combo41]
Now change the Control Source to:
=[Combo41]
lol, back to #Name?
Dont forget, the combo is on its own form and this one pops up in dataview
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Forms]![klookup]![Combo41]
Seems to have done the trick.
Seems to have done the trick.
ASKER CERTIFIED SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Or, if Combo41 is bound to a numeric value this will be:
=DLookup("E_ID", "UNITE_CAPD_MODULEENROLMEN
=DLookup("E_ID", "UNITE_CAPD_MODULEENROLMEN
My trouble now is that if change the control source of the form so as its no longer the query but the risk2 tables then i get the following...(see below) I also have the following on event code on the button...
Private Sub Command40_Click()
On Error GoTo Err_Command40_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "qwerty"
stLinkCriteria = "[E_REFERENCE]=" & "'" & Me![Combo41] & "'"
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria
Exit_Command40_Click:
Exit Sub
Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click
End Sub
printscreen.JPG
Private Sub Command40_Click()
On Error GoTo Err_Command40_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "qwerty"
stLinkCriteria = "[E_REFERENCE]=" & "'" & Me![Combo41] & "'"
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria
Exit_Command40_Click:
Exit Sub
Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click
End Sub
printscreen.JPG
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
I take it you mean the Record Source for the FORM, not the Control Source of a CONTROL on the form?
Assuming I'm right, you're getting the 'Enter Parameter Value' prompt because E_REFERENCE is not one of the fields in the form's Record Source.
Assuming I'm right, you're getting the 'Enter Parameter Value' prompt because E_REFERENCE is not one of the fields in the form's Record Source.
I got [Forms]![klookup]![Combo41
Appologies, i meant to say the forms record source. You are correct in what you say, how can i get around this?
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
My confusion about Combo41 was because you didn't mention that there were two forms until ID 21231215.
As for your E_REFERENCE problem, (apologies if this is obvious) you'll have to add this field to your form's Record Source if you want to reference it!
As for your E_REFERENCE problem, (apologies if this is obvious) you'll have to add this field to your form's Record Source if you want to reference it!
lol ok but i want the record source to be the risk table so as i can save and retrieve comments from it
What is the Record Source set to when you get the Enter Parameter Value prompt?
Also, what table contains the E_REFERENCE field?
Also, what table contains the E_REFERENCE field?
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
the record source when the prompt appears is Risk2 (the table i have made to keep the comments in) the E_REFERENCE field is stored in the table UNITE_CAPD_STUDENT which is a link table which is joined to the UnitE database
Then you'll have to change the Record Source to be a query that JOINs the 'Risk2' to 'UNITE_CAPD_STUDENT'.
Is there any chance you could post a sample database - as long as there aren't any data privacy issues?
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Hmmm ok. Appologies, E_REFERENCE comes from the table UNITE_CAPD_MODULEENROLMENT
printscreen.JPG
printscreen.JPG
You can't join tables if there's no common information, unless one of the two tables always has only 1 record - is this what you have?
Actually i can use PSN and link it to E_ID i think
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
The result is now...
printscreen.JPG
printscreen.JPG
I take it your Record Source is now something like:
SELECT *
FROM risk2 JOIN UNITE_CAPD_MODULEENROLMENT
ON UNITE_CAPD_MODULEENROLMENT
Bear in mind that your form will then only display data where corresponding records exist in BOTH tables.
SELECT *
FROM risk2 JOIN UNITE_CAPD_MODULEENROLMENT
ON UNITE_CAPD_MODULEENROLMENT
Bear in mind that your form will then only display data where corresponding records exist in BOTH tables.
How about that sample database then?
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Record Source is.....
SELECT UNITE_CAPD_MODULEENROLMENT
FROM risk2 INNER JOIN UNITE_CAPD_MODULEENROLMENT
i dont think the join worked. So plan B was if a table only holds 1 record. This is not the case
SELECT UNITE_CAPD_MODULEENROLMENT
FROM risk2 INNER JOIN UNITE_CAPD_MODULEENROLMENT
i dont think the join worked. So plan B was if a table only holds 1 record. This is not the case
Here's one way of looking at your problem:
Suppose you have a form which you want to use to display Car Types and House Types, and you want to use bound controls to display the data in one record from the Car Types table side-by-side with the data in one record from the House Types table.
Because the two sets of information are entirely unrelated you can't join the correspnding tables and as a result you can't use this approach to display the two sets of data on the same form at the same time.
Suppose you have a form which you want to use to display Car Types and House Types, and you want to use bound controls to display the data in one record from the Car Types table side-by-side with the data in one record from the House Types table.
Because the two sets of information are entirely unrelated you can't join the correspnding tables and as a result you can't use this approach to display the two sets of data on the same form at the same time.
Yep im with you. So basically what your saying is that you cant join link tables with local tables if they have no matching data? Can you think of another way to approach what i am trying to achieve then? Ive tried 1000 ways and found 10, 000 problems lol
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Its a pain that i cant use subforms but the best way to display the info and have it editable is in datasheet mode also becuase different class's have different numbers of students.
You can't meaningfully join ANY table or query to ANY OTHER table or query without there being some related data. Basically, it doesn't make sense to display Chalk records alongside with Cheese records - after all, how would such a form show that there is more Chalk than Cheese (or vice versa)? When you delete records are you deleting Chalk or Cheese or both?
On the other hand, you CAN display Chalk in one sub-form and Cheese in a DIFFERENT sub-form of the same parent form but this is NOT displaying the records side-by-side, as the two sub-forms have independent Record Sources.
ChalkCheese.zip.txt
On the other hand, you CAN display Chalk in one sub-form and Cheese in a DIFFERENT sub-form of the same parent form but this is NOT displaying the records side-by-side, as the two sub-forms have independent Record Sources.
ChalkCheese.zip.txt
Try adding and deleting records using the qryChalkCheese query from my sample database after creating more Chalk than Cheese (or vice versa) and hopefully you'll see what I mean!
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Looking at the related question you mentioned earlier, doesn't risk2 have a field that concatenates Student ID and Course code and aren't both of these also returned by your UNITE_CAPD_MODULEENROLMENT
Assuming I'm right, wouldn't it make matters easier if you separated these fields out again and had a composite primary key instead (i.e. a primary key made up of two distinct fields)?
Hi Jez,
Sorry, i had to go into a meeting. Im home now but will be looking at this over the weekend and will post again on Monday. Thanks a million for all of your help. On tis site they dont have example databases do they? Do you have any you could upload for meto browse through?
Sorry, i had to go into a meeting. Im home now but will be looking at this over the weekend and will post again on Monday. Thanks a million for all of your help. On tis site they dont have example databases do they? Do you have any you could upload for meto browse through?
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
It'd be really helpful to have the database as you have it now - it'd save me a whole load of effort mocking one up this end, only to find it's nowhere near what you've got.
Can you pare down what you've got (including removing any private data), zip it up and upload it as an attached file?
Can you pare down what you've got (including removing any private data), zip it up and upload it as an attached file?
Hi Jez,
Yes i will try to send you a copy of the database on Monday morning when i get into the office around 8am. As previously discussed my solution is not possible so hopefully we can find a solution that is second best.
Yes i will try to send you a copy of the database on Monday morning when i get into the office around 8am. As previously discussed my solution is not possible so hopefully we can find a solution that is second best.
Morning Jez, here is the database...
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Hmmm, hasnt attached its a rar file with a .txt extension
Jez, I have now managed to link my query to my local table which is to be used for storing
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
I rather hoped you'd manage to pare this down to just the form you're having problems with - as it stands I'm afraid I can't read from any of your linked tables, so I can't run up your form
Would it be possible to post a 'stand-alone' version?
Would it be possible to post a 'stand-alone' version?
If you can post a sample database that works (for me!) I will look into this further, but in light of the latest comments from koutny (& me!) in this thread:
https://www.experts-exchange.com/questions/23273226/Storing-external-and-local-table-data.html
it may well be impossible to achieve the results you're after using datasheet view
https://www.experts-exchange.com/questions/23273226/Storing-external-and-local-table-data.html
it may well be impossible to achieve the results you're after using datasheet view
I realise that this isn't your problem, but in the meantime you should put an 'Option Explicit' statement at the top of ALL your forms/modules. If you do this and recompile you'll see three cases where you are referencing an undeclared variable (named 'Click').
You also should replace 'DoCmd.DoMenuItem' with 'DoCmd.RunCommand' - the former syntax was deprecated in 1997! If I've understood your code correctly these statements, these should read:
DoCmd.RunCommand acCmdSaveRecord
There is also no need for ANY of the 'Me!' or 'Me.' qualifiers when accessing controls on the current form. Finally, you should SERIOUSLY consider replacing the default names given to your controls by Access with names that reflect what they are actually used for - it will make your code a LOT more understandable!
You also should replace 'DoCmd.DoMenuItem' with 'DoCmd.RunCommand' - the former syntax was deprecated in 1997! If I've understood your code correctly these statements, these should read:
DoCmd.RunCommand acCmdSaveRecord
There is also no need for ANY of the 'Me!' or 'Me.' qualifiers when accessing controls on the current form. Finally, you should SERIOUSLY consider replacing the default names given to your controls by Access with names that reflect what they are actually used for - it will make your code a LOT more understandable!
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Oh yeah, and you can simplify the various subroutines you have like this:
Private Sub Command40_Click()
Dim stDocName As String
Dim stLinkCriteria As String
On Error GoTo Err_Command40_Click
stDocName = "qwerty"
stLinkCriteria = "[E_REFERENCE]=" & "'" & [Combo41] & "'"
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria
Exit_Command40_Click:
Exit Sub
Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click
End Sub
to just:
Private Sub Command40_Click()
On Error GoTo Err_Command40_Click
DoCmd.OpenForm "qwerty", acFormDS, , "E_REFERENCE = '" & [Combo41] & "'"
Exit Sub
Err_Command40_Click:
MsgBox Err.Description
End Sub
Private Sub Command40_Click()
Dim stDocName As String
Dim stLinkCriteria As String
On Error GoTo Err_Command40_Click
stDocName = "qwerty"
stLinkCriteria = "[E_REFERENCE]=" & "'" & [Combo41] & "'"
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria
Exit_Command40_Click:
Exit Sub
Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click
End Sub
to just:
Private Sub Command40_Click()
On Error GoTo Err_Command40_Click
DoCmd.OpenForm "qwerty", acFormDS, , "E_REFERENCE = '" & [Combo41] & "'"
Exit Sub
Err_Command40_Click:
MsgBox Err.Description
End Sub
There's no need for the explicit '.Value' property references either, since this is the default property for a control.
This means that expressions such as '[Forms]![dataentrytest]![
This means that expressions such as '[Forms]![dataentrytest]![
Hi Jez, thank you for your comments and suggestions. Obv the problem i am having is encorporating link tables so i cannot really create a stand alone version of the database.
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Jez where do i add the 'Option Explicit' statement to a form?
Thanks.
Thanks.
You need to add the 'Option Explicit' statement as the first line in the code for ALL your forms and modules. You can get Access to do this automatically for new code (ONLY) by ticking the 'Require Variable Declaration' option in the dialog box you get when you select Options from the Tools menu in the Microsoft Visual Basic editor window.
As for creating a stand-alone database, you should be able to do this by deleting your linked tables and importing them from your back-end database instead. Assuming this works, please reduce the number of records to a small number and check you have no privacy issues before posting the database!
As for creating a stand-alone database, you should be able to do this by deleting your linked tables and importing them from your back-end database instead. Assuming this works, please reduce the number of records to a small number and check you have no privacy issues before posting the database!
Looking at Module1, I see that your updatecombo() subroutine currently does nothing (and isn't ever invoked). You obviously either need to delete this or supply the content, but if you need to keep it you should explicitly specify whether it is a Public or Prviate procedure.
My aplogies if this is deliberate, but you'd also do well to specify the Application Title by selecting Startup from the Tools menu in the Microsoft Access window - doing this will give the right name in the task bar instead of the generic 'Microsoft Access'.
My aplogies if this is deliberate, but you'd also do well to specify the Application Title by selecting Startup from the Tools menu in the Microsoft Access window - doing this will give the right name in the task bar instead of the generic 'Microsoft Access'.
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
As for creating a stand-alone database, you should be able to do this by deleting your linked tables and importing them from your back-end database instead. Assuming this works, please reduce the number of records to a small number and check you have no privacy issues before posting the database!
I am going to attempt the above. But if i import the backend tables wont they just become local ones and the example will then become different to my own?
I am going to attempt the above. But if i import the backend tables wont they just become local ones and the example will then become different to my own?
Importing the tables instead of using linked tables will indeed change the nature of your application, so you ONLY want to do this in the sample database you're trying to create.
The sample database will however show exactly what you're trying to do - basically, it doesn't matter where the records come from as far as presenting the information on the same form is concerned. To help focus only on the problem at hand please delete the forms, tables and records that do not apply.
The sample database will however show exactly what you're trying to do - basically, it doesn't matter where the records come from as far as presenting the information on the same form is concerned. To help focus only on the problem at hand please delete the forms, tables and records that do not apply.
Ok i will do just that!
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
http://www.area51.getsonmynurbs.com/dean.html
Let me know when you have downloaded it and i can take it down
Let me know when you have downloaded it and i can take it down
Ok, I've downloaded your database - but what happened to paring it down to just your problem form?
By the way, you should replace 'Recordset.Clone' with 'RecordsetClone' - there's no need to clone the form's record set when a clone already exists!
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
This subroutine is wrong too:
Private Sub Combo85_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo85], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
You should have:
Private Sub Combo85_AfterUpdate()
' Find the record that matches the control
RecordsetClone.FindFirst "ID = " & Nz([Combo85], 0)
If Not RecordsetClone.NoMatch Then
Bookmark = RecordsetClone.Bookmark
End If
End Sub
You'll need to ripple this change throughout your database.
Private Sub Combo85_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo85], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
You should have:
Private Sub Combo85_AfterUpdate()
' Find the record that matches the control
RecordsetClone.FindFirst "ID = " & Nz([Combo85], 0)
If Not RecordsetClone.NoMatch Then
Bookmark = RecordsetClone.Bookmark
End If
End Sub
You'll need to ripple this change throughout your database.
Sorry i left some forms required for the 'mainform' to run. Shall i send you another copy?
In your 'klookup' form, where are the 'Combo35', 'Command37', 'Command38', 'Search' and 'cref' controls referenced in the code - or have these disappeared as a result of your 'paring down' process?
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
They should no longer exist, they are from previous (failed attempts) to get the form working
339801 records in your UNIT_CAPD_MODULEENROLMENT table is not really what I was thinking of when I said pare your data down either!
You've also got the following unreferenced controls In your 'mainform' form:
Combo83
Combo85
Combo89
Command145
Combo83
Combo85
Combo89
Command145
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Should be just combo 93 (only combo currently on form). Appologies for the number of records in Moduleenrolement
Mainform is pretty much just going to display results, the only input is going to be the mentor comment at the bottom.
Your 'mainform' form has Combo93 and Command140 on it - so I take it the subroutine Command140_Click IS actually required too?
But what should the Control Source of the following controls be set to, given that the corresponding fields are not returned by the form Record Source:
But what should the Control Source of the following controls be set to, given that the corresponding fields are not returned by the form Record Source:
Form Control
---- -------
fricommentsform Text0
fririskform Combofri
mainform Text75
mainform Text22
mainform Text40
mainform Text42
mainform Text44
mainform Text46
mainform Text146
mainform Text122
moncommentsform Text0
moncommentsform Combo2
monriskform Combo2
qwerty Comments
thucommentsform Text0
thuriskform Combothu
tuecommentsform Text0
tueriskform Combotue
wedcommentsform Text0
wedriskform Combowed
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Also (as far as I can see), the following forms should not have a form header/footer defined, since the corresponding sections are empty:
fri_grade
mon_grade
per_fri
per_mon
per_thu
per_tue
per_wed
querty
risk3
thu_grade
tue_grade
wed_grade
fri_grade
mon_grade
per_fri
per_mon
per_thu
per_tue
per_wed
querty
risk3
thu_grade
tue_grade
wed_grade
'Your 'mainform' form has Combo93 and Command140 on it - so I take it the subroutine Command140_Click IS actually required too?'
That was being used to save a record when i was trying to display the records in continuous form view.
That was being used to save a record when i was trying to display the records in continuous form view.
Ur right that the above forms dont need a header and footer, im not sure where to use a header and footer on a form so they must have been there by default and i didnt remove them.
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Can you explain why so many records of your 'UNITE_T_ATTEND' table have a STUDENT_ID value of 9.9E+14 (628 records)?
Alot of the forms you mentioned above were made for sections of the form that i havent got to creating yet. Anything with the word 'grade' was going to be projected grade. Anything with 'per' is an attendance percentage which was populated by a link table (attendance)
My apologies if I'm bugging you about detail you haven't addressed yet - this is why I was asking for a pared down database.
Do you have any comments about IDs 21245705 & 21245807 above, or are these issues To Be Defined as well?
Do you have any comments about IDs 21245705 & 21245807 above, or are these issues To Be Defined as well?
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Ur not bugging me at all mate, they are all problems that need addressing.
21245807 - No idea. My best guess is that its redundant data from past courses. The UnitE database where that comes from is a huge database/mess that im not currently involved with.
21245735 - See 21245826
21245807 - No idea. My best guess is that its redundant data from past courses. The UnitE database where that comes from is a huge database/mess that im not currently involved with.
21245735 - See 21245826
Jez in this post....21245705 did you manually put that together?
I did indeed assemble the list you mention manually - but please feel free to stop me at any time if I'm wandering too far off on a tangent ...! :-)
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Nah ur alright lol. However....Can you see the problem im having with the KLookup (original problem)
No problem ... actually, I was just about to ask what the Record Source for your klookup form should be set to, to make the form work with your sample data?
Well i dont currently have one, the combobox is populated by the query SELECT qwerty2.E_REFERENCE FROM qwerty2;
Then when the user clicks 'search' then the qwerty2 (i know, i know, NAMING! lol) form appears with the student PSN numbers of students enrolled on that course. The qwerty2 table is the one i wanted to link to the risk2 table
Then when the user clicks 'search' then the qwerty2 (i know, i know, NAMING! lol) form appears with the student PSN numbers of students enrolled on that course. The qwerty2 table is the one i wanted to link to the risk2 table
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Am I right in thinking that your 'qwerty2' query is ONLY used to populate the drop-down values shown by the 'Combo41' combox box on your 'klookup' form?
Presumably this combo box should just be listing unique values, so there's something wrong with the underlying query used as the Control Source?
Presumably this combo box should just be listing unique values, so there's something wrong with the underlying query used as the Control Source?
My apologies, I've just seen that your 'qwerty2' query is used by the 'Combo41' combo box on your 'klookup' form AND as the Record Source for your 'qwerty' form!
Nonetheless, it still seems wrong that your combo box lists repeated values. Any thoughts?
Nonetheless, it still seems wrong that your combo box lists repeated values. Any thoughts?
Sorry if this is going back over old ground, but your 'qwerty2' query does not return an updateable record set either!
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Hmmmm, yes you are right in what you say. I was trying to link the table 'risk2' to that query as well so as i could write comments back to that table. On my drop down the values are unique, actually i did notice that when i imported the tables into the database to send to you that there were duplicates in the dropdown
Yeah the query qwerty2 is broken in terms of linking it to the risk2 table because when i linked the table into the query then the qwerty2 form was only returning a PSN if there was a comment next to it in the table.
Do you reckon that the Control Source for 'Combo41' on your 'klookup' form should be along these lines:
SELECT DISTINCT E_REFERENCE
FROM UNITE_CAPD_MODULEENROLMENT
WHERE E_REFERENCE Is Not Null
SELECT DISTINCT E_REFERENCE
FROM UNITE_CAPD_MODULEENROLMENT
WHERE E_REFERENCE Is Not Null
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Yes i would have thought that would work, are you suggesting i do it that way purely to eliminate duplicates?
Hmmm, that broke the dropdown combo
The keyword DISTINCT means 'only return records which have different values for ALL the fields listed in the SELECT clause'.
Assuming your drop-down should allow the user to pick any distinct non-null E_REFERENCE value from your 'UNITE_CAPD_MODULEENROLMEN
What do you mean when you say changing the Control Source 'broke the drop-down combo'? I realise it's not very helpful but it drops down fine for me!
Assuming your drop-down should allow the user to pick any distinct non-null E_REFERENCE value from your 'UNITE_CAPD_MODULEENROLMEN
What do you mean when you say changing the Control Source 'broke the drop-down combo'? I realise it's not very helpful but it drops down fine for me!
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
When i changed the control source and used the query in ID 21246418 then the dropdown contained no records
Ive got to leave the office now. Will be working on this tomorrow all day (as i seem to everyday). Thanks for your help today. I may take the copy of the database home that I sent you and try working on a few things.
It's just an educated guess, but try putting the ROW SOURCE (PLEASE NOTE I previously said CONTROL SOURCE by mistake) on one line like this (use cut and paste to be 100% sure):
SELECT DISTINCT E_REFERENCE FROM UNITE_CAPD_MODULEENROLMENT
SELECT DISTINCT E_REFERENCE FROM UNITE_CAPD_MODULEENROLMENT
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
It seems to me that the crux of the problem lies with your 'qwerty2' query - it joins records from your 'risk2' table to your various 'UNITE_...' tables but doesn't specify the join criteria to do so.
Do you think you may have the wrong end of the stick as to how joins work? Would I a quick recap (particularly regarding catersain-product joins) be worthwhile?
Do you think you may have the wrong end of the stick as to how joins work? Would I a quick recap (particularly regarding catersain-product joins) be worthwhile?
In answer to your last post, in regard to joining tables im just self taught so i expect there are errors there and i have never even heard of catersain-product joins im affraid. I agree with you that joining risk2 to ANY unite table is very much a problem
Just as a side question.... Would you suggest ADO's 'Add' function to copy the contents of one complete column from one table to another? (im not going to have repeating data as i will delete the original column).
Thanks.
Thanks.
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
A Cartesian-product join is what you get when you have multiple COMMA-SEPARATED tables (or queries) in your FROM clause, like this:
SELECT *
FROM Table1, Table2
The above example returns EVERY record in Table1 joined to EVERY record in Table2. The number of records returned is therefore the number of records in Table1 multiplied by the number of records in Table2 - hence the word 'product' in the name.
Using this form of query you specify the join criteria via the WHERE clause, like this:
SELECT *
FROM Table1, Table2
WHERE Table1.CommonID = Table2.CommonID
In this latter case, the WHERE clause effectively determines whether a particular Table1/Table2 record combination corresponds to a pair of related records in the two tables. This is precisely the information missing from your 'qwerty2' query - so EVERY combination is returned, hence the multiplicity of records you observe.
Bear in mind also that the records returned by a Cartesian-product join are not updateable - which explains why you can't edit the data.
SELECT *
FROM Table1, Table2
The above example returns EVERY record in Table1 joined to EVERY record in Table2. The number of records returned is therefore the number of records in Table1 multiplied by the number of records in Table2 - hence the word 'product' in the name.
Using this form of query you specify the join criteria via the WHERE clause, like this:
SELECT *
FROM Table1, Table2
WHERE Table1.CommonID = Table2.CommonID
In this latter case, the WHERE clause effectively determines whether a particular Table1/Table2 record combination corresponds to a pair of related records in the two tables. This is precisely the information missing from your 'qwerty2' query - so EVERY combination is returned, hence the multiplicity of records you observe.
Bear in mind also that the records returned by a Cartesian-product join are not updateable - which explains why you can't edit the data.
Before we resolve this problem you'll have to describe the relationships (many-to-one, one-to-one or one-to-many) between ALL the tables/queries you currently have in your 'qwerty2' query.
You'll also have to explain the uniqueness of each table/query - it seems highly suspiciopus to me that there aren't ANY indexes defined in these tables at present.
You'll also have to explain the uniqueness of each table/query - it seems highly suspiciopus to me that there aren't ANY indexes defined in these tables at present.
Was your above comment in relation to post ID: 21252074?
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Ok im with you now. Its not relating to ID21252074 so lets ignore that post for now.
Now all of the links i have made in queries is just by dragging and dropping betwwen the records which im guessing by fefault makes a 1-1 relationship? Im not sure how to change this to 1-many for example.
Now all of the links i have made in queries is just by dragging and dropping betwwen the records which im guessing by fefault makes a 1-1 relationship? Im not sure how to change this to 1-many for example.
You need to forget about the query builder for now and go right back to basics.
Consider ONE record from your 'risk2' table; does it relate to no records, one record or many records in EACH of your 'UNITE_...' tables? You also need to establish whether EACH relationship is mandatory or optional.
When you've answered these two questions for your 'risk2', repeat the process for the three 'UNITE_...' tables in turn - and post your answers here!
Consider ONE record from your 'risk2' table; does it relate to no records, one record or many records in EACH of your 'UNITE_...' tables? You also need to establish whether EACH relationship is mandatory or optional.
When you've answered these two questions for your 'risk2', repeat the process for the three 'UNITE_...' tables in turn - and post your answers here!
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
For each relationship between a given pair of tables you should also establish the fields in the two tables that contain matching data - this is the join criteria to use.
Lastly, you need to analyse the 'risk2' table and each of the 'UNITE_...' tables to determine for EACH table what unique indexes should be defined. Currently you have NO unique indexes, whIch (almost certainly) incorrectly allows you to have any number of duplicate records in your tables.
Bear in mind that a table can have many unique indexes (not just the primary key) and that each unique index can be composed of a COMBINATION of several fields.
Bear in mind that a table can have many unique indexes (not just the primary key) and that each unique index can be composed of a COMBINATION of several fields.
Now would also be a good time to check for suspicious data, such as the fishy 'E_STUDENT' values in your 'UNITE_CAPD_MODULEENROLMEN
You need to figure out how you want to handle these dodgy records too. For example, you could decide to set invalid values to Null, you could correct the data (if you can actually determine how to do this), perhaps you should delete all records containing invalid data or maybe you just want to ignore all unintelligible data?
You need to figure out how you want to handle these dodgy records too. For example, you could decide to set invalid values to Null, you could correct the data (if you can actually determine how to do this), perhaps you should delete all records containing invalid data or maybe you just want to ignore all unintelligible data?
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Morning Jez,
I will take all of your above advice and post the results. When you say 'NO unique indexes,' you are referring to primary keys yes? Almost all the Unite tables have primary keys, i have realised that my risk2 table NOW does not have a primary key, i did originally have one but during the ongoing process of trying to get the databas working they seem to have been deleted, i realise the importance of having a primary key so im sure i didnt delete them on purpose but hey!
Thanks again.
I will take all of your above advice and post the results. When you say 'NO unique indexes,' you are referring to primary keys yes? Almost all the Unite tables have primary keys, i have realised that my risk2 table NOW does not have a primary key, i did originally have one but during the ongoing process of trying to get the databas working they seem to have been deleted, i realise the importance of having a primary key so im sure i didnt delete them on purpose but hey!
Thanks again.
Appologies, just read ID: 21255509 regarding primary keys.
Jez, does this make things clearer?.....
Relationships.doc
Relationships.doc
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
It's a good start, but you need to identifiy the fields (or combination of fields) that are involved in BOTH tables of EACH relationship too. You also need to establish ALL the unique indexes for EACH table (see ID 21255509 above).
Once you've done this, you really should enforce integrity by defining the indexes and relationships in the underlying tables - unless you do this, it will be possible to create 'orphaned' or duplicate records.
Once you've done this, you really should enforce integrity by defining the indexes and relationships in the underlying tables - unless you do this, it will be possible to create 'orphaned' or duplicate records.
By the way, why are the 'Level Of Risk' values in your 'risk2' table sometimes textual and sometimes numeric?
As well, it's a LOT easier to query data if you set the 'Allow Zero Length' flag of all your text fields to 'No' and exclusively use Null to represent missing data - otherwise you have to worry about distinguishing Null, empty string and a string of spaces, which almost certainly ALL actually indicate 'no data'.
As well, it's a LOT easier to query data if you set the 'Allow Zero Length' flag of all your text fields to 'No' and exclusively use Null to represent missing data - otherwise you have to worry about distinguishing Null, empty string and a string of spaces, which almost certainly ALL actually indicate 'no data'.
So how come (in your first figure) you're saying that ONE UNITE_CAPD_STUDENT record MUST have MANY (well, at least one anyway) UNITE_T_ATTEND_ALL record?
This doesn't make sense to me, when there are 59931 UNITE_CAPD_STUDENT records but ony 5038 UNITE_T_ATTEND_ALL records in your sample database!
This doesn't make sense to me, when there are 59931 UNITE_CAPD_STUDENT records but ony 5038 UNITE_T_ATTEND_ALL records in your sample database!
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Will ammend/add to diagram this afternoon after lunch.
In answer to ID: 21261805, the Student record with 59931 has not been filtered to A level students only if that makes sense?
The relationships you define need to be UNIVERSALLY true - whether you're considering A-level students or not. This may mean that relationships you previously specified as mandatory are in fact optional.
It might be a result of your import process, but you seem to have a LOT of garbage data in your tables - unless you have students who live at '9.99000011463742E+14'?! :-)
It might be a result of your import process, but you seem to have a LOT of garbage data in your tables - unless you have students who live at '9.99000011463742E+14'?! :-)
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
When you're working out the relationships for a given table, you need to take the point of view of any ONE arbitrary record and consider how many other records relate to it in each of your OTHER tables (and whether the relationship is optional or mandatary).
The approach you take needs to consider ACTUAL (not IDEAL) data, In other words, if a particular relationship "should be mandatory" but the data indicates that related records are not always specified in reality, then the relationship is optional.
The approach you take needs to consider ACTUAL (not IDEAL) data, In other words, if a particular relationship "should be mandatory" but the data indicates that related records are not always specified in reality, then the relationship is optional.
The relationships should also take account of your garbage data - unless you're going to tidy this up first?
Only specify DIRECT relationships!
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
I will leave the garbage data as it is i think. It was not the importing of the tables that has created this, those garbage records really do exist in the unite database. The database is in a mess but its outside the scope of the current project to sort it all out.
I will take your points on board and redo it.
Many Thanks.
I will take your points on board and redo it.
Many Thanks.
This is proving tricky, from the data you have does it really seem possible to achieve what we are trying to achieve? there doesnt seem to be too many good joins between manyof the tables using keys.
Bear in mind that you can always change the tables themselves to add fields (or records) - unless their design is set in stone (so to speak)?
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Are you referring to unite tables? they are set in stone and have been for a long time a believe
Ive just realised that we can remove the UNITE_T_ATTEND_ALL all this produces is the attendance % which can be a lookup and doesnt need to be part of the query and doesnt even need to be stored.
I think im loseing it. We need qwerty2 to store comments in risk 2 and populate the S_STUDENTREFERENCE field depending on the course selection dropdown on form klookup
I think im loseing it. We need qwerty2 to store comments in risk 2 and populate the S_STUDENTREFERENCE field depending on the course selection dropdown on form klookup
I'm afraid I'm completely lost as to what you're actually trying to achieve!
Perhaps we should start again from the beginning?
Perhaps we should start again from the beginning?
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
The whole idea of specifying the relationships and indexes was to clarify (at least to me) the 'real-world' meaning of the data held in each table and how that data is divided up.
Please don't take this the wrong way, but so far I'm none the wiser as to this information!
Please don't take this the wrong way, but so far I'm none the wiser as to this information!
So where should 'Combo41' on your 'klookup' form get the set of course selections from - is this just the distinct 'E_REFERENCE' field values in your 'UNITE_CAPD_MODULEENROLMEN
The 'UNITE_CAPD_MODULEENROLMEN
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Your 'UNITE_T_ATTEND_ALL' table contains 5038 records, all of which have related records in your 'UNITE_CAPD_STUDENT' table - which contains 59931 records.
If you don't join to the 'UNIT_T_ATTEND_ALL' table in your 'qwerty2' query you'll return a LOT more records.
Is this REALLY what you want to do?
If you don't join to the 'UNIT_T_ATTEND_ALL' table in your 'qwerty2' query you'll return a LOT more records.
Is this REALLY what you want to do?
So where should your 'CourseYRClass' query fit in - and what is its purpose?
Morning Jez, ok here we go with some answers, i will try to keep them as simple as possible as i could possibly be confusing myself. The trouble is im obv coming into this with limited experience and the unite database is a bit messy to say the least.
==========================
'So where should 'Combo41' on your 'klookup' form get the set of course selections from - is this just the distinct 'E_REFERENCE' field values in your 'UNITE_CAPD_MODULEENROLMEN
The combo is getting the E_REFERENCE fields from the query qwerty2 as this filters in the A Level students only hence the like AS* and A2* in the criteria.
==========================
The 'UNITE_CAPD_MODULEENROLMEN
Yes im going to have to as there is no way of linking them so i assume its old data.
==========================
Your 'UNITE_T_ATTEND_ALL' table contains 5038 records, all of which have related records in your 'UNITE_CAPD_STUDENT' table - which contains 59931 records.
If you don't join to the 'UNIT_T_ATTEND_ALL' table in your 'qwerty2' query you'll return a LOT more records.
Is this REALLY what you want to do?.....
It is a good idea to join this table into the query. I cannot see how the table was created (and the unite 'expert' guy that created it only works mon & tue) but it was created recently and i think it was created for the surpose of this database.
==========================
So where should your 'CourseYRClass' query fit in - and what is its purpose?...
When a lecturer selected a class in the dropdown some of the students were appearing multiple times (this unite expert we have hear 2 days a week) said its to do with the way the database works and added that table for me and said it will now only return each student once, it's something to do with tables between tables in unite.
Is that any clearer?
Many Thanks.
==========================
'So where should 'Combo41' on your 'klookup' form get the set of course selections from - is this just the distinct 'E_REFERENCE' field values in your 'UNITE_CAPD_MODULEENROLMEN
The combo is getting the E_REFERENCE fields from the query qwerty2 as this filters in the A Level students only hence the like AS* and A2* in the criteria.
==========================
The 'UNITE_CAPD_MODULEENROLMEN
Yes im going to have to as there is no way of linking them so i assume its old data.
==========================
Your 'UNITE_T_ATTEND_ALL' table contains 5038 records, all of which have related records in your 'UNITE_CAPD_STUDENT' table - which contains 59931 records.
If you don't join to the 'UNIT_T_ATTEND_ALL' table in your 'qwerty2' query you'll return a LOT more records.
Is this REALLY what you want to do?.....
It is a good idea to join this table into the query. I cannot see how the table was created (and the unite 'expert' guy that created it only works mon & tue) but it was created recently and i think it was created for the surpose of this database.
==========================
So where should your 'CourseYRClass' query fit in - and what is its purpose?...
When a lecturer selected a class in the dropdown some of the students were appearing multiple times (this unite expert we have hear 2 days a week) said its to do with the way the database works and added that table for me and said it will now only return each student once, it's something to do with tables between tables in unite.
Is that any clearer?
Many Thanks.
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Update....
situation.doc
situation.doc
Right, let's start from the beginning and go ONE question at a time. First of all, imagine you have absolutely NO queries in your database WHATSOEVER - just tables. From this point of view, where does the set of class codes you want to select from get populated from?
Initially answer this question in straight English-language terms ("I get the set of class codes from the ... information") - this should express your requirements in understandable, REAL-WORLD terms. Next, translate this into field/table equivalents ("The ... information is determined from the ... field of the ... table, where .. field values also exist in the ... table ...").
I will wait for your answer before proceeding further.
Initially answer this question in straight English-language terms ("I get the set of class codes from the ... information") - this should express your requirements in understandable, REAL-WORLD terms. Next, translate this into field/table equivalents ("The ... information is determined from the ... field of the ... table, where .. field values also exist in the ... table ...").
I will wait for your answer before proceeding further.
Actually ive just changed the testa query. The relationship from the students table to risk2 is now one to many, i have set recordset type to dynaset with inconsistant updates for the testa query and the qwerty2 form and it ALL works fine!!!!
Essentially i split the query.
Thanks for your unlimited patience and help with this matter. It has been very much appreciated. Im unsure about this whole points system, shall i just click 'accepted solution' on your last post, as u deserve them.
Essentially i split the query.
Thanks for your unlimited patience and help with this matter. It has been very much appreciated. Im unsure about this whole points system, shall i just click 'accepted solution' on your last post, as u deserve them.
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
I reckon you should accept ID 21231342, as (I think) this answers your original question.
Great help. Thanks alot.
Ok no problem.
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Microsoft Access
--
Questions
--
Followers
Top Experts
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.