Fill in cell based on value of two columns

[JoJo6205]

Hi all,

Here is my dilemma:

My query has a PO column, which usually has repeating values, and a Priority column, which has either a 1 or 2, which is based on criteria in other columns. The end result should be that if one row has a Priority 1, all rows with that same PO number should be 1.


Example:

Name	Company	PO	Priority	Count	Date 1	Date 2
John	ABC Co	4659	2	0	9/10/14	10/11/14
Becky	That Co	6598	1	4	10/15/14	11/12/14
John	ABC Co	9987	1	3	2/5/15	1/5/15
Sheryl	This Co	4659			9/10/14	10/11/14
Michael	Another Co	6598	2		10/15/14	11/12/14

John and Sheryl have the same PO number but only John has a 2 priority. Sheryl's priority should also be 2. Same with Becky and Michael - they should both have a 1 priority.

This is the criteria to determine if someone gets a 1 or 2:

1. If the count is greater than 0, Priority = 1.
2. If the count = 0 but Date 1 is older than Date 2, Priority = 2.
3. If neither are true, Priority is null.

One additional 'monkey wrench' is that if a PO number has a Priority 1 (and all of the same PO numbers should be 1) but the Count in another row for the same PO number is null and Date 1 is older than Date 2, the Priority reads as 2. I would need to change this to 1.

I was thinking of different ways to write SQL code or creating additional tables to reference duplicates but I just can't figure it out.

Can this be done?

[June7]

Not easily. A calculation that requires data from other records of same table often requires a nested subquery or domain aggregate function or maybe even a custom VBA function. The criteria in your calc is complicated enough that a custom function might be needed.

[JoJo6205]

Ouch! I was afraid I would need something customized. Thank you for your quick response. I guess I have a project.

[Ruegen]

All in VBA

1.Make a new module

2.Write a new public function (although since the function is in a module it should be public anyway)

3.Have the function return an integer of the result you want (so function as an integer).

4.Pass the fields as parameters (date 1, 2, and count - is count a quantity or an actual calculation??) as well as converting null values to 0 using cint() function I would also convert dates to long using clng() for more accuracy

4.Evaluate using IF statements or some SELECT CASE statements

5.Make the function name = result

now you can use it as a custom function in your query of the priority field.

##another way##
For speed I would make a form that goes through each record and fills a priority field with a result.

That means any date over current date (cause that way you only need to do this for things that are current). The downside is you will have to manually run this each time you want to see it correctly so it's not automatic but it will load your form quicker (last resort).