MS Access query returning duplicate row when searching for the last x months of data-CodePudding

I need to get the last 13 months of data from a table using a query. The table has this format: Data Table. Where for every month over several years, a series of lanes as an accuracy and errors value. The Audit_Date column is Date/Time data type set to display mm/yyyy.

The query seems simple, pull the last 13 months for a specific lane.

SELECT [tbl_AC-02_Audit].Audit_Date, [tbl_AC-02_Audit].Lane, [tbl_AC-02_Audit].Accuracy, [tbl_AC-02_Audit].Errors, tbl_Date_Metric.[Metric Value]
FROM [tbl_AC-02_Audit] 
INNER JOIN tbl_Date_Metric ON [tbl_AC-02_Audit].Audit_Date = tbl_Date_Metric.[Audit Date]
WHERE ((([tbl_AC-02_Audit].Audit_Date)>DateAdd("m",-14,Date())) AND (([tbl_AC-02_Audit].Lane)="2N"));

Or if viewed in the query UI:

query UI

The query works fine for the last 10 months. But when I look back to 11, 12, or 13 months, I always get a duplicate data row in the query result, as shown in the next pic:

query result

NOTE: the base data table does NOT include any duplicate data rows - all rows are unique.

I have tried several variations on the date look-back code including,

DateAdd("m",-14,Date()), as shown in the pic, and DateSerial(Year(Now()),Month(Now())-14,Day(Now())),

suggested in another post regarding date look-back queries. Both forms, DataAdd and DateSerial, work up to looking back 10 months, but duplicate a row at 11 months.

Ultimately, the query will feed data to a chart on a form. But for now, the query does not return the correct data. What am I missing and why does the query duplicate a row when the look-back exceeds 10 months?

CodePudding user response：

Doah! The base table (Audit) did not have any duplicate entries as noted in the original post. However, the joined table (Date_Metric) DID have two entries for May. I was certain I had checked both tables, but evidently I missed it. Deleting the duplicate entry from the joined table produced the desired results. Thanks everyone for the help.

CodePudding user response：

So I will suggest you use a select statement that prompts for two date parameters.

****E.g select col1, col2, col3

from table A
Where datefield between dateparameter1 and dateparameter2****

You can input the parameter under the criteria section in the query design view

This will allow you to enter any date range easily, if you still get duplicate output, then be sure the underlying records are dupicated.