Entering information once and have it populate multiple columns

[ColoradoJustin via AccessMonster.com]

I have a form that I have to enter the date for Date of Service and have it
store that date in 4 different columns. Right now the way it is set up is, I
have 4 fields in the form that I have to put the same date in and it
populates the following columns: Box24AFrom_1, Box24ATo_1, Box24AFrom_2 and
Box24ATo_2 in the access database. The date is never different between the 4
columns in that row. So my question.... how would I make it easier and just
have to enter the date once and have it populate all 4 columns in that row?

 

[Duane Hookom]

If "The date is never different between the 4 columns in that row" then you
shouldn't need to store it in 4 different columns. Do you have a good reason
for doing this?

 

[ColoradoJustin via AccessMonster.com]

Yes. The data is then exported and it is used to pre-populate a medical form.
So I need the date in 4 locations so that it pre-populates the medical form
in the 4 places.

If "The date is never different between the 4 columns in that row" then you
shouldn't need to store it in 4 different columns. Do you have a good reason
for doing this?

I have a form that I have to enter the date for Date of Service and have it
store that date in 4 different columns. Right now the way it is set up is,

[quoted text clipped - 8 lines]

have to enter the date once and have it populate all 4 columns in that
row?

 

[Duane Hookom]

That's not a real good reason since you can bind four different text boxes
to the same field.

--
Duane Hookom
MS Access MVP

Yes. The data is then exported and it is used to pre-populate a medical
form.
So I need the date in 4 locations so that it pre-populates the medical
form
in the 4 places.

If "The date is never different between the 4 columns in that row" then
you
shouldn't need to store it in 4 different columns. Do you have a good
reason
for doing this?

I have a form that I have to enter the date for Date of Service and have
it
store that date in 4 different columns. Right now the way it is set up
is,

[quoted text clipped - 8 lines]

have to enter the date once and have it populate all 4 columns in that
row?

 

[ColoradoJustin via AccessMonster.com]

The medical form pulls the date from those 4 columns. I can't get rid of them.
I think I understand what you are saying, you are thinking it would be easier
to have the form pull the date from just one column for those 4 fields. The
problem is I can't change the form that is getting pre-populated and I also
can't change the columns. The easiest way for me to do this (if possible) is
to have 1 field on my MS Access form that I enter the date into and it will
populate the 4 columns in the DB. Does that make more sense?

That's not a real good reason since you can bind four different text boxes
to the same field.

Yes. The data is then exported and it is used to pre-populate a medical
form.

[quoted text clipped - 15 lines]

 

[Duane Hookom]

IMHO, you haven't justified the storing the same value more than once. At
the very least, you could create a query that adds the 3 extra columns.

SELECT DateField, DateField as DateField2, DateField As DateField3,
DateField As DateField4,....
FROM tblYourTable;

What do you mean by "can't change the form"? Can't you change the control
sources of text boxes? Do you understand what I am suggesting with setting
the control sources of 4 text boxes to the same field/column?

--
Duane Hookom
MS Access MVP

The medical form pulls the date from those 4 columns. I can't get rid of
them.
I think I understand what you are saying, you are thinking it would be
easier
to have the form pull the date from just one column for those 4 fields.
The
problem is I can't change the form that is getting pre-populated and I
also
can't change the columns. The easiest way for me to do this (if possible)
is
to have 1 field on my MS Access form that I enter the date into and it
will
populate the 4 columns in the DB. Does that make more sense?

That's not a real good reason since you can bind four different text boxes
to the same field.

Yes. The data is then exported and it is used to pre-populate a medical
form.

[quoted text clipped - 15 lines]

have to enter the date once and have it populate all 4 columns in that
row?

 

[John Vinson]

The medical form pulls the date from those 4 columns. I can't get rid of them.
I think I understand what you are saying, you are thinking it would be easier
to have the form pull the date from just one column for those 4 fields.

You are incorrect. It would be *harder*, as you are discovering -
you're having trouble filling the table!

You're confusing data STORAGE with data PRESENTATION. They are *two
different things*.

It is emphatically *not* necessary to store the same date in four
different table fields, to have that date printed out four different
places on a sheet of paper (a Report in Access terms, a "medical form"
to the outside world). Just use four textboxes on the report, all
bound to the same table field.

John W. Vinson[MVP]

 

[ColoradoJustin via AccessMonster.com]

Let me break this down for you. ( I really appreciate the help) This is the
flow of the data that I am talking about.

Access Form --> Access DB --> Excel Spreadsheet --> .txt (Tab Delimited) -->
Non Access Insurance Company Form.

So basically what I am doing is entering the data into the MS Access Form and
that information is getting stored into the columns in the MS Access DB. When
I am done entering the data, I export the DB to excel so that I can do some
record keeping and other things. Then I make a copy and convert it to a text
file (Tab Delimited) and send it to an insurance company. They then use that
text file to automatically populate a claims form. So see I can't just have
the date in 1 column because it is needed 4 times in the txt file to populate
their form correctly. So I was thinking it would be easier to enter the date
once on the MS Access form and have it fill those four columns in the MS
Access DB. I hope this explains it better and I really appreciate the help.

 

[Duane Hookom]

Correction, you don't "export the DB to excel". Apparently you are exporting
the "table" to Excel. As I suggested in my previous post, use a query where
you derive 3 extra columns from the same field in the table. Export the
query to Excel rather than the table.

Trust us. I assure you that John Vinson has been at this for a long, long
time ;-)

 

[ColoradoJustin via AccessMonster.com]

Thanks for your help. I really appreciate it. I don't exactly understand what
you are talking about because I am new to Access. I am not doubting the
knowledge of anyone here and again appreciate the help. I am use to using SQL
and Sybase with actual programs instead of using Access. I am going to try
using a different solution since I am not versed in Access. I was thinking
that this would be an easy thing to do, considering all I want to do is have
1 field in Access update 4 columns in a table instead of just having it
update 1.

Correction, you don't "export the DB to excel". Apparently you are exporting
the "table" to Excel. As I suggested in my previous post, use a query where
you derive 3 extra columns from the same field in the table. Export the
query to Excel rather than the table.

Trust us. I assure you that John Vinson has been at this for a long, long
time ;-)

Let me break this down for you. ( I really appreciate the help) This is
the

[quoted text clipped - 43 lines]

 

[John Vinson]

Thanks for your help. I really appreciate it. I don't exactly understand what
you are talking about because I am new to Access. I am not doubting the
knowledge of anyone here and again appreciate the help. I am use to using SQL
and Sybase with actual programs instead of using Access. I am going to try
using a different solution since I am not versed in Access. I was thinking
that this would be an easy thing to do, considering all I want to do is have
1 field in Access update 4 columns in a table instead of just having it
update 1.

Think of an Access Query as if it were a SQL View.

It works like a table, exports like a table, etc.

Just create a Query based on the table. Include the date field four
times with four different labels. Export THAT to Excel rather than
exporting the table itself.

John W. Vinson[MVP]

 

[ColoradoJustin via AccessMonster.com]

Thank you John. I am going to just create a new form based on php and linked
to a sql server. There is more to all this than just those 4 columns. There
are over 20 columns that get populated with information that doesn't change
at all. So that information is not even on the Access form. Things like
company name, billing address, claims account ID etc. If you want to take a
look at it... I uploaded it to a webspace. hxxp://
www.easyidenmodz.com/Copyof2005Humana.mdb replacing the xx. The table used is
the one that says 2005 Humana. I would really appreciate it. This was given
to me to see if I could solve the problem, but like I said I don't know much
about Access.

Thanks for your help. I really appreciate it. I don't exactly understand what
you are talking about because I am new to Access. I am not doubting the

[quoted text clipped - 4 lines]

1 field in Access update 4 columns in a table instead of just having it
update 1.

Think of an Access Query as if it were a SQL View.

It works like a table, exports like a table, etc.

Just create a Query based on the table. Include the date field four
times with four different labels. Export THAT to Excel rather than
exporting the table itself.

John W. Vinson[MVP]

 

[John Vinson]

Thank you John. I am going to just create a new form based on php and linked
to a sql server. There is more to all this than just those 4 columns. There
are over 20 columns that get populated with information that doesn't change
at all. So that information is not even on the Access form. Things like
company name, billing address, claims account ID etc. If you want to take a
look at it... I uploaded it to a webspace. hxxp://
www.easyidenmodz.com/Copyof2005Humana.mdb replacing the xx. The table used is
the one that says 2005 Humana. I would really appreciate it. This was given
to me to see if I could solve the problem, but like I said I don't know much
about Access.

I'm sorry, but that goes well beyond what I'm comfortable doing on an
unpaid volunteer basis.

The Form is - again - irrelevant. You're not exporting from the Form;
and if you're exporting data from multiple tables (the "constant" and
the "variable" information), that's all the more reason to export from
a Query joining those tables.

John W. Vinson[MVP]

 

[ColoradoJustin via AccessMonster.com]

Thanks. I appreciate your help and opinions.

Thank you John. I am going to just create a new form based on php and linked
to a sql server. There is more to all this than just those 4 columns. There

[quoted text clipped - 6 lines]

to me to see if I could solve the problem, but like I said I don't know much
about Access.

I'm sorry, but that goes well beyond what I'm comfortable doing on an
unpaid volunteer basis.

The Form is - again - irrelevant. You're not exporting from the Form;
and if you're exporting data from multiple tables (the "constant" and
the "variable" information), that's all the more reason to export from
a Query joining those tables.

John W. Vinson[MVP]