Need to summarize data

[mac6j]

Hello - I am no Access pro, so maybe many of you will get a laugh out of this question. Here goes.

I have a table that's on the "many" side of a "one to many" relationship. So there are multiple records in this table that have 1 company file number.

Key/Company Record #/Item # /Date Due/Date Complete
1/122/A /4-22-09/null
2/122/B/4-1-09/4-2-09/
3/123/C/5-6-09/null
4/123/D/4-2-09/4-1-09/

I need a query that summarizes each company record and telling me whether a certain record # has overdue items or not. I don't need to know which item is overdue. I don't need to know when it was due or complete. I just want to summarize. I want 2 columns in the result: Record # and Yes or No. And I don't want multiple rows for one record #. This is critical.

I want to eventually put this data in a form, but that's a different story.

I am coming close with a cross-tab. But I can't just get that simple 2 column result. Any help would be greatly appreciated.

Thanks!

 

[SkipVought]

Hi,

So given your example, EXACTLY what would you expect for
[tt]
Company Record # YesNo
122
123
[/tt]
Please explain your LOGIC as well.

I assume that you would post an example that illustrated BOTH a Yes and a No.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[mac6j]

Sorry-
Hopefully I can get a result like this:

Company Record # / Has Outstanding Overdue Items?
122/Yes
123/No

Thanks!

 

[SkipVought]

...and the LOGIC that you used?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[mac6j]

I don't know what you mean by Logic. programming logic - or the logical explanation for what I need. I'm guessing the first of those 2. That's what I'm asking for. I don't know where to begin.

 

[SkipVought]

I don't need to know which item is overdue. I don't need to know when it was due or complete

This seems a bit contradictory, but I gues what your requirement is, Yes if it is OVERDUE and NO if it is not yet due...

select [Company Record #], iif([]<Date([Date Due]),'Yes','No') As YesNo
From [YourTable]
Where [Date Complete] Is Null
Group By [Company Record #]

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[mac6j]

Won't that give me repeats of company record #? For example, it would give a result of

122/Yes
122/No
123/No
123/No

I just want a summary - does any one company record # have any items overdue?

So the only result I'd see is

122/Yes
123/No

 

[PHV]

A starting point:

SELECT DISTINCT A.[Company Record #],EXISTS(SELECT * FROM yourTable AS B WHERE B.[Company Record #]=A.[Company Record #] AND B.[Date Due]<Date() AND Nz(B.[Date Complete],Date())>B.[Date Due]) AS [Has Outstanding Overdue Items?]
FROM yourTable AS A

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

sorry,

select [Company Record #], iif(MIN([Date Due])<Date,'Yes','No') As YesNo
From [YourTable]
Where [Date Complete] Is Null
Group By [Company Record #]

Missed that somehow.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}