Join, Union, or create a table from query??

[techexpressinc]

I have 2 queries and need the data merged.

One query I pull the life-to-date data the other I pull last-week-data. I need the output of both queries merged on a report.

There always will be a match on a key field from the life-to-date-data to the last-week-data (family-number).

To get the data on the report what would work best? Can you do Joins on queries, or would it best somehow?? to output the query run outputs to a couple tables then do a simple join on the tables and put the data on the report?

Thanks for any help in this problem.
Russ - Rneuman at SCANinc.org

 

[Atomic Shrimp]

Do both of these queries use the same table(s)?

If so, it may be possible to merge them just by tweaking the criteria so that it includes all of the criteria from both queries.

Alternatively, if the output fields of the two queries are identical in structure, you could just create a union query based on them both - something like:

SELECT query1.* FROM query1

UNION

SELECT query2.* FROM query2;

Then save that query and use it as the basis for your report.

There might be duplication of data however - doesn't 'life-to-date' also actually include 'last week'?

 

[techexpressinc]

For both queries I excuted another query to sum and merge up the data. Same set of tables at the root. Yes, all the last-week-data is in the life-to-date-data.
Life-Cases - Last-Week-Cases
101 .......... none
102 ...........none
103............103 - Match
104........... none
105........... 105 - Match

The new query output to go to the Report
101 only ytd amts/percent-of-goals
102 only ytd amts/percent-of-goals
103 both ytd amts/percent-of-goals and weekly amts/percent-of-goals
104 only ytd amts/percent-of-goals
1053 both ytd amts/percent-of-goals and weekly amts/percent-of-goals

 

[techexpressinc]

Re: Instead of joins/unions - add new wkly fields

Probably the best would be to create a subset of weekly total fields within the lifetime query pull of data.

This is will be little a little hairy for the selection for a weekly-amt that is come from a user-entered parameter.

For example one field name = "F2F" - (Face-to-Face) activity sum. The formula in query is now:

F2F: Round(IIf(([ACTIVITY TYPE-SA]<>"Travel") And ([RATE VALUE]="1"),[TIME-SA],"0"),2)

I would need to create the weekly field like this:

WKLY-F2F: Round((IIf(([ACTIVITY TYPE-SA]<>"Travel") And ([RATE VALUE]="1"),[TIME-SA],"0"),2) and (DATE-SA Between [Start Date] And [End Date])

Does this look and sound like a great way to go?

Thx Russ

 

[techexpressinc]

I got the formula here it is:

WKLYF2F: Round(IIf(([ACTIVITY TYPE-SA]<>"Travel") And ([RATE VALUE]="1") And ([DATE-SA] Between [Start Date] And [End Date]),[TIME-SA],"0"),2)