Query Expressions

[g diddy]

Hi there!

I have a query which when run shows all the special needs records. This query gets its information from a linked ODBC table. I have in the database a seperate table (special needs students) which contains similar information but only for the current academic year (which is then used for various other functions). What I want to be able to do is have in the query some sort of expression that will insert the text "exists in database" into a new field of the query if the record in the link table is already in the table special needs students; and if not, leaving the field blank. I've tried using iif and other things but i'm not very good at it so keep getting syntax errors.

I'm just wondering if this is possible and if so what kind of expression do you use?

If you need any more info let me know.

Help greatly appreciated!

[ChipR]

Create a query that will show all rows from the link table joined with matching rows from the table [special needs students]. Include a field from [special needs students] in your query, and if there isn't a matching record, that field will be null for that record. Now you can create a new column with:

IIf(IsNull([special needs students].whateverField, "", "exists in database")

[g diddy]

thanks for the quick response! i get the error the expression contains the wrong number of arguments. The link table has more fields than the special needs students one. sorry i didn't realise. From what I know about databases I think that means it's not possible as they both need the same number of fields to work. Thank you anyway for your help! Much appreciated

[NeoPa]

The answer Chip supplied is pretty much correct (If I were being picky I may mention the blank value is normally specified as Null instead of ""). If it's not working for you I suspect you may have transcribed it with an error.

Why don't you post what you have and we'll see if we can spot the problem.

[g diddy]

OK thanks NeoPa! I currently have 3 tables that are being used for this query. The link table EXTTSTUDSPECNEED (contains 11 fields), the link table EXTSTUDUNIT (contains 7 fields) and the table SpecialNeedsStudents (contains 13 fields). All 3 are linked together via StudentID. I have 5 fields that the query is picking out: Name, Course and Notes (from SpecialNeedsStudents) StudentID from EXTTSTUDSPECNEED and UNITCODE from EXTSTUDUNIT. Without adding in the IIF statement it works correctly and brings up all the disability students. The IIF statement I used was:

IIf(IsNull([SpecialNeedsStudents].Here, "", "exists in database")

"Here" is a new field I added to the SpecialNeedsStudents table especially for this query. So all the values in the table are blank (for this field) with the hope that this IIF statement will populate the query field for those students who are in this table and the link table for use in a report.

I hope that helps. Really appreciate the help!

[NeoPa]

This all sounds very wrong. You cannot update the underlying fields from within a SELECT query.

This posted code is too isolated to tell us much. It is the rest of the SQL that gives the context within which this code can (but maybe doesn't) make sense. Can you post all of the SQL you are using please.

[g diddy]

Certainly, I hope this is what you mean. If not let me know.

Normal query on Access 2003 with the following:

Field: Name
Table: SpecialNeedsStudents
Sort: Ascending
 
Field: STUDENTID
Table: EXTTSTUDSPECNEED
 
Field: COURSE
Table: SpecialNeedsStudents
 
Field: UNITCODE
Table: EXTTSTUDUNIT
Criteria: [Forms]![SelectModule]![SelectModule]
 
Field: NOTES
Table: SpecialNeedsStudents
 
Field: IIf(IsNull([SpecialNeedsStudents].Here, "", "exists in database") 
 

There are 3 tables (2 link tables, 1 normal) all linked to each other by STUDENTID. The table SpecialNeedsStudents has many fields in the field list but most importantly for this query: STUDENTID, NAME, COURSE, NOTES and Here.

Does this help at all? Thanks for your time!

[NeoPa]

@g diddy
No, but the following may make it clearer and easier for you to post here in future.

To extract the SQL from a QueryDef (saved Access query) it is only necessary to view the query in SQL View. This shows the underlying SQL for the QueryDef and is text (See Access QueryDefs Mis-save Subquery SQL for problems with SubQueries). When a QueryDef is open (either in Design View or Datasheet View) it is possible to switch to the SQL View simply by selecting View \ SQL View from the menu.

From here it is simple to Copy & Paste it to wherever you need it.

[g diddy]

my apologies! Thanks for that!

Here is the code in sql view

SELECT SpecialNeedsStudents.NAME, EXTTSTUDSPECNEED.STUDENTID, SpecialNeedsStudents.COURSE, EXTTSTUDUNIT.UNITCODE, SpecialNeedsStudents.NOTES
FROM (SpecialNeedsStudents INNER JOIN EXTTSTUDUNIT ON SpecialNeedsStudents.STUDENTID = EXTTSTUDUNIT.STUDENTID) INNER JOIN EXTTSTUDSPECNEED ON (SpecialNeedsStudents.STUDENTID = EXTTSTUDSPECNEED.STUDENTID) AND (EXTTSTUDUNIT.STUDENTID = EXTTSTUDSPECNEED.STUDENTID)
WHERE (((EXTTSTUDUNIT.UNITCODE)=[Forms]![SelectModule]![SelectModule]))
ORDER BY SpecialNeedsStudents.NAME;
 

[NeoPa]

I think you need to post the one with your IIf() function call in it. That will give us a clearer idea of what you're attempting to do. The existing SQL has no reference to [SpecialNeedsStudents].Here.

You should understand that your ealier comment related to updating this field is not even in the right ballpark. This is clearly a SELECT query. Updates cannot be done in such a query. You can however, display a calculated value in a SELECT query. We can still look at that.

I feel we're going beyond what we know here without the relevant SQL posted. Let's see that first before we try to proceed.

[g diddy]

My apologies again! It won't let me view as sql view because I keep getting the error:

The expression you entered has a function containing the wrong number of arguments.

The expression being:

IIf(IsNull([SpecialNeedsStudents].Here, "", "exists in database")

The sql code I posted before is from the only other things in the query. I have a feeling i'm trying to do something that's not possible and/or i'm going about it entirely wrongly. Is there an alternative method to what i'm doing? My expressions skills are, at most, useless so I may be overly complicating something that could in fact be simple.

I'll try and clarify what i'm trying to do because I realise myself that I am being very confusing! I have a query which picks out information from 3 tables. 2 of the tables are link (ODBC) tables and one is not (SpecialNeedsStudents). I want the query to bring up the results and then add a column into the query (an expression) showing "exists in database" if any of the records found exist in the 2 link tables but not in the SpecialNeedsStudents table. Based upon the sql code I posted before (which is all the other columns of the query), am I going about this all wrongly? (if so I apologise for wasting your time and misleading you!!)

[JennDub]

g diddy,
I think that if you change your IIF statement to read:

IIf(IsNull([SpecialNeedsStudents].Here), "", "exists in database")

the "wrong number of arguments" error will be resolved. When you started your "IsNull" criteria you began with a "(" but did not include an ending ")" after the field name. Once th IIF statement syntax is correct, you should be able to display the calculated field in a Select query or use it to update the table via an Append or Make Table query.

~JennDub

[g diddy]

JennDub you legend thank you very much for spotting that.

Here is the sql view code:

SELECT SpecialNeedsStudents.NAME, EXTTSTUDSPECNEED.STUDENTID, SpecialNeedsStudents.COURSE, EXTTSTUDUNIT.UNITCODE, SpecialNeedsStudents.NOTES, IIf(IsNull([SpecialNeedsStudents].[Here]),"","exists in database") AS Expr1
FROM (SpecialNeedsStudents INNER JOIN EXTTSTUDUNIT ON SpecialNeedsStudents.STUDENTID = EXTTSTUDUNIT.STUDENTID) INNER JOIN EXTTSTUDSPECNEED ON (EXTTSTUDUNIT.STUDENTID = EXTTSTUDSPECNEED.STUDENTID) AND (SpecialNeedsStudents.STUDENTID = EXTTSTUDSPECNEED.STUDENTID)
ORDER BY SpecialNeedsStudents.NAME;

I'm not sure if i'm going about this right tho. I've added into the table SpecialNeedsStudents for that field (Here) "Exists in Database" as all the records were coming up with a blank field otherwise (- was the iif expression supposed to insert that text if they did exist?). At the moment all the students exist in the database; but if a student record exists in the link ODBC tables but not in the SpecialNeedsStudents table will this work? I don't fully understand all the joins etc so any clafication would be very gratefull.

Thank you for all your time and effort, really appreciate it!

[ChipR]

Sorry about that missing parenthesis.
You shouldn't be creating a field in the table for this purpose. It should just be a calculated field in a query based on what you already have in the table, as I explained in post #2. You can see the join type by clicking on the line between the ID field of the 2 tables in query design view, and you can change it from Inner Join to Outer Join.

[JennDub]

g diddy,
I think you're referencing the wrong field in your IIF statement. If you would like find out whether the data in one table (StudentID) exists in the other and display them as the calculated field named "Here", you might want to try to replace your IIF statement in the SQL with:

IIf(IsNull([SpecialNeedsStudents].STUDENTID), "", "exists in database") AS Here

From your first post, it sounded like you were trying to find out if the Student ID already existed in the database using all the records from the EXTSTUDUNIT table and those that matched in the SpecialNeedsStudent table. If so, you'll need to do a join in your query pulling all records from EXTSTUDUNIT and/or EXTSTUDSPECNEED and those that match from SpecialNeedsStudents. This will allow you to use whether or not the StudentID field is populated to determine if the data already exists in the query.

~JennDub

[NeoPa]

Well, I have to say Welcome to Bytes! JennDubb. You seem to be just starting as I watch (I refreshed and you posted again - just your second post to date).

Now I can go on to reading what you said ;)

[NeoPa]

Jenn is absolutely right here, but Chip has also made an important point. The table you're checking is linked with an INNER JOIN. This means that only records where one exists will be shown anyway. Does that make sense? Is it starting to become clearer or are you still struggling?

[g diddy]

That makes perfect sense. Thank you ChipR, JennDub and NeoPa for all your help. I learnt a lot too. thank you for your time and effort :D

[NeoPa]

Good for you :)

It's always good when members manage to learn something and make progress. Very gratifying.