Help Needed!! Stuck in MS Access

[tanujm]

Hi Everyone,

I have a problem with Ms Access and can't seem to figure out a good way to do it !!

I have a to write a query to which picks out how many times a customer has come into the shop for servicing (it should only pick those who have come more then once). The following are the tables:

Customer Table:
CustomerID
LastName
FirstName
Address
Suburb
Postcode
Service_ID

Service Table:
ServiceID
ServiceDescription
Hours
ServiceCharge
GST_Required
Customer_Id

How do I write the query ?? I have tried all I know and cant figure it out. Can anyone help me please. Any Suggestions would be greatly appreciated.

 

[tkovirus]

SELECT Count(ServiceTable.ServiceID) AS CountOfServices, ServiceTable.Customer_ID, ServiceTable.ServiceID
FROM ServiceTable
GROUP BY ServiceTable.ServiceID
HAVING (((Count(ServiceTable.ServiceID))>2))

...this should gt you on the right track...

 

[tanujm]

Dear tkovirus - Thanks for the help -

I m getting a error message when I try to run the query:

"You tried to execute a query that does not include the specified expression "Customer_Id" as part of an aggregate function."

Can you please tell me what do I do to get rid of this error?

Thanks,

Tanuj

 

[tkovirus]

This would happen if the Group By field is missing or miss-spelled.

 

[tkovirus]

...sorry...I forgot to include the CustomerID

SELECT Count(ServiceTable.ServiceID) AS CountOfServices, ServiceTable.Customer_ID, ServiceTable.ServiceID
FROM ServiceTable
GROUP BY ServiceTable.ServiceID,ServiceTable.Customer_ID
HAVING (((Count(ServiceTable.ServiceID))>2))

..try this

 

[tanujm]

Hi Again,

Thanks for the help.......

Its running now but not returning any entries.....

The ServiceID is a primary key in ServiceTable & Customer_ID is a lookup in the ServiceTable.

maybe I have design flaw in my tables ??? is it possible to show u the access file ... like email it to u ....... ofcourse if you are free and have time......... I really appreciate your assistance.....my email id is nnew@bigpond.net.au