How to preserve table design when appending data

Could you give a couple concrete examples of your table field properties vs what you import vs what you expect the result should be.

I'm importing large amounts of weather station data, some of which has been rounded to two decimal places and some which has not. I now have over 1,100,000+ entries and I wanted to achieve uniform formatting. However when I try to change the individual fields in design view I get an error that there is not enough disk space. I had read a workaround is simply to copy the table in design view without the data, amend the individual fields and then append it back in. When I do this though it reverts back to the original format.

This is the desired format

This is what I have in the table and results in no decimal rounding

It's important to understand there is a difference between STORAGE of data, and DISPLAY of data.

Every Single number is stored in some hard-to-understand 4-byte binary format; every Double in 8 bytes. Just let that happen.

When it comes time to display a number, you can apply formatting, e.g. Fixed with 2 decimals. This formatting is usually applied to the control displaying the value.

@Tom

Critical distinction, indeed, and a great explanation!

The important thing to understand, what Tom has tried to explained, is that what is in the stored in the tables vs what is displayed in forms and reports are 2 different things.

Users should never be directly accessing tables, so how it shows there isn't of any consequence.

And what Tom was explaining, is that in your queries, forms, reports, ... you can use the control's Format property to make it display in the format you would like.

So, don't focus on the table, but rather on the forms and reports.

If you are importing regularly from the same source, can you create a linked table to that source?  This can be done with Excel for instance, which in my experience as an environmental planning officer, is a very common porting medium for scientific data.

When importing data I would normally create a link to the source file, and then create an 'append' query based on the linked table, in which I could control, amongst other things, the precision of the data being appended to the operational table.  If, with your data, we take just the first two columns as examples, the 'append' query would be like this to append the values to a precision of two significant decimal places in each case:

INSERT INTO NameOfOperationlTable(precip, rh)
SELECT ROUND(precip,2), ROUND(rh,2)
FROM NameOfLinkedTable
WHERE NOT EXISTS
    (SELECT *
     FROM NameOfOperationalTable
     WHERE NameOfOperationlTable.NameOfPrimaryKey =
                  NameOfLinkedTable.NameOfPrimaryKey);

The use of the EXISTS predicate and correlated subquery in the outer query's WHERE clause ensures that there can be no invalid attempt to import a row which would result in a key violation.

Many thanks for all the responses. As someone who is new to Access, I think it was my flawed understanding of exactly what it does and coming from an Excel background I was probably trying to use it in a similar but effectively larger way....so thank you for clear explanations!

Rarely is Excel data in a format suitable for use directly in a relational database.  You might like to take a look at DecomposerDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo file illustrates how data imported from Excel as a single table can be decomposed into a set of correctly normalized related tables by executing a series of 'append' queries in a specific order.  At each stage of the process a brief explanation of the query being executed is given.

In the the demo the data is first imported into a 'master' table  by means of the TransferSpreadsheet method, but it would work equally well if the data were to be appended into the 'master' table from a linked table, or the linked table could be used instead of a 'master' table, in which case any desired rounding would be done in the 'append' queries.