Query pulling incorrect data

[mrgreen]

Hi All, Awesome Forum hoping some day I can help but until then I'll keep asking dumb questions. I'm pulling data into Excel (2003) from Access (2003) using ADO and the data in Excel is incorrect. I run the Query in Access and I get the correct # when it ends up in Excel it's not correct. I even created a Make Table Query to validate and when I make the Table in Access it's all good when I create it from Excel it's wrong. Can someone please enlighten me?

Here is my connection string and code straight from the Access Query Designer

'Establish Path to database...
struser = LCase(Environ("USERNAME"))
strpath = "C:\Documents and Settings\" & struser & "\Desktop\Work Horse.mdb"
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strpath & ";"
'Open ADO connection
cnt.Open strconn
'Remove tblomr---
strSQL = "DROP TABLE tblomr"
cnt.Execute strSQL
'Make Table Query-----
strSQL = "SELECT QRYBIGKAHOONA.DATEENTERED, QRYBIGKAHOONA.BLINE, QRYBIGKAHOONA.SHIFT, QRYBIGKAHOONA.ACTCASES,"
strSQL = strSQL & " QRYBIGKAHOONA.STDCASES, QRYBIGKAHOONA.ACTSCRAPPERC, QRYBIGKAHOONA.STDSCRAPPERC,"
strSQL = strSQL & " QRYBIGKAHOONA.FOODSAFETYINCIDENTS, QRYBIGKAHOONA.BCDEFECTS, QRYBIGKAHOONA.GAS,"
strSQL = strSQL & " QRYBIGKAHOONA.QUALITYHOLDS, QRYBIGKAHOONA.SAFETYINCIDENTS, QRYBIGKAHOONA.STDTHEOBYSHIFT,"
strSQL = strSQL & " QRYBIGKAHOONA.LINEEFFECIENCY, QRYBIGKAHOONA.STDDTPER, QRYBIGKAHOONA.ACTDTPER,"
strSQL = strSQL & " QRYBIGKAHOONA.PLANOVERWEIGHT, QRYBIGKAHOONA.ACTOVERWEIGHT, QRYBIGKAHOONA.STDSCRAPPOUNDS,"
strSQL = strSQL & " QRYBIGKAHOONA.DRYSCRAPBYSHIFT, QRYBIGKAHOONA.STDDTMINUTES, QRYBIGKAHOONA.ACTDTMINUTES,"
strSQL = strSQL & " QRYBIGKAHOONA.CHGOVER_HOURS, QRYBIGKAHOONA.ACTSALTUSAGEBYSHIFT, QRYBIGKAHOONA.STDSALTBYSHIFT,"
strSQL = strSQL & " QRYBIGKAHOONA.ACTOILUSAGEBYSHIFT, QRYBIGKAHOONA.STDOILBYSHIFT,"
strSQL = strSQL & " QRYBIGKAHOONA.ACTSEASONINGUSAGEBYSHIFT, QRYBIGKAHOONA.STDSEASONINGBYSHIFT INTO tblomr"
strSQL = strSQL & " FROM QRYBIGKAHOONA"
strSQL = strSQL & " WHERE (QRYBIGKAHOONA.DATEENTERED=#4/20/2010#);"
cnt.Execute strSQL

Thanks

 

[Galaxiom]

In what way is the data turning up in Excel "incorrect"?

BTW
Environ("USERPROFILE") will return the path all the way to the current user's folder regardless of the actual location.

 

[mrgreen]

Hey Galaxiom,

I am doing several calculations in my subqueries and when I pull the Plan % number it's right on but when I pull the Actual % it's not good. The numbers aren't matching. For instance I have one entry that should be 0% but it's returning a value of 4.08%. But, when I create a table using the Make Table Query in Access I get the 0% (correct) When I create the table using the SQL script in my previous post I get 4.08% (incorrect). Is it not refreshing all the subqueries when I run it from Excel? Hope this helps and thanks for the reply!

 

[Galaxiom]

I don't know about importing from Access but Excel deals with manually entered numbers into Percentage formatted cells in a very strange way.

If I remember correctly, even though it is a percentage column, a figure of less than one will be interpreted directly as number and then converted to percent. Figures above one are taken to be a percentage.

In your case I expect your zero percent value is actually rounded from 0.0408
Excel decides that you want 4.08 %

Try importing to an ordinary number column then derive another column to show percentages.

 

[mrgreen]

Galaxiom,

Sorry I didn't get back to you sooner. I have corrected the issue but it's hard to say what exactly was causing the problem. I had some rouding issues in a subquery and I also had some joins that weren't quit right. It would be nice to say I know exactly what caused it but at this point I'm happy it's working. Thanks for the knowledge!

 

[Galaxiom]

Always something to watch in Access is the precision of division, even with numbers one would expect to give whole number solutions.

If accuracy matters use the Decimal data type. These are stored as scaled integers and the precision can be controlled.