Re: Table Join (Maybe?)

Phillip Smith wrote:
>
> Hi again all,
>
> I have two tables:
>
> 1. Sales figures by date and customer.
>
> 2. Customer details – including their Geographic State
>
> I need to extract a report from the first table (I can do that!), and 
> in that report order by their State (I can do that too!), but I also 
> need a summary of all the customers in each state, below the end of 
> each state, and have a grand total at the bottom.
>
> Eg:
>
> Customer 1 State 1 $100.00
>
> Customer 2 State 1 $100.00
>
> State 1 $200.00
>
> Customer 3 State 2 $100.00
>
> Customer 4 State 2 $100.00
>
> State 2 $200.00
>
> Grand Total $400.00
>
> Does anyone have any magic pointers for me? I’ve been playing with 
> SELECT INTO as 2 queries (the individual customers, then the summary 
> figures added to the temp table) but I end up with ROWS IN FIRST QUERY 
> * ROWS IN SECOND QUERY instead of them all sorted together nicely L
>
> Thanks all,
>
> -p
>
Well, two queries one for the individual totals and one for the summary 
totals is good (maybe a third for the grand total), but you should do a 
union of the two and then play with the order by and/or group by clauses 
(depending on the data) to get the ordering that you want. I can't even 
count the times I've spent banging my head against the proverbial wall 
(you do have a proverbial wall don't you?) trying to get these kinds of 
queries to work with joins, sub-queries, case statements, etc... only to 
come back to using union on simple, to-the-point queries.

-- 
erik jones <erik@myemma.com>
software development
emma(r)