QUERY PhhhLEESS QUERY......

[PraizesLiberato]

Hi Guys,

This is the query
what im trying to do is get all the customers which have exceeded their acccount limit at least twice in a month with a table that only displays the customerid and the date of the excess.

Now Here is what i have done so far.

I made 2 queries

The First Query gathers the

1.Customerid
2.Month/Year as [MnthYrs]
3.Customerid& Month/Year as

In my Second Query I have created a query as seen below will give me the number of times the customers has had a axcess in their account in a particular month but i keep getting this error (Picture Attached). 
 
SELECT QueryForExsGettingMonths.Customerid
FROM QueryForExsGettingMonths
GROUP BY QueryForExsGettingMonths.Customerid
HAVING (((Left([QueryForExsGettingMonths].[code],7))=[QueryForExsGettingMonths].[MnthYrs]) AND ((Right([QueryForExsGettingMonths].[code],8))=[QueryForExsGettingMonths].[Customerid]));
 
really need help on this if you guys can.....plssssss.

 

[PraizesLiberato]

its seems simple but the aggreagete query error is maddening. if i just cousd see the result...someone help pls.

 

[PraizesLiberato]

i have a deadline

 

[PraizesLiberato]

SELECT QueryForExsGettingMonths.Customerid, Count(QueryForExsGettingMonths.MnthYrs) AS CountOfMnthYrs
FROM QueryForExsGettingMonths
WHERE (((Left([QueryForExsGettingMonths].

,7))=[QueryForExsGettingMonths].[MnthYrs]) AND ((Right([QueryForExsGettingMonths].[Code],8))=[QueryForExsGettingMonths].[Customerid]))
GROUP BY QueryForExsGettingMonths.Customerid;

Its working but i got it wrong. this code will only return the count of alll the dates for a particular customer. wheras i want the count specifically for the same customer having a repeat in the same date like.
 
cust     Dates
2         03/2010
2         03/2010
2         04/2010
2         04/2010
1         04/2010
1         04/2010
1         04/2010
 
customer      Date           Count
2                 04/2010      2
2                 03/2010      2
1                 04/2010      3
 
In other words im trying to get a list of all the customers and the dates as well notifying me how many time the dates have repeated for a particular customer
 
Therefore...
 
showing me the number of times an customer has had an excess in a month.
 
any help would be appreciated. thanks.

 

[gemma-the-husky]

its the twice thing that will make this tricky

its one thing identifying customers over their limit at a given point in time - its quite another being able to do what you want. eg - How often do you check the status. How do you record that a customer has been over the lmiit, and has now come back under the limit.

Very very tricky, I would have thought

 

[PNGBill]

This query converts dates to mnths

SELECT TblCustomerExcess.REcordPrimaryKey, TblCustomerExcess.CustID, Month([DateofExcess]) AS MonthOfExcess
FROM TblCustomerExcess;

And this query counts the records Primary key when Cust and Date are grouped from first query

SELECT QryCustomerExcess.CustID, QryCustomerExcess.MonthOfExcess, Count(QryCustomerExcess.REcordPrimaryKey) AS CountOfREcordPrimaryKey
FROM QryCustomerExcess
GROUP BY QryCustomerExcess.CustID, QryCustomerExcess.MonthOfExcess;

Your table should not have just two fields. each records should have a unique primary key