Autofill table columns

  • Thread starter Thread starter melody
  • Start date Start date
M

melody

Hi,

Is it possible to use a macro to fill the blank fields in
a column with the data in another column ?

Thanks in advance,
Melody
 
Melody,

Most likely this is not a job for a macro. You will probably need an
action query for this. But if you need more specific help, please
post back with more details, perhaps with an example, of what you are
really trying to do?

- Steve Schapel, Microsoft Access MVP
 
Hi Steve,

Thanks for pointing that out :-) I have a table with
two fields (lets call them field X and field Y) which are
date/time datatypes. For the majority of records, field Y
is null while field X has a non-null value.

What I intended to do was to fill all the null values in Y
with the values in X.

Appreciate any help with this.

Thanks in advance,
Melody
 
Melody,

You can do this with an Update Query. Just put [field X] in the
Update To row of the field Y column in the query.

I must say, though, that doing this is almost certainly unnecessary,
in fact it is almost certainly a bad idea, and is likely based on a
misapprehension of some sort.

- Steve Schapel, Microsoft Access MVP
 
Steve,

May I have an example of such a query ? And why 'this is
almost certainly a bad idea' ? :-)

Thanks in advance,
Melody

-----Original Message-----
Melody,

You can do this with an Update Query. Just put [field X] in the
Update To row of the field Y column in the query.

I must say, though, that doing this is almost certainly unnecessary,
in fact it is almost certainly a bad idea, and is likely based on a
misapprehension of some sort.

- Steve Schapel, Microsoft Access MVP


Hi Steve,

Thanks for pointing that out :-) I have a table with
two fields (lets call them field X and field Y) which are
date/time datatypes. For the majority of records, field Y
is null while field X has a non-null value.

What I intended to do was to fill all the null values in Y
with the values in X.

Appreciate any help with this.

Thanks in advance,
Melody
Click to expand...

.
Click to expand...
 
Melody,

Example:
Make a query based on your table. Make it an Update Query (select
Update from the Query menu in the query design window). Enter FieldY
in the query design grid. In the Update To row of the grid, in the
FieldY column, put [fieldX] and then run the query by clicking the
toolbar button with the red ! icon.

Bad idea:
I can't really say anything very specific about this, because you have
not given any details that we can go on. But by putting the fieldX
data into fieldY, this data is being duplicated, which is never a good
idea in a database, which should always have each piece of information
stored in only one place at a time. I am happy to discuss further if
you would care to explain what the data is all about and what you are
trying to achieve.

- Steve Schapel, Microsoft Access MVP


Steve,

May I have an example of such a query ? And why 'this is
almost certainly a bad idea' ? :-)

Thanks in advance,
Melody

-----Original Message-----
Melody,

You can do this with an Update Query. Just put [field X] in the
Update To row of the field Y column in the query.

I must say, though, that doing this is almost certainly unnecessary,
in fact it is almost certainly a bad idea, and is likely based on a
misapprehension of some sort.

- Steve Schapel, Microsoft Access MVP


Hi Steve,

Thanks for pointing that out :-) I have a table with
two fields (lets call them field X and field Y) which are
date/time datatypes. For the majority of records, field Y
is null while field X has a non-null value.

What I intended to do was to fill all the null values in Y
with the values in X.

Appreciate any help with this.

Thanks in advance,
Melody
Click to expand...

.
Click to expand...
Click to expand...
 
-----Original Message-----
Melody,

Example:
Make a query based on your table. Make it an Update Query (select
Update from the Query menu in the query design window). Enter FieldY
in the query design grid. In the Update To row of the grid, in the
FieldY column, put [fieldX] and then run the query by clicking the
toolbar button with the red ! icon.
Click to expand...

It works. I also added 'is null' as criteria to
prevent the non-null values of Field X from being replaced.
Bad idea:
I can't really say anything very specific about this, because you have
not given any details that we can go on. But by putting the fieldX
data into fieldY, this data is being duplicated, which is never a good
idea in a database, which should always have each piece of information
stored in only one place at a time. I am happy to discuss further if
you would care to explain what the data is all about and what you are
trying to achieve.

- Steve Schapel, Microsoft Access MVP
Click to expand...


Thanks, I appreciate any advice on this situation :-) :

I have a table of invoices with two date/time fields named
VerifiedDT and EffectiveDate. VerifiedDate is the date an
invoice is verified, while EffectiveDate is the data entry
date of an invoice. Whether the value of VerifiedDate and
EffectiveDate is the same or not is pretty much subject to
when the user has the time to key in invoices. This means
that an invoice can be verified and keyed in on the same
date or verified and keyed in on different dates.

The problem was, the use of EffectiveDate was only
introduced a few months ago, meaning that the Effective
Date for all invoice records that were keyed in _prior_ to
that time are null. What I was trying to do was to fill up
all the null values in the EffectiveDate field with
VerifiedDate (since the actual EffectiveDate of 700
records or so are quite impossible to trace).
The reason I needed to do this is so I can query based on
EffectiveDate.

Thanks in advance,
melody
Steve,

May I have an example of such a query ? And why 'this is
almost certainly a bad idea' ? :-)

Thanks in advance,
Melody

-----Original Message-----
Melody,

You can do this with an Update Query. Just put [field
Click to expand...
X]
in the
Update To row of the field Y column in the query.

I must say, though, that doing this is almost certainly unnecessary,
in fact it is almost certainly a bad idea, and is
Click to expand...
likely
based on a
misapprehension of some sort.

- Steve Schapel, Microsoft Access MVP
Click to expand...
wrote:

Hi Steve,

Thanks for pointing that out :-) I have a table with
two fields (lets call them field X and field Y) which are
date/time datatypes. For the majority of records,
Click to expand...
field
Y
is null while field X has a non-null value.

What I intended to do was to fill all the null values
Click to expand...
in
Y
with the values in X.

Appreciate any help with this.

Thanks in advance,
Melody

.
Click to expand...
Click to expand...

.
Click to expand...
 
Melody,

Thanks for the further explanation.

All is forgiven :-) Now that I see what you have done and why, I
agree that it is acceptable. I had assumed you were making a mistake
that many people commonly make, but I was wrong. Best wishes for your
future endeavors with Access.

- Steve Schapel, Microsoft Access MVP
 
Back
Top