appending data to a table

[JoeFish]

I'm interested in briefly joining several tables together and then when done
separating them into their original tables. I'd like to append like data to
each row for the purpose of defining which table the information comes from.
Is there a way to automatically fill a new field with certain data as it is
created?

Any suggestions?

 

[Mike Painter]

I'm interested in briefly joining several tables together and then
when done separating them into their original tables. I'd like to
append like data to each row for the purpose of defining which table
the information comes from. Is there a way to automatically fill a
new field with certain data as it is created?

Any suggestions?

You can add a field to the "master table" and update it when you append the
other tables.

= "Table A"

If you have several tables with like fields in them you have discovered one
of the reasons it is not a good idea.
They should be in the master table to start and if there is a need to
identify a source "Store A", Store B", etc that should be done at the form
level with a related table called "Stores" in this case.

 

[NetworkTrade]

well let's take this one step at a time:

1. > I'm interested in briefly joining several tables together and then when
done

separating them into their original tables.

*** you may be able to accomplish this via a query. You should be able to
do a simple query including all fields of both tables and get the results you
one. This presumes a common field populated in both tables would align the
rows together...
2. I'd like to append like data to

each row for the purpose of defining which table the information comes from.

*** Not fully understanding what you want...but I get the feeling that if
Table A is 100 records and Table B is 100 records you do not want a new table
with 200 records but instead want a new table of 100 records but for which
the records now have data from both tables.......as noted in #1 you would use
the query to bring two rows of data together from separate tables....There
needs to be something in common between rows of differing tables otherwise
they will not merge as rows but instead append into one table with just a
larger quantity of records

the term "append" in Access world tends to have specific meaning which I'm
not sure is needed. There is an Append Query but I'm not sure if that is
necessary in this case....

3. > Is there a way to automatically fill a new field with certain data as
it is

created?

*** Well this is a whole other ball of wax. A simple answer is yes....but
one has to know alot more about the form in question to give an instruction
on implementing it......

 

[JoeFish]

I guess I should give an example of what i'd like to do with the information
so you can see why i am am asking the question.

I have a financial/member records program which allows me to export member
records into a comma delimited format to be sent to a company for postal
formatting and forwarding information to be appended at the end of the file.

I also have several employee's personal Access databases of which i'd like
to send to this company for processing.

In order to save our company money (processing each file independently would
cost about $70 for each as contrasted against $70 for all of them at once),
I would like to append each database into one table (a process I understand
how to do). But before I can do this, I need to be able to add a field into
each table which defines each row in it as having come from a particular
table/database. Since there are many rows in each separate database, i need
to have the ability to add the same information (i.e., Stephen's Database,
Joseph's Database) to each row in that particular table. Of course, the
main reason for adding this information is to be able to separate each table
out after the information has been processed by an outside company and be
able to seamlessly integrate it back into each appropriate database/program.

I hope you can better unerstand what I am asking in my original question.

Got any better pointers now?

 

[John Vinson]

Since there are many rows in each separate database, i need
to have the ability to add the same information (i.e., Stephen's Database,
Joseph's Database) to each row in that particular table.

No. You DON'T need to store that information redundantly in the source
table!

You can create a UNION query or an Append query using a calculated
field, by typing

DatabaseName: "Stephen's Database"

in a vacant Field cell. This calculated value can then be exported
just as if it were a stored table field.

John W. Vinson[MVP]