By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,271 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

Right Join query with Between HELP

P: 1
Hi,
I have a table with a date_created and date_expired and I want to look
at the 15th of each month and every month since the system started and
count up how many accounts were active at each of those points in
time.
Will have to assume that if there's an expiry date bigger than today
then that account was active at each of those points in time.
I created a numbers table from 1 to 100.
I have the following code working well in SQL2000 but I can't make it work in Access! Can someone tell me how to make this work in Access?

Expand|Select|Wrap|Line Numbers
  1. SELECT The15th, COUNT(Account_Created) AS Cnt 
  2. FROM AccountsHistorical 
  3. RIGHT JOIN (SELECT DATEADD(month,n,'20020703') as The15th FROM Numbers) x 
  4. ON x.The15th BETWEEN AccountsHistorical.Account_Created AND AccountsHistorical.Accounts_Expire 
  5. GROUP BY The15th 
  6. ORDER BY The15th
Mar 20 '07 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,366
Hi,
I have a table with a date_created and date_expired and I want to look
at the 15th of each month and every month since the system started and
count up how many accounts were active at each of those points in
time.
Will have to assume that if there's an expiry date bigger than today
then that account was active at each of those points in time.
I created a numbers table from 1 to 100.
I have the following code working well in SQL2000 but I can't make it work in Access! Can someone tell me how to make this work in Access?

Expand|Select|Wrap|Line Numbers
  1. SELECT The15th, COUNT(Account_Created) AS Cnt 
  2. FROM AccountsHistorical 
  3. RIGHT JOIN (SELECT DATEADD(month,n,'20020703') as The15th FROM Numbers) x 
  4. ON x.The15th BETWEEN AccountsHistorical.Account_Created AND AccountsHistorical.Accounts_Expire 
  5. GROUP BY The15th 
  6. ORDER BY The15th
Try:
Expand|Select|Wrap|Line Numbers
  1. SELECT AccountsHistorical.The15th, COUNT(Account_Created) AS Cnt 
  2. FROM AccountsHistorical 
  3. RIGHT JOIN (SELECT DATEADD("m",AccountsHistorical.n,#3/7/2006#) AS The15th FROM Numbers) As x 
  4. ON AccountsHistorical.The15th = x.The15th
  5. GROUP BY AccountsHistorical.The15th
  6. HAVING (AccountsHistorical.The15th BETWEEN AccountsHistorical.Account_Created AND AccountsHistorical.Accounts_Expire)
  7. ORDER BY AccountsHistorical.The15th;
And is n a numeric field in your table?
Mar 20 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.