Join Properties

[jereece]

I have a query that has 2 linked tables in it. I have the Joined Properties set to include all records from table 1 and only those records from table 2 where the joined fields are equal. To validate my query is working, I picked a query that would pull 3 records from Table 1 when there are no criteria in Table 2. However when I add some criteria for a field in table 2 I get no results. I expected to get the 3 records from table 1 and the field in table 2 to be empty since there is no data from that table that matches the query. Is this normal? It's not what I expected.


Thanks,
Jim

 

[jereece]

As a follow-up, if what I asked above is normal as I suspect, how can I write a query to look for records in table 1 that do not have a certain criteria in table 2 for that corresponding record. For example lets say I have the following in my query

TABLE 1
YR
ID#
Type


TABLE 2
YR
ID#
Priority_Cd

Obviously the YR and ID# fields would be joined properties. What I want to know is, of all the records in Table 1 where the Type is "-1", how many records only have a 3 in the Priority_Cd field of table 2. There may be several records for each issue (YR and ID#) but I want to know how many only have Priority_Cd = "3".

Hope this makes sense.

Thanks,
Jim

 

[tehNellie]

I have a query that has 2 linked tables in it. I have the Joined Properties set to include all records from table 1 and only those records from table 2 where the joined fields are equal. To validate my query is working, I picked a query that would pull 3 records from Table 1 when there are no criteria in Table 2. However when I add some criteria for a field in table 2 I get no results. I expected to get the 3 records from table 1 and the field in table 2 to be empty since there is no data from that table that matches the query. Is this normal? It's not what I expected.


Thanks,
Jim

If you put criteria for table2 then yes. There are no matching records in Table2 so you get 0 rows. You could use an OR statement in your query:

WHERE table2.criteria = [i]something[/i] OR table2.criteria  IS NULL

Then you'll still get your three records (table2.criteria IS NULL) where there are no records in table2, plus any additional records where there is a match based on the Table2.criteria.

Obviously the YR and ID# fields would be joined properties. What I want to know is, of all the records in Table 1 where the Type is "-1", how many records only have a 3 in the Priority_Cd field of table 2. There may be several records for each issue (YR and ID#) but I want to know how many only have Priority_Cd = "3"..

SELECT tbl1.yr, tbl1.ID, COUNT(tbl2.priority_CD) as countofRecords
INNER JOIN tbl2 ON
  tbl2.ID = tbl1.ID
  AND tbl2.YR = tbl1.YR
WHERE tbl1.type = '-1' AND tbl2.priority_CD = 3
GROUP BY tbl1.yr, tbl1.ID

 

[jereece]

Thanks tehNellie.

In the criteria field for my table 2 item if I use

Like "*1*" or Like "*2*" or is Null

I still get no results. If I leave it blank I get results that are all "3". Again what I am looking for is how many in Table 1 have no 1 or 2 [Priority_Cd] from table 2.

As for your second code, I am unsure how to do this. I am not good at manual coding for queries. Can you tell me what to put in the Criteria field of the query or explain where to put your code?

I appreciate the help.

Jim

 

[cpberg1]

Jim,

The code (or pieces of it) can be pasted into the SQL view. From your query in design view go to the view menu and select SQL view.

You'll see code similar to the one posted above. You can edit, amend and or copy paste directly there.

 

[peregrin]

how many records only have a 3 in the Priority_Cd field of table 2

I still get no results. If I leave it blank I get results that are all "3". Again what I am looking for is how many in Table 1 have no 1 or 2 [Priority_Cd] from table 2.

Do you want a 3 in table 2's priority_CD, or do you want not 1 or 2?