Q: throwing record to a function

[Mark]

Using Access 2000.

Here's the deal:

I have a table that (in addition to other fields) has
about 30 different fields named
minutes1
minutes2
....
minutes10
minutes15
....

Basically, the n in minutes[n] says how many minutes a
customer waited. Now, the value in this field is '1' if it
took that many minutes, or '0' if it didn't.
So, if something took 10 minutes, minutes10 = '1'
and minutes1, minutes2, etc. = '0'

Why they did this is beyond me.

What I gotta do is find how many minutes a customer
waited. In a query, I know I can pass fields to a
function. I'm wondering if I can pass the whole record to
a function. From there, I can programmatically find the
fields and check the values. Can I do this and how? And
if, not, is there a shortcut to doing this rather than
passing a function like
GetMinutes(minutes1, minutes2, ...)
or (gasp!) one hell of a nested iif statement.

Thanks!
-Mark

 

[GreySky]

Why they did this is beyond me.

You're not the only one whose jaw dropped in amazement.


GetMinutes(minutes1, minutes2, ...)

This sounds like your best bet. Of course, you could mask
the values to shorten the number of arguments, and then do
bit-wise comparisons, but in the end, I think your idea
above is the soundest solution to a ridiculous problem.

David Atkins, MCP

 

[Tom Ellison]

Dear Mark:

"Why they did this is beyond me." You got that one right!

How about:

SELECT SomeColumns, 1 AS WaitMinutes
WHERE minutes1 = 1
UNION ALL
SELECT SomeColumns, 2 AS WaitMinutes
WHERE minutes2 = 1
UNION ALL
SELECT SomeColumns, 10 AS WaitMinutes
WHERE minutes10 = 1

add another section for each minutesXX column you have.

The result will then just show the number of minutes for every row,
allowing you to associate that value with the other columns in the
table, what I have called "SomeColumns."

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Mark]

Thanks to both GreySky and Tom for suggestions.

Looks like passing a record from a query into a function
is a no-can-do. I'll probably go with the function that
has 36 arguments (I counted, 36 friggin fields!)

Next thing I'm going to do is smack whoever designed this
table.



-Mark

 

[Van T. Dinh]

Use a PARAM ARRAY in your function declaration.

I wonder what the original designer would do for a customer who waits more
than the 36 specified values?

Actually, I love seeing this sort of database when I see a new client: I
know I can easily convince the client that I am knowledgeable and a contract
is coming my way!

 

[Dale Fye]

Mark,

I agree with Tom, creating a query to PseudoNormalize the data in you
table is a much better option because it will most likely run
significantly faster than passing 36 arguments to a function, and
running that function for each record in a table.

If you want other fields in the query, just include the key fields you
will need to join it back to your main table in your PseudoNormalize
query


--
HTH

Dale Fye


Thanks to both GreySky and Tom for suggestions.

Looks like passing a record from a query into a function
is a no-can-do. I'll probably go with the function that
has 36 arguments (I counted, 36 friggin fields!)

Next thing I'm going to do is smack whoever designed this
table.



-Mark