Re: Sorting of data from two tables

On Sat, Sep 17, 2011 at 2:56 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:
> On Sep 17, 2011, at 9:32, "R. Smith" <ship(dot)quotes(at)gmail(dot)com> wrote:
>
>
> What I want to do is do a query joining table A with B and sorting
> firstly on a field in Table A then on several fields in Table B.
>
>
> SELECT a.gdn_gdn, a.gdn_custref, a.gdn_date, a.gdn_address_name,
> a.gdn_method, b.gdn_stockref, b.gdn_row, b.gdn_bay, b.gdn_shelf
> FROM a
> LEFT JOIN b
> ON a.gdn_gdn = b.gdn_gdn
> ORDER BY a.gdn_method,b.gdn_row, b.gdn_bay, b.gdn_shelf
>
>
> It would help if you gave an example comparing the above query results with
> your desired results.  It would also help if you actually provided a
> detailed description of you goal instead of the generic one quoted above.
>  Given your stated need the query does exactly what you requested.
> David J.

Ok, here is some more specific information. The data stored is
inventory pick data.
We have a table which stores all the header information, addresses etc
(Table A) and
then each order line (Table B) which stores item to be picked and location.

Now what we want is the data sorted by the method to be sent out which
is stored in
Table A and then the location of the item being picked which is stored
in Table B. Now
where there is more than one order line we just sort on the first order line.

If we use the above query to sort the data, we end up with the order
data all mixed up
with one multiple orders spread amongst other orders, as the query
does not know that
some of the orders in Table A have multiple order lines in Table B. So
we may pick order 1,
then order 2, then order 3, and all of sudden come back to order 1, as
there is a second order
line to pick which is in a different location to the first order line
in order 1, and order 2 and order 3.

So simple solution is to drop it in to a cross tab query, then sort it
as many times as you like as
all the order data is in one row now rather then multiple ones.
However the moment you run the
crosstab query it fails, as the number columns are a variable which
you cannot have. Hence
my asking how you do this?

Sample data output:

Standard Query

Order 1, Despatch Method A, Orderline1, Item Ref, Location A
Order 2, Despatch Method A, Orderline1,Item Ref, Location A
Order 3, Despatch Method A, Orderline1,Item Ref, Location A
Order 1, Despatch Method A, Orderline2, Item Ref, Location B
Order 4, Despatch Method A, Orderline1,Item Ref, Location B
Order 5, Despatch Method B, Orderline1,Item Ref, Location A
Order 6, Despatch Method B, Orderline1,Item Ref, Location B
Order 7, Despatch Method B, Orderline1,Item Ref, Location B
Order 5, Despatch Method B, Orderline2,Item Ref, Location B

Crosstab Query

Order 1, Despatch Method A, Orderline1, Item Ref, Location A,
Orderline2, Item Ref, Location B,
Order 2, Despatch Method A, Orderline1,Item Ref, Location A
Order 3, Despatch Method A, Orderline1,Item Ref, Location A
Order 4, Despatch Method A, Orderline1,Item Ref, Location B
Order 5, Despatch Method B, Orderline1,Item Ref, Location A,
Orderline2,Item Ref, Location B
Order 6, Despatch Method B, Orderline1,Item Ref, Location B
Order 7, Despatch Method B, Orderline1,Item Ref, Location B

Richard