Same query / different tables

[Jeff]

Hi

I am trying to determine how to write a particular query. I have 1
parent record with 2 child records. The parent has neighborhood on it.
Each child has a date field. What the users now want is a report that
lists the number of unique dates by neighborhood.

My question: how do I determine the number of unique days for a child
record? How can I tie those all together?

Thanks

Jeff

 

[Jeff Boyce]

Jeff

I don't follow...

You mention both child dates and unique days for a child. What are you
storing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff]

Sorry, to be more clear: I have a parent record that represents a
parent. (This is for a health clinic.) There's one child record for
each parent visit to the clinic. That record has an activity date of
when the parents came in. There's also a different child record for
each parent who was visited by the clinic; that record also contains
an activity date. The users of the system would like to know each
unique date in which a parent visited the clinic or was visited by the
clinic. So if 10 people visited on 2/13/07, they want to just know
2/13/07 as one unique date. I believe I can do that by grouping?

So I need a way to find each unique date and then summarize those
dates via queries.

Jeff

 

[Jeff Boyce]

Jeff

So you're saying it doesn't matter how many people came in on 2/13/07, you
just want to know that at least one came in on that date?

Create a query, add the table, add the date field, click the Totals button,
use GroupBy.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff]

That's fine for the first child record, but I'm a bit confused on how
to do that with multiple child records. My unique days would be
*either* going to the clinic or having it go to them. So if the clinic
saw people on 2/13, and they went to people on 2/14, and both worked
on 2/15, that would be 3 unique dates. So my uniques dates could exist
in 1 child record or the other or both. I know about joins but I don't
know of a join where the key of neighborhood and date could exist in
either table and end up in the query.

Thanks..

Jeff

 

[John W. Vinson]

That's fine for the first child record, but I'm a bit confused on how
to do that with multiple child records. My unique days would be
*either* going to the clinic or having it go to them. So if the clinic
saw people on 2/13, and they went to people on 2/14, and both worked
on 2/15, that would be 3 unique dates. So my uniques dates could exist
in 1 child record or the other or both. I know about joins but I don't
know of a join where the key of neighborhood and date could exist in
either table and end up in the query.

It sounds to me like you need to get a single "table" of all visits,
whether the patient visits the clinic or the clinic visits the
patient. You could do this using a UNION query of the two visits
tables. This UNION query could then be joined to the parent table to
count visits (regardless of who visited whom).

"If the rock strikes the pitcher... woe to the pitcher!
If the pitcher strikes the rock... woe to the pitcher!"

"If the patient visits the clinic... health to the patient!
If the clinic visits the patient... health to the patient!"

John W. Vinson [MVP]

 

[Jeff]

Thanks -- that did the trick!!