Field size storage question

[BPBP]

I have 8 number type fields in my table which the field size set to decimal scaled to 2 decimal places. I noticed that it states the storage size for this is 12 bytes.

After using this for 1 month with about 100 over entries. Then later on, i noted there a field size which is single and only takes a storage size of 4 bytes. So i changed all the fieldsize to Single with 2 decimal place) from decimal(i checked the program size before doing this).

I save the table and exit access and checked the program size again. It ended up becoming a few hundred KB bigger? and I thought choosing a smaller field size is supposed to save space?

 

[Uncle Gizmo]

Have you compacted your db?

 

[gemma-the-husky]

and dont worry about size - its all marginal

the issue is that a single will not hold data that is as big as a double or decimal, and will not store real numbers with the same level of precsion

so you are safe to use single ONLY if your data will remain within the bounds of a single datatype.

 

[BPBP]

Will i loose data after compacting? Because I always thought this is something similar to windows defragmentation of the harddisk. I notice sometimes after defragmenting, some files gets lost or pointers to the files get messed up.

I understand that access is only 2G? So i'm trying to save on space. Single should be more then big enough, For these fields the numbers goes up max of 10K and with 2 decimal places only.

 

[LPurvis]

>> 10K and with 2 decimal places only.

Have you considered Currency then?
Don't be confused by the name. Though it's true that Access will then (somewhat annoyingly) try to format that field type as a monetary value through the UI - you can set the default format in the table design to be "General Number" and as you add controls to forms they will be default pick that up too.
But Currency has an ample such range for your requirements, supports up to 4 DP and offfers exact representation and calculation of amounts (no floating point approximation).

Equally, if you're using Access 2007 then the Decimal datatype has been improved in ACE. Making it an excellent choice.

As for the size of your database file, yes internal operations in the FE and data operations in the BE will cause growth without reclaiming space until compacted. Though the compact operation can conceivably go wrong (backup before you do it) it's a very common practice - indeed it's recommended and no it won't delete data. (Unless it fails catastrophically - in which case revert to your backup - and repeat

 

[Uncle Gizmo]

Will i loose data after compacting?

I've never heard this before, please could you direct me to the source of your information.

 

[gemma-the-husky]

i can only repeat - dont worry about the size of a database

for all practical purposes, the 2Gb limit is never an issue.

Just choose the correct datatype for your purpose.

 

[BPBP]

Alright thanks all.