Joining Unioins output to tables? Help Needed

[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 the query, or would it best somehow?? to output the query runs to a table 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

[techexpressinc]

For both queries I excuted another query to sum and merge up the data. Same set of tables at the root. And, 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 would be look like:
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]

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

[RuralGuy]

Thanks for letting us see your solution. I love it when people solve their own problems. You get to see people learn.