Multi-table query returning only stalest record in child table

[Anchoress]

Want Multi-table query to return only stalest record in child table

E2A: This question has been answered to my satisfaction - thank you to user boblarson!

________________________________

Hi: long time no post. For anyone who remembers me, I changed job 16 months ago and now work for a non-profit, mostly in The Raiser's Edge.

I'm creating a small Access DB to manage contacts and prospects for our upcoming Capital Campaign. We're working in 97-2003.

The DB is a simple contact management DB, and the principals on the project want a multi-table query with several fields from the parent (contact) table, and ONE piece of data from the child table (two fields) - they want to see only the stalest INCOMPLETE item from the child table, which is a combo activity/to-do list.

So the returned query would have one line item for each parent record, and would show the oldest outstanding to-do item (whether it's in the past or in the future).

I've included a copy of my DB. The parent table is called "PROSPECTS". The query I created (with the fields my principals want but no code) is called "Q Prospects most recent outstanding action".

If anyone could help me OR just direct me to a thread with a similar question, I'd most deeply appreciate it!

E2A: To make it more clear for folks who might not want to open my DB, following is a list of fields in the query, with additional info:

Title - (Parent) No criteria
FName - (Parent) No criteria
LName - (Parent) No criteria
CompanyName - (Parent) No criteria
AssistantName - (Parent) No criteria
PrimaryContactAtFirst - (Parent) No criteria
DueDate - (Child) Criteria = want to see only the oldest outstanding record (without a complete date)
Purpose - (Child) No criteria
CompleteDate - (Child) Criteria = Is Null (only incomplete items to be queried on)

 

[boblarson]

You state:

DueDate - (Child) Criteria = want to see only the oldest outstanding record (without a complete date)

But after looking in your database, there is no field which can be used to identify which is the oldest record without a complete date. If you would capture the date/time of input then you could do it. But as it stands now, there isn't a way.

 

[Anchoress]

HI Boblarson: Thanks for the reply. So if the query were filtered to exclude all the completed actions, wouldn't the 'due date' field enable one to find the oldest item in the to-do list? Maybe I'm mis-understanding your question, or perhaps I expressed myself poorly.

For instance: If I run the query I created as-is, it shows all the incomplete items for all records. If I just want to see the oldest item for each record, isn't that what I asked for? What is missing from my DB to prevent that?

Anchoress

E2A: The Due date is the field to be used to determine the oldest outstanding item.

 

[Anchoress]

Using the "DMin" function that I just read about in a different thread, LOL, would the following work:


Dim dteNextDue as Date
NextDue = DMin (“[DueDate]”, “[T Communication and activity log]”, “[CompleteDate] = Is Null”]


If so, where would I put it? In the criteria line for a new field, or the title line? Or in SQL?

 

[boblarson]

So you want the earliest Due Date which doesn't have a completed date? Don't bother with DMin.

Check out what I did with your database. I first started with building this part (qryEarliestDueDateWhereCompletedIsNull) and then used it in your existing query to limit it to the right records. Let me know if you have any questions.

See the attached revised database file.

 

[Anchoress]

Hi, boblarson: That's perfect! Thank you so very much!! I hope my question and your answer may be of use to others, as well!