one to many relationship problem :/

[dbenoit64]

The one table is called applications.
The many table is called documents.
(One application has many docs).

I need to make a report that selects the appID and the count of documents in the past week for that appid. (plus other things)

I think i should create a query that gets this info fist. but its impossible to to do the count for the week.

These are the things I must include in my report:

1.AppID
2.#Docs for the app this week
3.Total size of the docs for this week for each app
4.#Docs for the app this month
5.Total size of the docs for this month for each app
6.Oldest Document for each app
7.Newest Document for each app
8.Total size of all docs
9.Total # of docs

I have figured out how to get all of them except for:

2.#Docs for the app this week
3.Total size of the docs for this week for each app
4.#Docs for the app this month
5.Total size of the docs for this month for each app

This is how i did them so far:

For appid

Field: APPL_ID
Table: APPL_INFORMATION
Total: Group By
Sort:
Show: x
Criteria:

for overall size:

Field: SumOfDOC_BYTES_E1: DOC_BYTES_E
Table: APPL_DOCUMENTS
Total: Sum
Sort:
Show: x
Criteria:

for oldest doc

Field: DOCUMENT_UPDATE_DT
Table: APPL_DOCUMENTS
Total: Min
Sort:
Show: x
Criteria:

for newest doc:

Field: DOCUMENT_UPDATE_DT
Table: APPL_DOCUMENTS
Total: Max
Sort:
Show: x
Criteria:

for overall total:

Field: DOCUMENT_ID
Table: APPL_DOCUMENTS
Total: Count
Sort:
Show: x
Criteria:

where i have to group the apps the resultt just comes up empty (where there are no recent documents obvoiously). I can fathom any sql statement that has the power to do this so i dont know if its even possible but i guess there has to be a way.
can you help me out here?? it is really important to me.

Thanks,

Dave

 

[Chris RR]

I'm not really clear whether you have one query or more than one. If I were doing this, I would make as many queries as I needed to get the various elements, then join those queries into one for use in the report.