Need help with query that links to another query

[Christine Pearc]

Hi. I'm having a strange problem I hope someone can help with.

In QueryA, I'm getting values from TableA, which includes a field called AssigneeID.

I want to see the name associated with AssigneeID, so in QueryA, I've added qryAssignee, which has a relationship to TableA.

Trouble is, if the record doesn't have an Assignee, it doesn't show up when I run the query. What am I doing wrong, and how do I fix it?

Christine

 

[lcross]

i don't know if is the correct way to do it, but you can create a table Assignee_details
related with table A, then just call the query with that field. donn't forguet to make the right relationship

 

[Christine Pearc]

Um, If I understand you correctly I've already done that: qryAssignee gets its info from tblEmployees.

I do understand that, in records that don't have an AssigneeID, the nature of the relationship with qryAssignee can never be true.... I just don't know what to do about it!

Help!!

 

[Rovers]

You need to change the properties of the Join between qryAssignee AND TableA so that you are selecting everything from TableA regardless of whether there is a record in the qryAssignee

RClick on the join (relationship)

change the properties so that it looks liek this:


TableA --> qryAssignee

Try this.

 

[Christine Pearc]

Thanks, Rovers. I've got it set up as you say:

Left table: Right table:
TableA qryAssignee

Column: Right column:
AssignToID AssignToID

Of the three join property options, #1 is selected. I know that's not right, but the other two options aren't appropriate either. Where would I find the option to select everyting from TableA regardless of whether there is a record in qryAssignee?

 

[Rovers]

Hello,

You need option 2. The one that should say:

Include All Records from 'TableA' and only those from 'qryAssignee' where the joined fileds are equal.

If you've still got problems then zip and attach a copy and I'll have a look.

 

[Christine Pearc]

Hi again. The problem with selecting option 2 is that I want to see ALL records in my table - not just those who have an Assignee. Is this possible?

Last night I did find a work-around by using DLookup:

Assignee: IIf([tblCARs.AssignToID]>0,DLookUp("[FullName]","qryEmployees","[EmployeeID]=" & [AssignToID]),"")

However, this seems to take an unearthly amount of time.

Depending on your answer, I'll clean up my app to get rid of unnecessary stuff and send it to you.

Cheers for sticking with me on this!
Christine

 

[Rich]

Your join's the wrong way around, you want All the records from queryA and only those from the table where the join fields are equal

 

[Christine Pearc]

Rich, thanks for joining in. I tryed what you said, and also took Rover's suggestion to change the join properites to option 2. This produced the message:

"The SQL statement could not be executed because it contains ambiguous
outer joins. To force one of the joins to be performed first, create a separate
query that performs the first join and then include that query in your SQL
statement."

I don't understand what "...the first join..." is supposed to join to (what to do).

(I also tried your suggestion, but changed the join properties to option 1, but this increased the record count from 61 to 333.

 

[Rich]

What's the record source of QueryA, you could also try the unique values property

 

[Christine Pearc]

QueryA takes information from the main table (tblCARs), which is used to store corrective action requests (CARs). The purpose of QueryA is simply to present a list of all CARs in the system. The results of the query are in a subform; the main part of the form allows the user to search for CARs based on various criteria that they select.
Some CARs have been approved, in which case they have an Assignee; some are rejected, so they don't have an assignee and others have not yet been reviewed so they wouldn't have an assignee either.