Query returning results as string instead of number... why?

[andreas_udby]

I have a calculated query, one of a dozen. They all work correctly except this one, which insists on treating two variables (certSales and certSales _wo) as if they were String instead of Numeric. Here's the SQL statement:

SELECT qrySegment.[Business Segment],
(Nz([HC_Sales],0)) AS certSales,
(Nz([HC_Sales_wo],0)) AS certSales_wo,
IIf([certSales]=0,0,(((Nz([GB_Sales],0))+(Nz([BB_Sales],0))+(Nz([MBB_Sales],0))+(Nz([QL_Sales],0)))/(Nz([certSales],0)))) AS current_certSales,
IIf([certSales_wo]=0,0,(((Nz([GB_Sales_wo],0))+(Nz([BB_Sales_wo],0))+(Nz([MBB_Sales_wo],0))+(Nz([QL_Sales_wo],0)))/(Nz([certSales_wo],0)))) AS current_certSales_wo
FROM ... yada yada.

This statement is identical to the other eleven, except that each queries values from a different set of preceding queries. When I went back and investigated the queries that feed this one, I couldn't find any errors or misattributions.

I've also checked the appropriate columns in the query design grid to see if anything was screwed up in the Field Properties, but no such luck. I'm stumped as to why those two variables are coming back as String values. Anyone have any ideas? Should I delete the query and write it over again?

Thanks,
Andreas

 

[Jon K]

nz() standing alone returns a string. If nz() is involved in a calculation, it returns numeric.

So
Nz([HC_Sales],0) AS CertSales
returns a string.

And both
Nz([HC_Sales],0) + 0 AS CertSales
and
Nz([HC_Sales]) + 0 AS CertSales
return numeric

 

[andreas_udby]

You're kidding... what a pain in the tush. Now I have to go though and error correct 94 queries. Thanks for your help!

 

[David R]

94 queries?

Why so many?

 

[andreas_udby]

It's a mess, but imagine this: a huge datadump is pushed out from an Oracle HR database into a single massive Excel spreadsheet. There are three reports (and 12 subreports) that have to be generated from this spreadsheet using pivot tables of all kinds, , as well as cross-report calculations, etc., ad nauseum. Last month it took two people 30+ hours to generate those reports.

One of them asked me if I knew how to write an Excel macro that would automate the process (uh, huh). So of course I suggested that they do it in Access instead, and now I'm building the Access tool to do it. I've gotten the first two reports done, but the third (and most difficult one) is giving me a run for my money. Seriously, this third report is a crime against humanity, but I'm nearly to the point now where I can get it to run.

I'm proud of the first two reports -- after receiving the initial spreadsheet and uploading it to Access, I can generate them in under two minutes. And all it took was 127 megabytes worth of queries and calculations...

I'm sure that most of the experts on this board could have done it far better, but I'm still learning Access, and, hey -- it works! If only these little things would stop jumping up and biting me in the rear.

 

[David R]

Any chance you can use the Oracle DB tables instead?

If the spreadsheet is so huge because of relational database tables, why not have relational talk to relational, instead of using the middle-man? Granted I don't use Oracle so there may be other considerations I'm not aware of.

Just a couple other thoughts since you seem to have it largely in hand:
Crosstab Queries work a lot like Pivot Tables.
Reports can be opened with a WHERE clause that specifies a criteria just for this instance. You can then open it next time with a different WHERE clause and reuse the basic query.
You can write QueryDefs on the fly and then save them. For example, if you have 12 queries where each one just varies by one criteria, you can use the same query each time except for getting a different criteria.
Hell, you can write and run SQL on the fly without ever saving it (though it's a bit slower running). Still nothing like 30+ hours of course...or it shouldn't be.

I know you've practically got it working so I'm not trying to discourage you. I just remember writing many, many queries that only varied by one factor, then finding out I could use WHERE clauses to consolidate immensely.

Good luck,
David R

 

[andreas_udby]

Crosstabs are still out of my league, but I think that'll be a good thing to read up on this weekend. As for the two DBs talking to each other, none of us has any idea how to connect Access to Oracle; so I just decided to automate the process after the point of data dump.

I know that this thing could have been much, much more streamlined by using honest-to-God tables instead of dozens of queries, but I didn't want the users to have to deal with splitting up the dump into various tables -- I'm not insulting their intelligence, I just figure that the chance for human error is minimized if all they have to do is import the new data and push the appropriate report button.

Thanks for the leads, though; I have a hunch there's more automation projects in my future, and the better I can get at these advanced techniques... well, the better.

 

[David R]

Nah, I think you're on the right track by doing it in queries

Make-Table efforts to get data to work the way you want it to have the side-effect of contributing gloriously to database bloat. Because you're creating and destroying temporary tables as you work, your database will swell in size a dozenfold, or more.

Like I said, I don't work with Oracle data sources; most of our data is either in-house stuff, or at the worst imported as a comma-delimited/fixed-width text file and we go from there. You might post a new topic asking for help with getting tables out of Oracle into Access; I know they're not identical, but they are compatible. It may be as simple as getting your Database Administrator to let you link to the tables in a read-only fashion.

 

[andreas_udby]

I'm so stupid. A couple of my underlying queries had "group by" instead of "where" selected in the Totals block. That's fixed everything. Hey, Thanks for your help!

 

[RV]

>other, none of us has any idea how to connect Access to Oracle<

Don't you guys use SQL*Net or a similar applic?
If you're interested, I can ask around...

RV

 

[andreas_udby]

They've got some tool called WebIntelligence bolted on for querying and reporting. Other than that, I don't know what they have in place.

 

[RV]

Andreas,

ORACLE has online websupport called METALINK.
The url is metalink.oracle.com

Everyone who uses licensed ORACLE applics can ask for registration to METALINK.
I looked on METALINK, there's a lot of info to be found which could be very helpfull to you.

So, see to it that you get registrated!!!

HTH,

RV