update query

[Guest]

I have this as a two step process:

1. I have a make table query that creates blank columns I will fill in
during the next step
2. I want to update those blank columns with an update query.

For the field name, I used (Name: "") to create place holder columns, but
when I create an update query to change Null to "Manager", it doesn't
recognize the Null values.

 

[John Vinson]

I have this as a two step process:

1. I have a make table query that creates blank columns I will fill in
during the next step
2. I want to update those blank columns with an update query.

For the field name, I used (Name: "") to create place holder columns, but
when I create an update query to change Null to "Manager", it doesn't
recognize the Null values.

This sounds like a very awkward approach. MakeTable queries are VERY
rarely necessary; typically you would create the tables you're going
to need at the outset, and add data to them. Could you explain what
these tables are, and why it's necessary to routinely create new
tables rather than using Queries to select data from an existing
table?

To solve the problem with your existing design - be sure that the
field's Allow Zero Length property is False, and initialize the fields
to NULL rather than to "".

John W. Vinson[MVP]

 

[Guest]

I'm creating a large master list of usable contacts. This data may or may
not change from week to week. The data I'm using to start the master list is
being imported from a datasouce that provides the current data in an
"unclean" format (ALL CAPS, sample data that needs to be removed, etc.) The
data from the datasource will change, but the majority of the data will
remain the same. So, I wanted to create a master list in which I'll be able
to clean once and be done with it, but also check it against current data and
add/delete records as necessary...

My steps are as follows:

1. import data from data source.
2. clean the data and make it the "master" list.
3. then in future weeks, import new current data, join to master to
idenitify adds/deletes.
4. remove deletes and append adds to bottom of master.

Hopes this gives you a clear picture of what I'm trying to do.
Thanks for your help.

 

[John Vinson]

I'm creating a large master list of usable contacts. This data may or may
not change from week to week. The data I'm using to start the master list is
being imported from a datasouce that provides the current data in an
"unclean" format (ALL CAPS, sample data that needs to be removed, etc.) The
data from the datasource will change, but the majority of the data will
remain the same. So, I wanted to create a master list in which I'll be able
to clean once and be done with it, but also check it against current data and
add/delete records as necessary...

My steps are as follows:

1. import data from data source.
2. clean the data and make it the "master" list.
3. then in future weeks, import new current data, join to master to
idenitify adds/deletes.
4. remove deletes and append adds to bottom of master.

It sounds like you need one and only one MakeTable query, or none at
all. If you have two tables - the master table and an import table -
then you can set them up at the beginning; when you Import, simply run
a delete query DoCmd.RunSQL "DELETE * FROM tblImport;" to empty the
import table, and import into it. Then do your joining, deleting and
appending.

John W. Vinson[MVP]