Assign a value based on order of priority

[tim564]

Hi


I need to update a table field based on a set order of priority of another field. Imagine I have the following table:

Customer
Brand
Primary

Customer and Brand are my primary key and I have to assign Primary the value "X" once to any given customer based on a specific order of priority for the value of Brand. The possible values for Brand and the order of priority are: 2,8,4,3 and 13. So if a customer exists in Brands 4, 8 and 13 the value for Primary will be set only against the record that has 8 as the Brand.

How can I program this in Access?

regards

Tim

 

[Adam Caramon]

How can I program this in Access?

In order to get a good answer, I think you're going to have to do a better job of explaining. What do you mean by "so if a customer exists in Brands 4, 8 and 13".

Essentially, provide more details or attach a stripped down version of a database that demonstrates at least the basics of what you're talking about.

 

[tim564]

OK, second time lucky...

I have a table with three fields:

Customer
Brand
Primary

• Customer and Brand together form the primary key.
• Brand can have values 2,3,4, 8 and 13.
• Customer and Brand have values but I have to assign a value "X" to the field "Primary" but based on a specific order of priority for the value in field Brand.
• This order goes 2, 8, 4, 3, 13.

For example:
Customer Brand Primary
110001 4
110001 8 X
110001 13
110002 2 X
110002 3

• In the example, customer 110001 has 3 records as Brand occurs three times. Based on the order of priority that I mentioned, the record where Brand = 8 has to be assigned "X" in the field Primary because 8 comes before both 4 and 13 in the order of priority.
• For customer 110002, of the values it has for Brand the value 2 comes first so this record is assign 'X' in the field Primary.
• So, for each customer Primary is only assigned a value once.

Sorry if this was a bit vague before. I hope this is clearer now.

regards

Tim

 

[tim564]

OK, so the formatting didn't come out for my table example but you should be able to work it out.

 

[plog]

Because Primary is a derived field, I wouldn't store this in a Table, I would create a query and derive the Primary whenever needed. Here's how you do it:

First create a table called 'Brands' that looks like this:

BrandNum, BrandPriority
2, 1
8, 2
4, 3
13, 4

Next, create a sub-query named 'sub_CustomersPrimary' to determine each Customers lowest priority brand. This is that SQL:

SELECT YourTableNameHere.Customer, Min(Brands.BrandPriority) AS PrimaryPriority
FROM YourTableNameHere INNER JOIN Brands ON YourTableNameHere.Brand = Brands.BrandNum
GROUP BY YourTableNameHere.Customer;

Be sure to replace 'YourTableNameHere' with the name of your table. Next create your main query that will bring back the Brand value based on the priority the sub query determined is the primary:

SELECT sub_CustomersPrimary.Customer, Brands.BrandNum AS PrimaryBrand
FROM sub_CustomersPrimary INNER JOIN Brands ON sub_CustomersPrimary.PrimaryPriority = Brands.BrandPriority;

The result of that query will hold just the primary records.