Dlookup problem

Ok ive found the Student references in the table UNITE_CAPD_MODULEENROLMENT so i have the following DLookup...

=DLookUp("E_ID","UNITE_CAPD_MODULEENROLMENT","E_REFERENCE=" & [Combo41])

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 table?

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_MODULEENROLMENT", "E_REFERENCE = """ & [Combo41] & """")

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'.

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 needs to be equal to the value being shown in dropdown box on the form (visible in the print screen)<<<<<<

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 table = the value of Combo41 on the klookup form.  If this correct then the solution above should work.

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.

>>>>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

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_MODULEENROLMENT", "E_REFERENCE = """ & [Combo41] & """")

and just insert '[Forms]![klookup]![Combo41]' ?

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 table = the value of Combo41 on the klookup form.  If this correct then the solution above should work.'

Are you trying to get a QUERY to work or a FORM?

Form

Have you tried my solution???

On the Criteria line for the E_REFERENCE field just replace the  Like "7*"  with   [Forms]![klookup]![Combo41]   and run your query.

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

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?

etsherman, i tried your suggestion but it breaks the dropdown box.

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 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

21229873 - They are names correctly

21229892 - #Name?

Try temporarily simplifying the expression to just:

    =DLookup("E_ID", "UNITE_CAPD_MODULEENROLMENT")

This won't give you the right ID value, but at least it will tell you whether E_ID.UNITE_CAPD_MODULEENROLMENT exists.

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

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?

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_MODULEENROLMENT'.

Now change the Control Source tto:

    =DLookUp("E_ID", "UNITE_CAPD_MODULEENROLMENT","E_REFERENCE Is Not Null")

It now has all the text22 fields set to  12388132

So that confirms that the field 'E_REFERENCE' DOES exist in the table 'UNITE_CAPD_MODULEENROLMENT'.

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

Forms]![klookup]![Combo41]

Seems to have done the trick.

Or, if Combo41 is bound to a numeric value this will be:

    =DLookup("E_ID", "UNITE_CAPD_MODULEENROLMENT", "E_REFERENCE = " & [Forms]![klookup].[Combo41])

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

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.

I got [Forms]![klookup]![Combo41] from ID:21230368

Appologies, i meant to say the forms record source. You are correct in what you say, how can i get around this?

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!

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?

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?

Hmmm ok. Appologies, E_REFERENCE comes from the table UNITE_CAPD_MODULEENROLMENT. What would be the best way to join them as they have no similar fields to link...
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

The result is now...
printscreen.JPG

I take it your Record Source is now something like:

    SELECT *
    FROM risk2 JOIN UNITE_CAPD_MODULEENROLMENT
    ON UNITE_CAPD_MODULEENROLMENT.E_ID = risk2.PSN

Bear in mind that your form will then only display data where corresponding records exist in BOTH tables.

How about that sample database then?

Record Source is.....

SELECT UNITE_CAPD_MODULEENROLMENT.E_REFERENCE
FROM risk2 INNER JOIN UNITE_CAPD_MODULEENROLMENT ON risk2.PSN = UNITE_CAPD_MODULEENROLMENT.E_ID;

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.

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

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

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!

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 query?

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?

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?

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.

Morning Jez, here is the database...

Hmmm, hasnt attached its a rar file with a .txt extension

This link will work...

http://www.area51.getsonmynurbs.com/dean.html

Jez, I have now managed to link my query to my local table which is to be used for storing

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?

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

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!

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

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]![PSN].Value' simplifiy to just '[Forms]![dataentrytest].[PSN]'.

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.

Jez where do i add the 'Option Explicit' statement to a form?

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!

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'.

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?

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.

Ok i will do just that!

http://www.area51.getsonmynurbs.com/dean.html

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!

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.

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?

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

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:

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

Select allOpen in new window

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

'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.

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.

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?

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

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 ...!  :-)

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

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?

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?

Sorry if this is going back over old ground, but your 'qwerty2' query does not return an updateable record set either!

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

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_MODULEENROLMENT' table, then the query in ID 21246418 above is what you need.

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!

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 WHERE E_REFERENCE Is Not Null

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?

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.

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.

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.

Was your above comment in relation to post ID: 21252074?

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.

The qwerty2 Query
query.JPG

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!

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.

Now would also be a good time to check for suspicious data, such as the fishy 'E_STUDENT' values in your 'UNITE_CAPD_MODULEENROLMENT' table, as well as any 'orphaned' data ('child' records without any corresponding 'parent' record).

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?

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.

Appologies, just read ID: 21255509 regarding primary keys.

Jez, does this make things clearer?.....
Relationships.doc

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.

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'.

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!

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'?!  :-)

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 relationships should also take account of your garbage data - unless you're going to tidy this up first?

Only specify DIRECT relationships!

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.

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)?

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'm afraid I'm completely lost as to what you're actually trying to achieve!

Perhaps we should start again from the beginning?

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!

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_MODULEENROLMENT' table?

The 'UNITE_CAPD_MODULEENROLMENT' table in your sample database has 13609 records with Null 'E_REFERENCE' field values, so I guess you want to ignore these?

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?

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_MODULEENROLMENT' table?'...

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_MODULEENROLMENT' table in your sample database has 13609 records with Null 'E_REFERENCE' field values, so I guess you want to ignore these?....

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.

Update....
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.

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.

I reckon you should accept ID 21231342, as (I think) this answers your original question.

Great help. Thanks alot.

Ok no problem.