vba access cry for help

[viceroy1959]

hi,

i have to filter and sort names of people by their clan names. By clan, i mean that each person belongs to two clans: his mother's clan and his father's clan as indicated by his middle name and family name respectively. Suppose i choose to filter out and sort four clans, namely: Smith, Jones, Ford, and Bush. The possible combinations are:
1) (family name=smith), (middle name <>Jones, Ford,or Bush). --smith clan
2) (family name <> jones, ford, or bush), (middle name= smith) --smith clan
3) (family name= Jones), (middle name<>smith, ford, bush).-- jones clan
4) (family name <> smith, ford or bush), (middle name= jones )--jones clan
5) (family name=ford), (middlename <> smith, jones, bush).-- ford clan
6) (family name <> smith, jones or bush), (middle name= ford)-- ford clan
7) (family name= bush), (middlename <> smith, jones, ford).--bush clan
8) (family name<>smith, jones or ford), (middle name= bush )--bush clan
5) (smith-jones) or (jones-smith) combinations --smith-jones clan
6) (smith-ford )or (ford-smith )combination --smith-ford clan
7) (smith-bush) or (bush-smith) combination --smith-bush clan
8) (jones-ford) or (ford-jones) --jones-ford clan
9) (jones-bush) or (bush-jones) --jones-bush clan
10) (ford-bush) or (bush-ford) -- ford-bush clan

can a function be designed to bring out these 10 combinations and assign a number to each combination so that the number will be the basis for the sort order? i was thinking of using the function in a calculated field in a query.

can somebody help me?

[matthewspatrick]

I'm not sure VBA is required here; you can probably get it done in SQL, and any SQL-reliant solution will run much faster than a VBA-reliant solution.

[viceroy1959]

Hi Patrick,

thank you for replying. i will try your suggestion


viceroy

[XLGibbs]

YOu can add the column necessary to contain the number sequence, and run update queries, setting the number and modifying the criteria as you go..

If this is only a one time thing, that would be easiest..in Access anyway. Otherwise, using VBA or SQL solution is likely best.

PS Patrick..... I love that quote in your signature..classic.

[matthewspatrick]

PS Patrick..... I love that quote in your signature..classic.

I aim to please :)

[viceroy1959]

hi xlgibbs,
thank you for your reply too. i tried using a vba function on a calculated field on a query and used this to as a column to sort by. it works!!!