Prefered Customer (1 Viewer)

[Gismo]

Hi all,

I have a preferred customer set up in my products table
When I raise a new purchase order and I select the customer, I would like to have the items with the customer as preferred customer to be sorted to the top of the combo box and the balance of the products below. If no preferred customers is set up then just display alpha numerically

Please could you advise on how to approach this?

 

[bob fitz]

I have a preferred customer set up in my products table

I don't really understand this. Can you tell us more about what this means and how it is done.

 

[jdraw]

I agree with Bob that we need more info and perhaps an example or 2.
What makes a Customer a preferred Customer?
Are you looking at previously purchased Products in some way?

 

[CJ_London]

I'm rather confused - purchase orders are usually for suppliers, not customers. And this comment

just display alpha numerically

makes no sense - alpha and numeric sort differently

 

[XPS35]

Maybe you can try to use a union query as the rowsource. Something like

SELECT ID, CustName FROM tblCustomer WHERE Preferred = TRUE
UNION
SELECT ID, CustName FROM tblCustomer WHERE Preferred = FALSE

 

[Gismo]

I'm rather confused - purchase orders are usually for suppliers, not customers. And this comment



makes no sense - alpha and numeric sort differently

Apologies, I was referring to suppliers

I have 100 products
When I select a supplier, I want to se all 100 products
Lets say I purchase meat and spices
Meat I purchase from Meat Supplier and spices from a supplier in South Africa
When I select the meat supplier, I want to see all 100 products but have all the meat products to the top of the drop down list
When I select the spice supplier, I want to see all 100 products but the spices to the top of the drop down list

In my products table I have pre selected a few items with pre selected supplier preferences

hope this make a bit more sense than the first description

 

[bob fitz]

In my products table I have pre selected a few items with pre selected supplier preferences

Exactly How is that selection made.
Can you post a copy of the db

 

[CJ_London]

When I select the meat supplier, I want to see all 100 products but have all the meat products to the top of the drop down list

why? what is the point of showing products the selected supplier does not supply?

Otherwise a union query along the lines suggested by @XPS35 should work for you

all depends on the fields in your tables, how they relate, etc. Information you have not provided and I'm not going to waste time speculating what they might be

 

[jdraw]

What exactly relates/identifies a Supplier as a Meat Supplier? Past sales/purchases?

Product --->Supplier supplied Product<---Supplier

So somewhere in your setup/design you have a Table along the lines of
This Supplier is a MeatSupplier
This Suppler is a SpiceSupplier
(Supplier is a SupplierType)
Can you show us your tables and relationships?

One of the points to be considered is does any Suppler fit into more than 1 SupplierType?
Why show all Products to any specific SupplierType?

Can you give us more of the business rationale for this proposed set up?

 

[Gismo]

why? what is the point of showing products the selected supplier does not supply?

Otherwise a union query along the lines suggested by @XPS35 should work for you

all depends on the fields in your tables, how they relate, etc. Information you have not provided and I'm not going to waste time speculating what they might be

For instance if you normally purchase bread from one supplier but that day you purchase from the garage, the option to purchase any product from any supplier should be available

 

[Gismo]

Products table
see below

When I select Rogers meat, I would like to see the products from the product table marked as rogers meat preferred supplier on the top of the list before any other product is displayed

When crown national is selected as supplier, I would like to see the spices on the top of the list before any other product from the products table

 

[ebs17]

Targeted sorting:

ORDER BY
    attribute = 55,
    attribute

 

[CJ_London]

When I select Rogers meat, I would like to see the products from the product table marked as rogers meat preferred supplier on the top of the list before any other product is displayed

so it is 3 levels

1st (top) - the products stated as preferred supplier for the supplier selected
2nd - other products of the same type - meat/spice etc
3rd - whatever is left

With regards the 2nd, I don't see anything to indicate product type - 'Raw Products' does not seem to be sufficient to define 'meat' - would have thought Beef Mince was something a meat vendor would supply for example. Or are we supposed to rely on 'beef/lamb/pork/chicken/venison etc to be in the product name?

but then that is inconsistent with

When crown national is selected as supplier, I would like to see the spices on the top of the list before any other product from the products table

So if it's crown national is selected, - same problem as above regards product type but only 2 levels since you don't need the top level - those which are specified as preferred supplier

So here we are, already 12 posts in and still no clear explanation of the requirement. So I'm going to gracefully drop out as it is not possible to provide a more specific answer other than the one posted in #3

 

[Gismo]

so it is 3 levels

1st (top) - the products stated as preferred supplier for the supplier selected
2nd - other products of the same type - meat/spice etc
3rd - whatever is left

With regards the 2nd, I don't see anything to indicate product type - 'Raw Products' does not seem to be sufficient to define 'meat' - would have thought Beef Mince was something a meat vendor would supply for example. Or are we supposed to rely on 'beef/lamb/pork/chicken/venison etc to be in the product name?

but then that is inconsistent with



So if it's crown national is selected, - same problem as above regards product type but only 2 levels since you don't need the top level - those which are specified as preferred supplier

So here we are, already 12 posts in and still no clear explanation of the requirement. So I'm going to gracefully drop out as it is not possible to provide a more specific answer other than the one posted in #3

No just 2 levels
If the supplier selected has a product marked with the same supplier, then to the top
Then any other products

There will be 2 fillets, one raw and one manufactured, when purchasing i only look at raw product, selling would only be manufactured products

 

[Gismo]

Got it working as required

Thank you