Creating a report from multiple queries 2

[mimivdg2002]

I need to create a report that keeps track of signed/unsigned records for internal programs of different departments. The following is an example of the data layout:

DeptID ProgramID Sent_Date Signed_Date
1 1 10/10/04
1 2 10/12/04
1 3 10/1/04 10/31/04
1 4 11/1/04
1 5 8/2/04 9/30/04
2 1 5/2/04 7/5/04
2 2 5/2/04
and so on...

I have a report created from two queries. I need to create it from two queries because the criteria is different.

Query 1
Gets records for which the "Sent_date" is not null and the "Signed_date" is null, essentially get the records which have been sent but not signed.

Query 2
Gets the records whose "Signed_Date" is not null (records which have been signed).

Here is the problem, when I create my report from these two queries, it looks like I get a cartesian product. Here is what my report displays:

DeptID [Sent Programs] [Signed Programs]
1 Program 1 Program 3
1 Program 1 Program 5
1 Program 2 Program 3
1 Program 2 Program 5
1 Program 4 Program 3
1 Program 4 Program 5

Essentially, what the report seems to do is duplicate the sent programs by the number of signed programs. How can I avoid this problem?

Thanks
M

 

[PaulBricker]

It seems to me that Query1 and Query2, as mentioned above, could be got just by querying for a Sent_Date that Is Not Null. That would give you Queries 1 & 2. Not sure what you are trying to do with the data once you get it in one query, but maybe I'm not seeing exactly what you are getting at. Care to explain a little more.

Thanks

Paul

 

[dhookom]

You should share you SQL view. I expect you have a cartesian product when you should/could use a union query or two subreports.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mimivdg2002]

Maybe it would be clearer if I show you what I want the report to display (given the data in the above post):

DeptID [Sent Programs] [Signed Programs]
1 Program 1 Program 3
1 Program 2 Program 5
1 Program 4
2 Program 2 Program 1

In other words, I want it to display that for dept 1, program 1, 2, and 4 have been sent but not signed, and program 3 and 5 have been signed. For dept. 2, program 2 has been sent, and program 1 has been signed.

Below is the sql that I'm using:

Query 1:

SELECT A.deptid, a.program_name, a.program_id FROM A
WHERE (((a.Sent_Date) Is Not Null) AND ((A.Signed_Date) Is Null))
;

Query 2:
SELECT A.deptid, a.program_name, a.program_id FROM A
WHERE ((A.Signed_Date) Is not Null))
;

Thanks
M

 

[PaulBricker]

This SQL is going to give your both records that you are looking for, but that won't help

SELECT A.deptid, a.program_name, a.program_id FROM A
WHERE (((a.Sent_Date) Is Not Null);

What you want is to have your data set up to look like your example and to do that you would use Query2 in a subreport and add that to the Detail section of your Main Report and link your Reports on [deptid]

Hope this helps.

Paul

 

[dhookom]

I don't see any relationship between Program1 and Program3 or Program2 and Program5. Therefore, I would create a report with only the deptid field. Create two subreports (one for not signed) and place them in the detail section of the main report. Set the Link Master/Child of these subreports to the deptid field.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]