Problem: Query not returning all records

[AccessAM]

Hello,
I wonder if the community can help me with a problem I cannot solve?
When I try to create a query (using query design), after selecting fields from the 2 related tables that I need information to be taken from, it only brings back some of the rows.
The 2nd table has a field which looks up data in a field from the 1st table. The 2nd table also has another field which in many records has the same data as the field that looks up data, but not always. It appears where there is a difference, the record is omitted from the query results. I haven't set any other criteria.

I hope this makes sense to someone out there!
I'll be glad to provide more details.
Kind regards and any assistance will be much appreciated.

AM

 

[Beetle]

Sounds like you're using an Inner join when what you need is an Outer (Left or Right) join. In the join properties this would be one of the options that says;

Include ALL records from 'TableX' and only those records from 'TableY' where the join fields are equal.

 

[AccessAM]

Hi Beetle - thanks for your message.
I tried your suggestion and it almost worked perfectly.
Now, the query returns all the rows but omits data where they are not equal. It's as though I need an "Include ALL records from 'TableX' and ALL records from 'TableY' even if the join fields are not equal" join option.
Do you have any other suggestions?

 

[Beetle]

It's as though I need an "Include ALL records from 'TableX' and ALL records from 'TableY' even if the join fields are not equal" join option.

This would mean removing the the join all together and the resulting recordset would likely be a cartesian product which, first of all, would be read only (not updateable) and, second, in most cases is not useful for anything.

Can you explain a bit more about the nature of the data in the two tables and what results you are expecting to see in the query? Also, does the query need to be updateable, or is read only acceptable (ie it is for a report, etc.)?

 

[AccessAM]

Thanks for your reply.
Essentially, new general data regarding customer assistance telephone numbers are entered into table 1, including the telephone number itself. In the related table (table 2), other technical data for each telephone number in entered. The telephone number is selected via a look up to table 1. Data in table 1 needs to be entered before data in table 2.

Once the telephone number is selected in table 2, the next field requires another connecting telephone number to be entered; sometimes this number is the same as the main telephone number and other times it's not (depending on how it's been set up by the telecoms provider).

The query that I'm trying to run takes information from both tables and then the problems occur.

The query should be read only.

Thanks for your support

 

[Beetle]

I must be missing something. Based on your description, every record in table 2 should have a related record in table 1 so a basic Inner join should give the results you want. What field are you using for the join?

Can you post an example of the field structure of the tables and an example of the (incorrect) results you are seeing in the query. It may also be helpful if you can post the SQL of the query.

 

[AccessAM]

Please see the attachment which contains the details requested.
Thank you.

 

[jdraw]

See this sample and see if it helps understanding your result
http://www.w3schools.com/sql/sql_join_left.asp

 

[Beetle]

From your screen shots it appears to me that your Technical Information table contains a record where the Customer Dialed Number field has a value of 226061607, and some of the other fields may have values as well, but the Orange Number field happens to be Null (or empty) for that record.

In other words, there isn't necessarily anything wrong with your join, your query is returning exactly what you asked it to return. It just so happens that there is no value in that field for that record.

If you only want to return records that have a value in the Orange Number field, then put Not Null in the criteria row for that field.

Edit: Disregard the above, as I was thinking you were still using an Inner join (just glanced at your query results at first, not your join). However, the results it is returning are what you would expect for a Left join.

I guess I'm still not clear on what results you want to see in your query?

 

[AccessAM]

Hi again,
This is the problem I'm having; the field isn't null, it has a value in it. See the attached which contains a shot of the Technical table containing both fields in question.

Thanks
View attachment screendump2.pdf

 

[Beetle]

Can you upload a copy of your db, with some dummy data (or at least any sensitive data removed) so I can get a look at what you've got going on there?

 

[AccessAM]

Hi again,
I seem to have solved the problem by removing of the "look up" from the Technical Info table to the Business Function table for the Customer Dialled Number field (I've retained the join between both fields, though). Not ideal, but at least the query is now working. I still can't understand why it didn't work and neither could some of my colleagues.
To get around the need for a look up, I've created a subform which seems to populate with the customer dialled number without the need to have a look up.
Thanks for all your contributions.