Sorting with conditions? Help!

[niuginikiwi]

I have a report that gets printed with partially completed sales orders and
is filtered to sales orders for OrderDate field.

I have tblOrders and tblOrderDetails. A tblCustomer and
tblCustomerDestination are related to tblOrders with one to many
relationships. tblCustomer is independent of tblCustomerDestination. They
both have their PKs as FKs in tblOrders but are not related to each other
directly.
But one custome can have more than one destionations (maybe a
table/relationship design issue here)
What I want to do is to have orders taken and entred in any order as they
can but sorted to a specific order to be printed.
Eg:
A customer called MG has destinations Christchurch, Dunedin, Wellington and
customer PDL has destination Christchurch, Wellington.

I want the report to be in this order when printed:

PDL - Christchurch
MG-Dunedin
PDL - Wellington
MG-Wellington
MG-Christchurch

Hope I explained this one well.

Thanks.

 

[Larry Linson]

No, you really need to give a definition of the order you want, because it
is not "patently obvious to the casual observer." It is certainly not in
Customer, then Location order; it is certainly not in Location, then
Customer.

I want the report to be in this order when printed:

PDL - Christchurch
MG - Dunedin
PDL - Wellington
MG - Wellington
MG - Christchurch

Larry Linson
Microsoft Access MVP

 

[niuginikiwi]

Thanks Larry,

I guess I have to create another field or type Number in the tbleOrders
called something like Priority where a numeric value can be assigned to each
order on that particular date so the report can get sorted on that field.
But I am still hesitant to get pple to add that extra value ie if i can sort
the report with what i already have.

 

[Larry Linson]

Thanks Larry,

I guess I have to create another field or type Number in the tbleOrders
called something like Priority where a numeric value can be assigned to
each
order on that particular date so the report can get sorted on that field.
But I am still hesitant to get pple to add that extra value ie if i can
sort
the report with what i already have.

Can you define how "what you already have" can be used to determine the
order in which the records should be shown? Access really isn't any better
at clairvoyance than I am, and that's a skill I never developed. That is,
why was it obvious to you that the records you listed should be in the order
you listed them? Is there another field that we can't see that affects it?

Larry Linson
Microsoft Access MVP

 

[niuginikiwi]

At the moment, I can not see any field that I can really define to have
records sorted in the order I want. Since tblCustomer and
tblCustomerDestination are not directly related, I am goin down another track
to have a common field that I can use to define the sorting.
I am combining tblCustomer and tblCustomerDestination into one table and
will add another field to tblCustomer (the combined table) that will assign a
numberic value that the customer can be sorted when dealing with the report...
There will be a lil bit of repetition but there a benefits which I can see
and I do't see the records on the Customer table growing over 60 or not even
up to 100
I hope I am making the right move ...

 

[Larry Linson]

I hope I am making the right move ...

I hope so, too, but I don't understand the details of what you have and are
trying to accomplish well enough to comment on it.

A typical database application "models" the real world just to the extent
necessary to accomplish the business need it is addressing. Just from the
names, tblCustomers and tblCustomerDestinations would seem to be information
that should be related... look carefully and see if there isn't a
relationship you overlooked.

I've worked on several applications where for each "Customer" there were
multiple "Locations" ... a one-to-many relationship with CustomerID as the
key in the Customer table, and CustomerID as the foreign key in the
Locations table. That may not be what your tables represent, but it's worth
taking a careful look before jumping in feet-first making changes.

Larry Linson
Microsoft Access MVP