Group / Count Dates with a one-to-many relationship

[nooneknows]

Hello all,

I searched the posts/faq's for an answer to my question, but was not able to find a solution.

I have an issues tracking database for documents. Each document can have one or more issues. Each record has a timestamp that is populated when it is opened/closed.

Tables:

tblDocuments
Doc_ID (Primary Key)
DocumentEntered_Timestamp (Date)
DocumentClose_Timestamp (Date)

tblIssues
Issue_ID (Primary Key)
IssueOpen_Timestamp (Date)
IssueClose_Timestamp (Date)

I am trying to create an "Activity Report" by grouping / counting the Timestamp dates.

-------------------------------
Report:

October 1, 2003
Documents Entered: 5
Documents Closed: 4
Issues Opened: 6
Issues Closed: 4

October 2, 2003
Documents Entered: 6
Documents Closed: 7
Issues Opened: 5
Issues Closed: 3
-------------------------------------------

Ideally, I would like to pass a date range parameter and have a query count/group each of the Timestamp dates. I have tried to create a crosstab report, and several attempts at various sub-reports, but I am completly at a loss on how to achieve this.

Any help is appreicated.

Thanks.

 

[dhookom]

I would normalize and union your tables into a single query:
SELECT "Document" as DocIss, DateValue(DocumentEntered_TimeStamp) as TheDate, "Entered" as Event
FROM tblDocuments
WHERE DocumentEntered_TimeStamp is not Null
UNION ALL
SELECT "Document", DateValue(DocumentClose_Timestamp) , "Close"
FROM tblDocuments
WHERE DocumentClose_Timestamp is not Null
UNION ALL
SELECT "Issue", DateValue(IssueOpen_Timestamp), "Open"
FROM tblIssues
WHERE IssueOpen_Timestamp is not Null
UNION ALL
SELECT "Issue", DateValue(IssueClose_Timestamp) , "Close"
FROM tblIssues
WHERE IssueClose_Timestamp is not Null;
Then, just create a totals query that groups by TheDate, DocIss, Event and counts DocIss.


Duane
MS Access MVP