Parameter query date dilemma

[LLLiddle]

As an Access learner, I'm tearing my hair out over what is probably a
simple problem. I hope someone can help.

I have a table for Clients (tbl.Clients with pkClientID).

Each client may use one, two or three services (tblFood, tblMoney, and
tblMentoring). Each of these tables contains a field, fkClientID.

Within each of the three service tables there is a date field
indicating each occasion a client used the service. A client may use
any or all of the services multiple times.

I need to identify and count those clients who used ANY of the services
within a specific date range to answer the question "How many clients
were served during this time period?"

A collateral question is, "How many clients were served for the FIRST
time during this time period?"

If it isn't clear, a client should be counted only once during any time
period, regardless of the number of services utilized or the number of
occasions on which the service was utilized.

Thanks.

[pietlinden]

Sounds like a design flaw, because this is a simple query.
You should have 3 tables, not 2. Oh, and don't use punctuation in
object names, it will bite you. You can use underscores, but most
punctuation marks have very definite meanings in Access.

Client(ClientID (PK), ClientName...)
Service(Service ID, ServiceText)
Uses(UClientID (PK1), UServiceID (PK2), DateUsed, ...)

Then the queries are easy.

add the 3 tables to the QBE grid, join on

Client.ClientID =Uses.UClientID
Service.Service ID=Uses.UServic eID
and then filter on the month by using BETWEEN

[LLLiddle]

pi********@hotm ail.com wrote:

Sounds like a design flaw, because this is a simple query.
You should have 3 tables, not 2. Oh, and don't use punctuation in
object names, it will bite you. You can use underscores, but most
punctuation marks have very definite meanings in Access.

Client(ClientID (PK), ClientName...)
Service(Service ID, ServiceText)
Uses(UClientID (PK1), UServiceID (PK2), DateUsed, ...)

Then the queries are easy.

add the 3 tables to the QBE grid, join on

Client.ClientID =Uses.UClientID
Service.Service ID=Uses.UServic eID
and then filter on the month by using BETWEEN

pi********@hotm ail.com wrote:

Sounds like a design flaw, because this is a simple query.
You should have 3 tables, not 2. Oh, and don't use punctuation in
object names, it will bite you. You can use underscores, but most
punctuation marks have very definite meanings in Access.

Client(ClientID (PK), ClientName...)
Service(Service ID, ServiceText)
Uses(UClientID (PK1), UServiceID (PK2), DateUsed, ...)

Then the queries are easy.

add the 3 tables to the QBE grid, join on

Client.ClientID =Uses.UClientID
Service.Service ID=Uses.UServic eID
and then filter on the month by using BETWEEN

I apologize for not explaining clearly.

I have FOUR separate tables:

tblClients (pkClientID), ClientName, Address, City...etc.

tblFoodAssistan ce (pkFoodAssistan ceID) (fkClientID), AssistanceDate,
FoodSource...et c.

tblFinancialAss istance (pkFinanceAssis tanceID) (fkClientID),
PaymentDate, CheckNumber, PaymentAmount.. .etc.

tblMentorAssist ance (pkMentorSessio nID) (fkClientID), SessionDate,
SessionLength, Mentor...etc.

While it's straightforward to select dates using tblClients and any one
of the other three tables, it falls apart if I consolidate in a single
query the ClientID, AssistanceDate, PaymentDate and SessionDate.

What I want to do is to wind up with a single query that will identify
whether a client utilized any or all of the services during a specified
period of time, preferably using a single BETWEEN...AND parameter
dialog.