473,320 Members | 1,870 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Change numerical data

max
There is probably an easy answer to this but I can't figure it out. I
have a table with fields of numbers that are 5 or more decimals long.
i.e. 47.19568 I want to round these numbers to 1 decimal. i.e. 47.2
I have tried to create a query and set that field to 1 decimal and when
I look at it in datasheet view, it displays the number I want. The
reason I need to do this is I have another table with numbers to 1
decimal and I need to link these two fields together in a query.
Access is treating 47.2 as the original underlying number and it
returns no results when I run the query. I tried to do a make table,
but the new table still has the original 5 decimal number. Is there a
way to set the original table field properties to automatically change
these to 1 decimal numbers? Or make a new table with just the rounded
numbers? Any help is greatly appreciated.

Oct 3 '06 #1
5 1336
max wrote:
There is probably an easy answer to this but I can't figure it out. I
have a table with fields of numbers that are 5 or more decimals long.
i.e. 47.19568 I want to round these numbers to 1 decimal. i.e. 47.2
I have tried to create a query and set that field to 1 decimal and when
I look at it in datasheet view, it displays the number I want. The
reason I need to do this is I have another table with numbers to 1
decimal and I need to link these two fields together in a query.
Access is treating 47.2 as the original underlying number and it
returns no results when I run the query. I tried to do a make table,
but the new table still has the original 5 decimal number. Is there a
way to set the original table field properties to automatically change
these to 1 decimal numbers? Or make a new table with just the rounded
numbers? Any help is greatly appreciated.
Perhaps you will need accuracy to five decimal places sometime? You
could maintain this and still link the tables using
JOIN ON Round(Table1.FieldName,1) = Table2.FieldName

If you make a new table or change the values in the original table you
may very well run into problems with keeping your data current.

Oct 3 '06 #2
It doesn't usually make much (logical) sense to use a floating point number
as a key or a join field. Identity fields are most often either Integer or
Text. I'd suggest you take a careful look at the design of your database.

Larry Linson
Microsoft Access MVP
"max" <ms****@yahoo.comwrote in message
news:11**********************@e3g2000cwe.googlegro ups.com...
There is probably an easy answer to this but I can't figure it out. I
have a table with fields of numbers that are 5 or more decimals long.
i.e. 47.19568 I want to round these numbers to 1 decimal. i.e. 47.2
I have tried to create a query and set that field to 1 decimal and when
I look at it in datasheet view, it displays the number I want. The
reason I need to do this is I have another table with numbers to 1
decimal and I need to link these two fields together in a query.
Access is treating 47.2 as the original underlying number and it
returns no results when I run the query. I tried to do a make table,
but the new table still has the original 5 decimal number. Is there a
way to set the original table field properties to automatically change
these to 1 decimal numbers? Or make a new table with just the rounded
numbers? Any help is greatly appreciated.

Oct 3 '06 #3
Run an update query that rounds your data; or, without doing damage,
create a view (a select query) on your table where you round the number.

To return just one decimal, I use this expression:

round(yourfield*10+.5)/10

max schreef:
There is probably an easy answer to this but I can't figure it out. I
have a table with fields of numbers that are 5 or more decimals long.
i.e. 47.19568 I want to round these numbers to 1 decimal. i.e. 47.2
I have tried to create a query and set that field to 1 decimal and when
I look at it in datasheet view, it displays the number I want. The
reason I need to do this is I have another table with numbers to 1
decimal and I need to link these two fields together in a query.
Access is treating 47.2 as the original underlying number and it
returns no results when I run the query. I tried to do a make table,
but the new table still has the original 5 decimal number. Is there a
way to set the original table field properties to automatically change
these to 1 decimal numbers? Or make a new table with just the rounded
numbers? Any help is greatly appreciated.
--
Bas Cost Budde
Holland
www.heuveltop.nl/BasCB/msac_index.html
Oct 3 '06 #4
max
Thanks to all who responded.

The JOIN ON Round(Table1.FieldName,1) = Table2.FieldName solution
gives me what I need. Thanks again.
max wrote:
There is probably an easy answer to this but I can't figure it out. I
have a table with fields of numbers that are 5 or more decimals long.
i.e. 47.19568 I want to round these numbers to 1 decimal. i.e. 47.2
I have tried to create a query and set that field to 1 decimal and when
I look at it in datasheet view, it displays the number I want. The
reason I need to do this is I have another table with numbers to 1
decimal and I need to link these two fields together in a query.
Access is treating 47.2 as the original underlying number and it
returns no results when I run the query. I tried to do a make table,
but the new table still has the original 5 decimal number. Is there a
way to set the original table field properties to automatically change
these to 1 decimal numbers? Or make a new table with just the rounded
numbers? Any help is greatly appreciated.
Oct 4 '06 #5

Larry Linson wrote:
It doesn't usually make much (logical) sense to use a floating point number
as a key or a join field. Identity fields are most often either Integer or
Text. I'd suggest you take a careful look at the design of your database.

Larry Linson
Microsoft Access MVP
unless you are querying ranges of numbers for display in a report,
though this doesn't seem to be the case here since the writer is
willing to discard these floating decimals.

Oct 4 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
by: 2mc | last post by:
Generally speaking, if one had a list (from regular Python) and an array (from Numerical Python) that contained the same number of elements, would a While loop or a For loop process them at the...
9
by: Carl | last post by:
I have been using Python for quite some time now and I love it. I use it mainly for explorative computing and numerical prototyping, ie testing and trying out different kinds of algorithms and...
12
by: Pol Bawin | last post by:
Hi All, Did somebody already define attributes for numerical properties to define value : minima, maxima, a number of decimal, ...? This information would be useful to unify syntax Polo
5
by: KR | last post by:
KR Feb 6, 1:48 pm show options Newsgroups: microsoft.public.access.forms From: "KR" <kra...@bastyr.edu> - Find messages by this author Date: 6 Feb 2006 13:48:00 -0800 Subject: Extract Number...
7
by: Giacomo | last post by:
I work on a page structured like: <h2> ... </h2> <div ="div1" class="show"> ... </div> <h2> ... </h2> <div id="div2" class="show"> ... </div> <h2> ... </h2> <div id="div3" class="show">...
23
by: Abhi | last post by:
Hi.. I wanted the C source code in machine readable format for the book "Numerical Recipes in C". I got hold of the pdf version of the book somehow. Does anyone have the complete C code of the...
8
by: mlwerth | last post by:
Dear Access Group: This is the most basic and most embarrassing of questions, but I cannot find where to change the data type of a text field that I have in Access 2003 to a number field. I've...
5
by: ALaurie10 | last post by:
Hi In my database, I use data that is imported XML data. One of the feilds in my table has text data defined with only two values -->"1" and "2". These are text values which have specific...
10
by: Babak | last post by:
Hi, I've developed a C program which contains a large number of vectors and matrices operations. Throughout my code, I used the template from the Numerical Recipes book to define vectors and...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.