query selects records twice (1 Viewer)

[DrPat]

Can anyone explain to me why this query pulls every record twice and what I can do to fix it?

I did it in design mode because I'm an amateur, but here is the SQL.



SELECT [Patient Data Table].[First Name], [Patient Data Table].[Last Name], [Patient Data Table].[C MRI Facility], [Patient Data Table].[C MRI Sched Date], [Patient Data Table].[C MRI Scan Done]
FROM [Patient Data Table], [Date Range]
WHERE ((([Patient Data Table].[C MRI Scan Done])=Yes) AND ((Month([C MRI Sched Date]))=[Date Range]![From Month]) AND ((Year([C MRI Sched Date]))=[Date Range]![From Year]));

 

[pbaldy]

My guess is there are 2 records in the date range table. Without a join between the tables, you get a Cartesian product.

 

[DrPat]

Thanks Paul. I went ahead with your thinking and as a test, I deleted every record except one (one that I knew should be selected) and guess what? she gets selected twice.

Any other ideas?

 

[pbaldy]

In the second table? I've used tables for criteria, and you will have this problem if you get 2 records in it. Can you post the db here?

 

[plog]

You have duplicate data in one of your Tables for the fields you have linked the two on. In the WHERE clause you have this:

((Month([C MRI Sched Date]))=[Date Range]![From Month]) AND ((Year([C MRI Sched Date]))=[Date Range]![From Year]));

That's how you are establishing your link. So, you have duplicate values, probably in [Date Range] for the [From Month] and [From Year] fields.

 

[DrPat]

You are absolutely right. Thanks. I need to empty the date range table as the macro begins.

 

[pbaldy]

No problem. I got away from using tables for criteria, but I still have a couple of apps that use one. I had to make sure that any form that was bound to that table for the user to enter criteria had Allow Additions set to No to prevent the user from accidentally getting a second record entered.