Selecting various tables based on date parameter

[kskid]

I am trying to extract data from varions tables based on the activity date on each of the tables. If I use the "AND" connector, then all of the tables must meet the date criteria and my list is incomplete.

Table1.Date in Last7Days and
Table2.Date in Last7Days and
....
Tablen.date in Last7Days

If I use the "OR" connector then I get more than what I need. That is, I get unwanted dates simply because the ID's matched.

Table1.Date in Last7Days or
Table2.Date in Last7Days or
....
Tablen.Date in Last7Days

These activity tables are all Left Outer Joined to the Master Table by a Master ID number.

Short of making a combination selection, I might add that I have about 6 tables affected and that would be 6! or 720 combinations, I believe.

Any suggestions?

Thanks in advance.

 

[lyanch]

It sounds like you are combining id's from one table to the next? like table1.id -> table2.id ->table3.id Is there one master table that you can use...something like

master.id -> table1.id
master.id -> table2.id
etc.

That would be a good start. If not consider a union query

xxx
from table1
where table1.date in Last7Days

union

xxx
from table2
where table2.date in Last7Days

union
etc

Lisa

 

[kskid]

I am not likning the table in a series.

I have one Master table linking to each of the table with an outer join

Master ->(+)Table1
->(+)Table2

I guess the only way is to go into

Database... Show SQL Query

and add the union queries

By the way, why won't the Show SQL Query honor my carriage returns and puts everything back on one line? Sure makes it difficult trying to debug or modify the code.

Thanks

 

[synapsevampire]

Yeah, a Union sounds correct, and I agree, Crystal is very annoying when showing Union queries... I haven't found a means to make it honor formatting.

-k

 

[kskid]

Maybe I'll do the unions in Crystal SQL Query and then make my final report with Crystal Reports. Will the Crystal SQL Query get refreshed when I refresh the Crystal Report?

Thanks for all of your help.