Matthew Snook
NW Salmon Database
- Local time
- Today, 22:52
- Joined
- Apr 19, 2001
- Messages
- 133
to all:
My database includes data imported from old Excel files. Since the initial transfer, all data entry has been through Access forms and queries.
On one field, [Individual_Egg_Weight], the imported data were in the form 0.00 where all values were less than one and weights were recorded to the nearest hundredth of a gram (these are salmon eggs).
My Access table shows all data, both imported and directly entered, in that same form: '0.00' and all data looks the same. However, when summary operations are performed in a query, such as 'Sum' or 'Avg', the aggregate function is in error. All of the old data is summed properly, but all of the new direct data is treated as if there were no decimal! So instead of an egg weighing 0.32 grams, they are treated as if the number is 32 grams. Keep in mind that if you look at the number, or even copy and paste it over into Excel, it comes out as '0.32' as it should. But an aggregate query will add '0.32' and '0.34' to get '66' and will average them to '33' instead of '0.33'
What's up with that? The table defines this field as number type 'Double' with decimal set to 'Auto' and an input mask which reads "0."00 - When we enter data, the '0.' is put in place as soon as we enter the '32' so that the input looks like '0.32' when we finally hit the enter button. The number is displayed as '0.32' and copies directly into Excel as '0.32', but cannot be summed properly.
What am I missing?
Thanks,
Matt
My database includes data imported from old Excel files. Since the initial transfer, all data entry has been through Access forms and queries.
On one field, [Individual_Egg_Weight], the imported data were in the form 0.00 where all values were less than one and weights were recorded to the nearest hundredth of a gram (these are salmon eggs).
My Access table shows all data, both imported and directly entered, in that same form: '0.00' and all data looks the same. However, when summary operations are performed in a query, such as 'Sum' or 'Avg', the aggregate function is in error. All of the old data is summed properly, but all of the new direct data is treated as if there were no decimal! So instead of an egg weighing 0.32 grams, they are treated as if the number is 32 grams. Keep in mind that if you look at the number, or even copy and paste it over into Excel, it comes out as '0.32' as it should. But an aggregate query will add '0.32' and '0.34' to get '66' and will average them to '33' instead of '0.33'
What's up with that? The table defines this field as number type 'Double' with decimal set to 'Auto' and an input mask which reads "0."00 - When we enter data, the '0.' is put in place as soon as we enter the '32' so that the input looks like '0.32' when we finally hit the enter button. The number is displayed as '0.32' and copies directly into Excel as '0.32', but cannot be summed properly.
What am I missing?
Thanks,
Matt