retreive data with a date range and total account > 2

[tbscmgi]

To All-
I'm new to Access Queires, I trying to get data from a table. My column name are as follow.
ID
Number
Date
Amount
Account

Select Account , Date
from Cust
where date between '010107' and '033107' and (here is where I'm having a problem) I need to get all account that is between the date range and the account have to be there 3 or more times.

Thanks

 

[lespaul]

Maybe:

SELECT Account, Date FROM Cust C INNER JOIN (SELECT Account FROM Cust GROUP BY Account HAVING Count(*) > 3) A ON C.Account = A.Account WHERE [Date] between '010107' and '033107'

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[PHV]

SELECT Account, [Date]
FROM Cust C INNER JOIN (
SELECT Account FROM Cust [!]WHERE [Date] Between '010107' And '033107'[/!] GROUP BY Account HAVING Count(*) > 3
) A ON C.Account = A.Account
WHERE [Date] Between '010107' And '033107'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tbscmgi]

Leslie and PHV thank you very much for all of your help.
Tony

 

[Melagan]

Be careful with field names like "Date", as that is a reserved word in Access and could cause you problems later down the line. Something a bit more specific like Entry_Date or Order_Date would be preferable.

~Melagan
______
"It's never too late to become what you might have been.

 

[tbscmgi]

To Lesli, PHV or Melagan
what does this "A." mean.

Thanks
Tony

 

[tbscmgi]

To Lesli, PHV or Melagan
This is my script:
I'm having a syntax error in the FROM statement.

SELECT [wiresTBL].[ID], [wiresTBL].[Number], [wiresTBL].[Date], [wiresTBL].[Amount], [wiresTBL].[Account], [wiresTBL].[CustName], [wiresTBL].[Bank], [wiresTBL].[BeneficiaryOrignator], [wiresTBL].[StateCountry]
FROM wiresTBL AS W INNER JOIN [SELECT Account FROM wiresTBL GROUP BY Account HAVING Count(*) > 2] AS A ON W.Account=A.Account
WHERE [Date] between '012007' and '062007';

Thanks
Tony

 

[PHV]

SELECT W.ID, W.Number, W.Date, W.Amount, W.Account, W.CustName, W.Bank, W.BeneficiaryOrignator, W.StateCountry
FROM wiresTBL AS W INNER JOIN (
SELECT Account FROM wiresTBL WHERE [Date] Between '012007' And '062007' GROUP BY Account HAVING Count(*) > 2
) AS A ON W.Account=A.Account
WHERE W.Date Between '012007' And '062007'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tbscmgi]

Hi PHV-
I'm having this problem:
Data type mismatch in criteria expression.
I never saw this error before.
Thanks for all of your help
Tony

 

[Melagan]

To Lesli, PHV or Melagan
what does this "A." mean.

That is a table alias.

SELECT a.Field1, a.Field2, b.Field2
FROM tblMyTableA [b]AS a[/b] INNER JOIN tblMyTableB [b]AS b[/b] ON
a.Field1 = b.Field2

Otherwise...

SELECT tblMyTableA.Field1, tblMyTableA.Field2, tblMyTableB.Field2
FROM tblMyTableA INNER JOIN tblMyTableB ON
...
etc.

It just makes typing the code a whole lot faster and also improves readability, in my opinion.

Note that [tablename] AS [alias] -- AS is optional.



~Melagan
______
"It's never too late to become what you might have been.

 

[lespaul]

that error occurs when you are:

1. searching for a string and using numbers
2. searching for a number and string delimiters

Is your [Date] really a Date datatype in the table? then it would use the # delimiters. Since the only delimiters in this query are the ' around your date information, I would check that. (Of course, we were just copying the date information you posted:

Select Account , Date
from Cust
where date between '010107' and '033107' and (here is where I'm having a problem)

)