Custom Function? Another Method?

[Can Of Worms]

I am beating my brains out on this problem...

I regularly have to access a table in excess of 150 million rows (Via Oracle
Software) that I then extract data in groups of 100,000 to 8 million rows to
analyze. Each row has a

 field which determines what I am doing with
the row, and 11 different value fields. 3 of the value fields are Long
Integer Types, the remainder are Doubles. (3 groups of [Quantity](Int) [Cost]
[Retail] and 1 group of [Cost] [Retail])

The problem is that the SQL designers that developed the table and how data
is added to the table have different meanings for each of the 11 fields,
depending on what the [Code] is. So in order to do any level of calculations
for [Quantity] [Cost] or [Retail], I have to pick the RIGHT [Quantity] [Cost]
and [Retail] from each row, based on the value of [Code]. I.E., the value I
need to add to the total sum might be [QuantityA] for [CodeX] and [QuantityB]
for [CodeY] and [CodeZ], etc...

There are 83 possible [Code]s, so nested IIF statements are out of the
question. I initially attempted 2 similar custom functions using Select Case
in VBA (one working with Integers for [Quantity] and the other with Doubles
for [Cost] and [Retail]) but when I finally got it to stop crashing with
errors, it just returned a result of "#error", no matter what I did.

The functions worked, as I could test them via Immediate window, however
when using it in a query to build a field, it kept returning "#error" for
every row.

For example: (80 other codes removed)
I pass the [Code], [QuantityA], [QuantityB], [QuantityC] fields as values
into:

Function ChooseValue(Code As String, QuantityA as Integer, QuantityB as
Integer, QuantityC as Integer) As Integer
Select Case Code
Case "ASTZ"
ChooseValue = QuantityA
Case "RPUJ"
ChooseValue = QuantityC
Case "EKRS"
ChooseValue = QuantityB
Case Else
ChooseValue = 0
End Select
End Function

Is there a better way to do this? Am I missing something?

-CoW

 

[John W. Vinson]

I am beating my brains out on this problem...

I regularly have to access a table in excess of 150 million rows (Via Oracle
Software) that I then extract data in groups of 100,000 to 8 million rows to
analyze. Each row has a

 field which determines what I am doing with
the row, and 11 different value fields. 3 of the value fields are Long
Integer Types, the remainder are Doubles. (3 groups of [Quantity](Int) [Cost]
[Retail] and 1 group of [Cost] [Retail])

The problem is that the SQL designers that developed the table and how data
is added to the table have different meanings for each of the 11 fields,
depending on what the [Code] is. So in order to do any level of calculations
for [Quantity] [Cost] or [Retail], I have to pick the RIGHT [Quantity] [Cost]
and [Retail] from each row, based on the value of [Code]. I.E., the value I
need to add to the total sum might be [QuantityA] for [CodeX] and [QuantityB]
for [CodeY] and [CodeZ], etc...

There are 83 possible [Code]s, so nested IIF statements are out of the
question. I initially attempted 2 similar custom functions using Select Case
in VBA (one working with Integers for [Quantity] and the other with Doubles
for [Cost] and [Retail]) but when I finally got it to stop crashing with
errors, it just returned a result of "#error", no matter what I did.

The functions worked, as I could test them via Immediate window, however
when using it in a query to build a field, it kept returning "#error" for
every row.

For example: (80 other codes removed)
I pass the [Code], [QuantityA], [QuantityB], [QuantityC] fields as values
into:

Function ChooseValue(Code As String, QuantityA as Integer, QuantityB as
Integer, QuantityC as Integer) As Integer
Select Case Code
Case "ASTZ"
ChooseValue = QuantityA
Case "RPUJ"
ChooseValue = QuantityC
Case "EKRS"
ChooseValue = QuantityB
Case Else
ChooseValue = 0
End Select
End Function

Is there a better way to do this? Am I missing something?

-CoW[/QUOTE]

I'd be strongly inclined to quit beating your brains out, and beat out the
alleged brains of the Oracle developers who designed this mess!!!!

Is there some way you can create some (normalized!) Views on the Oracle side?
Do your data subsets necessarily contain a mixture of codes?

About all I can think of is a series of pass-through queries (to make the
Oracle engine do the work); a monster UNION query of 83 individual queries,
one for each code, might be required. And that should certainly be done in
Oracle not Access or your query will run for months.

My sympathy... what a mess!

John W. Vinson [MVP]

 

[Klatuu]

I'm with John on this. The database is a bad design.
One thing I noticed about your function that regardless of code, you return
an integer value. With multiple numeric types to deal with, How about a
Variant?
Wonder if that would help?

 

[Can Of Worms]

Actually, yes this removed the errors and it works correctly. I actually had
built 2 functions, one for working with the integers, one for working with
the doubles, but this was apparently the problem.

Thanks

PS: See my reply to John about my agreement opinions on the table

I'm with John on this. The database is a bad design.
One thing I noticed about your function that regardless of code, you return
an integer value. With multiple numeric types to deal with, How about a
Variant?
Wonder if that would help?
--
Dave Hargis, Microsoft Access MVP

I am beating my brains out on this problem...

I regularly have to access a table in excess of 150 million rows (Via Oracle
Software) that I then extract data in groups of 100,000 to 8 million rows to
analyze. Each row has a

 field which determines what I am doing with
the row, and 11 different value fields. 3 of the value fields are Long
Integer Types, the remainder are Doubles. (3 groups of [Quantity](Int) [Cost]
[Retail] and 1 group of [Cost] [Retail])

The problem is that the SQL designers that developed the table and how data
is added to the table have different meanings for each of the 11 fields,
depending on what the [Code] is. So in order to do any level of calculations
for [Quantity] [Cost] or [Retail], I have to pick the RIGHT [Quantity] [Cost]
and [Retail] from each row, based on the value of [Code]. I.E., the value I
need to add to the total sum might be [QuantityA] for [CodeX] and [QuantityB]
for [CodeY] and [CodeZ], etc...

There are 83 possible [Code]s, so nested IIF statements are out of the
question. I initially attempted 2 similar custom functions using Select Case
in VBA (one working with Integers for [Quantity] and the other with Doubles
for [Cost] and [Retail]) but when I finally got it to stop crashing with
errors, it just returned a result of "#error", no matter what I did.

The functions worked, as I could test them via Immediate window, however
when using it in a query to build a field, it kept returning "#error" for
every row.

For example: (80 other codes removed)
I pass the [Code], [QuantityA], [QuantityB], [QuantityC] fields as values
into:

Function ChooseValue(Code As String, QuantityA as Integer, QuantityB as
Integer, QuantityC as Integer) As Integer
Select Case Code
Case "ASTZ"
ChooseValue = QuantityA
Case "RPUJ"
ChooseValue = QuantityC
Case "EKRS"
ChooseValue = QuantityB
Case Else
ChooseValue = 0
End Select
End Function

Is there a better way to do this? Am I missing something?

-CoW[/QUOTE][/QUOTE]

 

[Can Of Worms]

I 100% agree with you John. I am technically an analyst, but over the past 2
years that I have been learning Access and dealing with data here, I have
been slowly re-working the nightmare of a mess of data that they have up
here. (I even had one 'analyst' cut and paste a Word document into an Excel
spreadsheet cause she thought I wanted the data 'in Excel format')

Changing this table is a goal of mine, but I can't wait for the table to
change to get the analysis results I need.

Your suggestion does re-form my frame of mind, and I think I see a way of
attacking the problem from the Oracle side. Dave's suggestion below got my
function to work, but your suggestion pointed my mind in the correct
direction.

Thanks.