0

When I run the code below in Access 2016, I get 0 matches between the subqueries. However, if I remove the Date of Sale Field from the top subquery or from both I get correct matches. I'm certainly not a SQL expert but this seems really odd and drove me crazy for a couple of hours. Can anyone shed some light on why this might be the case?

select count(1) from
(select t1.[customer number], [date of sale],count(1) from 6mototal t1,
6molkp t2
where t1.[customer number]=t2.[customer number]
and mnth=(select distinct(month(DateAdd ('m', -4, firstdate))) from 6molkp)
group by [date of sale],t1.[customer number]
) t5,

(select t1.[customer number],[date of sale],count(1) from 6mototal t1,
6molkp t2
where t1.[customer number]=t2.[customer number]
and mnth=(select distinct(month(DateAdd ('m', -5, firstdate))) from 6molkp)
group by [date of sale],t1.[customer number]
) t6
where  t5.[customer number]=t6.[customer number]
Kacper
  • 4,798
  • 2
  • 19
  • 34
GeorgeK
  • 1
  • 1
  • 1
    You should show the version that matches and the version that doesn't match, and not make us guess at what the two versions look like. – Gordon Linoff Nov 01 '16 at 17:35
  • Try giving `[date of sale]` table alias qualifiers. Also, try using [explicit over implicit joins](http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins). Also, most likely you can do away with all subqueries and run conditional aggregation. Post data for such an answer. – Parfait Nov 01 '16 at 21:44

0 Answers0