Issue with Integer and Double number format settings

[brendanolear]

I have an access 2010 maketable query that contains column header "Margin: 0" in order to ensure there is always at least a zero in this column, within the created table (Table1), prior to the next step.

I then run an update query on "Margin" column that is populated from (Table 2).

The problem I have is, the 0 within Table1 is set as an Integer but the values from Table 2 are Double containg decimal values.


I have attempted to change Table1 to Double, which works initially however whenever I re-run the make table query to re-create Table1 the settings reverts back to Integer.


How can I ensure the "Double" setting is always retained on this column?

 

[Joe4]

whenever I re-run the make table query to re-create Table1 the settings reverts back to Integer.

This is why I hate Make Table Queries - you don't easily have control over the Format of the fields being created.
I take a different approach.
Set up the table like you want it (this may mean running the Make Table Query ONCE, and then changing the field formats to your liking).
Now, don't use this Make Table Query anymore. Instead do the following:
- Delete all the data out of the Table you have created
- Use an Append Query to write the data to it.

So instead of running a Make Table Query every time, you are simply re-using the same Table (set up the way you like it) by deleting the old data and adding new data via an Append Query.

You can even create a Macro to do it all with a single click.

 

[brendanolear]

Thanks Joe, makes perfect sense and will save me losing my hair.