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(at)myemma(dot)com>
software development
emma(r)