Search code examples
sqlms-access-2003

conditional criteria in access 2003

I am fairly new to Access 2003, and I've been having some issues with what I think is called an inner join. I have two tables that I have no ability to edit, so I am querying them and the information in one of the two tables relates to profiles of users and the second is auxilary information relating to them like email addresses.

My issue is that I need to apply criteria to table 2 (auxilary), but sometimes the record doesn't exist because not all of table 1 has a match in table 2. So I have selected to preserve all fields from table 1 and only matching for table 2, but due to my criteria for table 2, I will never get a result unless there is values in table 2.

I have a field in table 1 that lets me know if there will be values in table 2, so what I want to do is some sort of if table1val = "1" Then table2val Like "*01*". So far this has not worked, likely due to error on my part, so I was wondering if this is possible in design view, because from looking it up it seems like it may be possible in sql view, but I've had no luck attempting this due to my lack of sql knowledge.

edit: what I had that was working criteria for table2:

under note_txt: Like "*help*" Or Like "*HELP*" Or Like "*Help*"

under category_code: "Emails" Or "Email"

under high_priority_ind: Like "-1"

These all work, but will return nothing if the field shows #Deleted

the field in table1 I would like to have these criteria be based on is when prod_marker: Not Like "0"

My understanding is you cannot have an if in a criteria because say if you put "1" as the criteria for table 1 it acts like table1 = 1, and you cant have table1 = if. So is there a better way of going about this, because it seems I cant have 

If(Not (prod_marker) Like "0") Then ((high_priority_ind) Like "-1")

Below is the SQL that I've been able to come up with that just crashes my access (I didnt include the SELECT and FROM because its long and I wasnt sure if it was necessary):

WHERE (((dbo_client_link.link_id_txt)=[Enter Policy Number]) AND
 ((dbo_client_link.link_relationship_code)="O" Or 
(dbo_client_link.link_relationship_code)="J") AND 
((dbo_producer.aga_direct_ind)  Like "0") Or 
(((dbo_producer.aga_direct_ind) Like "-1" ) And 
( (dbo_note.note_txt) Like "*help*" Or 
(dbo_note.note_txt) Like "*HELP*") AND 
((dbo_note.category_code)="Emails" Or 
(dbo_note.category_code)="Email") AND 
((dbo_note.high_priority_ind) Like -1)))
Solution

  • My issue is that I need to apply criteria to table 2 (auxilary), but sometimes the record doesn't exist because not all of table 1 has a match in table 2.

    You need to use a left join from Table1 (main) to Table2 (auxillary). If you're doing this in Design View, you should be able to right-click the join (line between the 2 tables), then go to Join Properties. You want to select Include all records from Table1 and only those records from Table2 where the joined fields are equal

    Now you can use the Table1.prod_marker <> "0" expression as a criterion (you don't need the LIKE operator, since you're not using a wildcard search). Going along these lines: What types of values are in the prod_marker field? Is it only "0" and "1", or do you have other possible values? If you're only ever going to see either "0" or "1", you can also use Table1.prod_marker = 1

    If you also want to filter records based on values in Table2, you can add OR criteria (like high_priority_ind = "-1", UCASE(note_txt) like "*HELP*", and UCASE(category_code) like "*EMAIL*"

    Something like this: enter image description here