Make table query changes precision of number field

[jkpe]

I hope someone can help me out here. I am using Access 2002.

I have created a make table query that is also a summary query. One
particular field is giving me problems. I am averaging a numeric field. In
the orginal table, the field size is set to "Decimal," format is "Standard,"
precision is 10, scale is 2, and decimal places is 2.

When I preview the query it looks fine, but if I run the make table query,
the field in question in the resulting table is set to Decimal, format is
blank, precision is 16, scale is 0 and decimal places is "Auto." Scale being
zero, I have lost anything after the decimal place.

If I change the query to a regular select query and export the data it looks
fine. If I build another make table query using the same field and summary
function, the resulting table is fine. I tried deleting the query field and
setting it up again, but nothing changed.

I hope someone has run into this before, as I have been searching and found
nothing relevant. Thanks!

Jessica

 

[Jeff Boyce]

Why are you using a make-table query?

An alternate approach is to set up a table that will "receive" the results
of your query, and define the data types as appropriate. Then simply delete
all (former) rows and append all the new rows.

But then, why even use a table? If your summary query is returning the
values you want, you could export the (results of the) query, rather than
build a table at all.

Regards

Jeff Boyce
<Office/Access MVP>

 

[peregenem]

I have created a make table query that is also a summary query. One
particular field is giving me problems. I am averaging a numeric field. In
the orginal table, the field size is set to "Decimal," format is "Standard,"
precision is 10, scale is 2, and decimal places is 2.

When I preview the query it looks fine, but if I run the make table query,
the field in question in the resulting table is set to Decimal, format is
blank, precision is 16, scale is 0 and decimal places is "Auto." Scale being
zero, I have lost anything after the decimal place.

I can't reproduce. For me, the scale remains the same. My test code:

Sub Test_Access_Jet4()

On Error Resume Next
Kill "C:\Test.mdb"
On Error GoTo 0

Dim cat As Object
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test.mdb"
With .ActiveConnection

' Create test table
.Execute _
"CREATE TABLE Test (" & _
" dec_col DECIMAL(19, 4) NOT NULL);"
.Execute _
"INSERT INTO Test VALUES" & _
" (-0.00026);"

' Make new table based on test
.Execute _
"SELECT AVG(dec_col * 1000) * 1000 AS avg_dec_col," & _
" dec_col INTO MakeTest" & _
" FROM Test GROUP BY dec_col;"

' Show that numeric scale has not changed
Dim rs As Object
Set rs = .OpenSchema(4, _
Array(Empty, Empty, "MakeTest", "avg_dec_col"))
MsgBox "NUMERIC_PRECISION=" & _
rs!NUMERIC_PRECISION & vbCr & _
"NUMERIC_SCALE=" & _
rs!NUMERIC_SCALE & vbCr
End With

End With

End Sub

 

[jkpe]

Well, I have a number of queries based on this query, and performance became
an issue, so I went with a table. I have given up and combined a delete query
and an append query to get the desired results. Thanks for your help!

Jessica