Displaying all records in a query

[dranieri]

I have 2 tables, 1 is were all new maintenance issues are logged named 'logged calls table'. The 2nd table is were I report on these maintenance issues named 'call report table'.

Both tables have a related field called ProblemID which I use to match all related call reports to the logged call report.They also have a Status field that tells me whether the call is complete or waiting on parts etc.

My query shows me all the logged calls and their related call reports, if there are any.

My problem starts when I set a criteria on the call report.status field in the query, to not equal to 'Complete'. The query returns only the logged calls that have a call report associated with it, that is not complete, but does not return all the new logged calls that do not have any call reports as yet, which by my criteria, are not complete.

Is there a way to show these other logged calls without call reports, when there is a criteria in the call report status field.

 

[dawnd3]

Hi there,

In the query design, click on the link line between the 2 tables and change the join type. You probably want the one that says "Show all records from logged calls table...." This will show you all records from the logged calls table regardless if there is a matching record in the other table.

Let me know if this helps.

Dawn

http://www.calliopeconsulting.net

 

[dranieri]

I've have already tried this, but still no joy. I think the solution is in the criteria field, where I have asked for all records were the status is 'not complete'. Therefore, what I want the query to show are all the call reports where the Status is 'Not complete' and all the empty call reports, if that makes any sense. The query does exactly what I have told it to do, but I need to know how to also include all logged calls were there is no call report record.

 

[dawnd3]

Is the status in the call report table or the logged calls table? Is the relationship a 1 to 1 relationship or 1 to many? It sounds like you want to find the records that are not equal to complete and also null. But if the status field is in the call report table, and those records only show up if there is a call report to link to a logged call then there is no status field to query. Feel free to send me the database and I will take a quick look. It might be easier than trying to explain. dawn@calliopeconsulting.net

Dawn

http://www.calliopeconsulting.net