Appending Rounds Decimal to Whole Number

[Jon R]

Using an Iif statement in a Select query, I am allocating unit costs -
dollars and cents to two decimal places - to Year fields - 2001, 2002,
etc. When the query results are ultimately Appended to a table, the
cost is rounded to whole dollars, e.g. 5.75 ends up as 6.00. The
table's data type for this allocated unit cost is Field Size - Long
Interger, Format - Standard, Decimal Places - 4. What am I doing
incorrectly? How can I preserve the decimal formatting in the Append
query? Thank you for any advice.

 

[Brendan Reynolds]

Change the field type to Currency. A long integer by definition does not
include a fractional part. BTW: if you have fields named 2001, 2002, etc.,
you have a serious problem with your database design.

 

[Larry Linson]

Using an Iif statement in a Select query, I am
allocating unit costs - dollars and cents to two
decimal places - to Year fields - 2001, 2002,
etc. When the query results are ultimately
Appended to a table, the cost is rounded to
whole dollars, e.g. 5.75 ends up as 6.00. The
table's data type for this allocated unit cost is
Field Size - Long Interger, Format - Standard,
Decimal Places - 4. What am I doing incorrectly?
How can I preserve the decimal formatting in
the Append query? Thank you for any advice.

An Integer or Long Integer, by definition, has no decimal places.

Format only controls the display, not the definition or storage of the
value. You can display an Integer in a Format that shows decimal places, but
as you've found out, those will be zero.

If you want the decimal fractions to be preserved, you'll have to use a
numeric format that preserves them: Single, Double (both floating point),
Decimal, or Currency.

Larry Linson
Microsoft Access MVP

 

[Jon R]

An Integer or Long Integer, by definition, has no decimal places.

Format only controls the display, not the definition or storage of the
value. You can display an Integer in a Format that shows decimal places, but
as you've found out, those will be zero.

If you want the decimal fractions to be preserved, you'll have to use a
numeric format that preserves them: Single, Double (both floating point),
Decimal, or Currency.

Larry Linson
Microsoft Access MVP

Mr. Reynolds
Will you please elaborate on your comment "serious problem with your
database design?" Can you suggest a "best" way to segregate data in
time periods - including being able to calculate totals for the data
in the time periods. Thank you in advance for the help.

 

[Brendan Reynolds]

For database design, the article at the following URL is a good starting
point, and has links to some further resources ...

http://office.microsoft.com/en-gb/access/HA012242471033.aspx

Segregation of data into time periods or other categories is an issue of
presentation rather than of storage, and better handled using queries and/or
report group sections.

Oh .. and my name is Brendan ... 'Mr Reynolds' is my Dad!

 

[Jon R]

An Integer or Long Integer, by definition, has no decimal places.

Format only controls the display, not the definition or storage of the
value. You can display an Integer in a Format that shows decimal places, but
as you've found out, those will be zero.

If you want the decimal fractions to be preserved, you'll have to use a
numeric format that preserves them: Single, Double (both floating point),
Decimal, or Currency.

Larry Linson
Microsoft Access MVP

Mr Linson
Thank you for your help. You solved my formatting issue.
Jon Rasmussen

 

[Jon R]

For database design, the article at the following URL is a good starting
point, and has links to some further resources ...

http://office.microsoft.com/en-gb/access/HA012242471033.aspx

Segregation of data into time periods or other categories is an issue of
presentation rather than of storage, and better handled using queries and/or
report group sections.

Oh .. and my name is Brendan ... 'Mr Reynolds' is my Dad!

Brendan
Thank you for the reference. I should have stated more clearly that I
am using a query of a table to allocate daily transactions by INV_DATE
to annual periods. Is this approach in keeping with your "using
queries" suggestion?
Jon

 

[Larry Linson]

Thank you for your help. You solved my
formatting issue.

You are welcome. I'm glad I could assist you in getting past that stumbling
block.

But, like Brendan, when you mention "Mr. Linson", I look around to see if my
late father has been temporarily resurrected to vote in some election or
other. <SMILE Whether you believe in miracles or not, that does happen
sometimes.>

Larry Linson
Microsoft Access MVP

 

[Brendan Reynolds]

I don't know enough about your specific scenario to advise you, Jon.
Repeating columns in a query certainly sounds less alarming than repeating
columns in a table, but then you mentioned that this is an append query, so
perhaps those repeating columns do end up in a table. But then again, it
sounds as though this may be a data-warehousing type of operation, and
de-normalized tables are sometimes used in that type of scenario for
performance reasons.

 

[Jon R]

I don't know enough about your specific scenario to advise you, Jon.
Repeating columns in a query certainly sounds less alarming than repeating
columns in a table, but then you mentioned that this is an append query, so
perhaps those repeating columns do end up in a table. But then again, it
sounds as though this may be a data-warehousing type of operation, and
de-normalized tables are sometimes used in that type of scenario for
performance reasons.

Brendan
Thanks for the response. Your response has prompted me to rethink my
approach to the organization of data I have been asked to analyze. I
only have access to the electronic data. To clean up the data, make
part numbers consistent in sales, inventory and purchasing, correct
the formatting, etc. I have used the "Replace" and "Iif" functions
with Delete, Make Table, and Append queries. Group members like you,
Larry Linson and others have helped me with the use of the "Replace"
function, formatting, etc. My level of Access skill is such that it
has gotten too complicated and cumbersome for me given the time
constraints. While Access can probably do it, I am now using Excel to
get the data cleaned up, formatted, etc before I use Access to group,
analyze, etc. I will use this experience to identify areas where I
need to gain more Access knowledge such as data organization and
manipulation and macros to handle repetitive tasks.
Jon