Help with "current client" list

[Me]

Hi All,

I've got a table named tblClient that has a ClientID as PK. I've got a
table named tblTransactions that includes a FK of ClientID and some other
controls one of which is DateOfTrans that is the date the transactions
happened.

I need a query, one row per client, of anyone that has had a transaction
since 1/1/2006. I've tried it on my own and I get something like 6 lines
for John Smith, 11 lines for Harry Wilson, etc...and these lines correspond
to the 6 transactions for John and 11 for Harry since 1/1/2006. Not what I
need. I need just one line per client. HELP!

Anyone point me in the right directions?

TIA
me

 

[Jeff Boyce]

Let's see if I can paraphrase your request correctly ...

I want to see who has had any transaction(s) since 1/1/2006.

If that's a fair paraphrase, start a query with the both tables, joined on
ClientID.

Add in the ClientName and the TransactionDate columns.

Click on the Totals button (looks like a backwards "3").

The aggregation for both will be "GroupBy" ... now change the aggregation
for the TransactionDate to "Where", and put the date in the Selection
Criterion beneath it, something like: >#1/1/2006#

When you run this query, it finds all transactiondates after 1/1/2006, and
shows each person once.

Is that what you're after?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Me]

Jeff-

Exactly what I needed. Works like a charm.

I spent quite a while trying to figure this out before posting. Guess I
should take a class.

Thanks,
me