Missing records when query runs

[SydB]

I am relatively new to Access - Beginner/Intermediate level. I am having a problem with dropped records when I run a query, or print reports that rely on the query for data.



The query is created from eight tables, seven linked to the eight with one to many links. (The eight table, which I refer to as the main table has about 720 records). The query has a parameter that requests a date before executing.

I have run the query 13 times (with different dates) and 8 times records were missing. One time as many as a third of the records. I know this because I first filter the main table by date and get a count of the records, then I run the query.

Could this be something that I have done wrong, or could it be a hardware problem raising its head?

I have both Access 2010 and 2013 on my computer which is running windows 8.1. System info: Intel i5-3330 CPU @ 3.00GHz; 8.00 GB RAM; 64-bit. Not sure if this matters but thought that I would mention it, just in case.

Can anyone guide me here.

[rpeare]

I would rule out a hardware problem almost immediately. I assume the 13 times you ran the query were for different date ranges or when you ran it for the same date range you got the same results (even if they were wrong)

With a query this complex I don't know that the SQL would help can you provide a sample database for analysis.

The things that I would check are:
1. Is your date actually stored as a date and not as a text field
2. Have you pared down your query to the simplest part, then added pieces as you verify it's still running correctly? if not, try it and see where it starts to break down.

[ItsMe]

... I know this because I first filter the main table by date and get a count of the records, then I run the query...

I have not tried to save a filter to a table. If I apply a filter to a table, I always make sure I do not save the table. I apply the filter, look at it, close the table and do not save.

Perhaps your query is looking at a filtered table.

[SydB]

I would rule out a hardware problem almost immediately. I assume the 13 times you ran the query were for different date ranges or when you ran it for the same date range you got the same results (even if they were wrong)

With a query this complex I don't know that the SQL would help can you provide a sample database for analysis.

The things that I would check are:
1. Is your date actually stored as a date and not as a text field
2. Have you pared down your query to the simplest part, then added pieces as you verify it's still running correctly? if not, try it and see where it starts to break down.

Thanks for your response. I am off to work now, but will follow your suggestions tomorrow and let you know the results. This my first time using a forum, hope I am following correct protocol.[/QUOTE]

[SydB]

Thanks for your response. I am off to work now. Will review and let you know how things work out.

[June7]

Also, INNER JOIN could be issue. Post the sql of query for analysis.

[atuhacek]

Like June7 said make sure there isn't a table included in the query that doesn't have data for a field that you want returned. when you use the query designer it defaults to fields being equal you have to edit the relationship to make it include all from one table. This is normally what haunts me. Then depending where the table is nested in the relationships you may have to break the query into smaller one's or use vba.