Queries not picking up newly added records

[KirRoyale]

Hi all.
New member here – I hope I’m not breaking any etiquette or protocol rules! Only recently started to get to grips with Access (with the help of ‘The Missing Manual’ and some online resources).
Usually I can find answers to my Access problems by searching other people’s questions. On this occasion, I drew a blank so decided to register here and reach out…..
I created a database. exported some Excel data into Access tables, created a number of lookups, default values etc. and created a pretty simple query.
My problem is that after importing and tweaking the data, when I add new records, my queries do not pick them up!
I have tried:
1. saving, closing, opening and re-running the queries.
2. putting an Nz expression for each field in the query as I read that null values may cause a problem.
3. wrote the query again, field by field to see if all records were received.
4. Exported the table back to Excel and imported to a new Access table in my database
None of this works.
Would somebody please be able to advise? Let me know if it would help to provide more info.
Thanks in advance!

 

[Galaxiom]

Where are you adding the new records?

 

[KirRoyale]

In a table on which the query is based.

 

[Galaxiom]

Your query Where condition is probably not matched to the new records.

One of the most common problems involves:
WHERE [somefield] <> somevalue

or

WHERE [somefield] = Null

These expressions will not return records with Null in [somefield] because any comparison with Null will return False. Null can only be tested with Is Null.

If this isn't it then post the SQL of your query and describe the records that it is not returning.

 

[KirRoyale]

Thank you. I think I understand what you say.

The query is as follows:

SELECT Nz([JobDetail].[Date],"") AS [Date], Nz([Source].[Source],"") AS Source, Nz([Region].[Region],"") AS Region, Nz([JobDetail].[Ref],"") AS Reference, Nz([JobDetail].[Position],"") AS [Position], Nz([Company].[Company],"") AS Company, Nz([Status].[Status],"") AS Status
FROM Status INNER JOIN (Company INNER JOIN (Region INNER JOIN (Source INNER JOIN JobDetail ON Source.ID = JobDetail.Source) ON Region.ID = JobDetail.Region) ON Company.ID = JobDetail.[Posted by]) ON Status.ID = JobDetail.Status;

In the 8 records that have been recently input and are not picked up in the query, there are only 2 fields that are not populated for some (but not all) of those records. In the properties, these 2 text fields are not required and zero length is allowed.

 

[Galaxiom]

INNER JOIN will only return a record if records are in both tables connected by the ON clause.

You probably need some OUTER JOINS (LEFT JOIN or RIGHT JOIN) which will return all the records from one side plus the related records from the other before applying the WHERE clause.

Right click on the line between the tables and change the Join Type.

 

[KirRoyale]

Thank you I'll try that!

 

[KirRoyale]

Hi Galaxiom,
That worked a treat! Thank you very much!
I appreciate your prompt help! Have a good evening.