add a row from one table to another?

  • Thread starter Thread starter Ynot
  • Start date Start date
Y

Ynot

I am trying to look up one row in one table and display it, if I find it,
add it to another table, is there an easy way to do this? Or an example
someplace?
 
I am trying to look up one row in one table and display it, if I find it,
add it to another table, is there an easy way to do this? Or an example
someplace?
Click to expand...

An Append query will do this... but! Storing the same data in two
different tables is very rarely a good idea. Could you explain what
you're trying to accomplish by doing so?
 
When I add new a row to a table very often many of the columns are the same
and only dates, amount, and locations change. All other columns remain the
same. I want to build a table with rows made up of the standard information
so that it can be selected, and appended to the new table then only the
additional columns need to be filled in. If the append could pop up a copy
of the form I use now to add to the table that would be great.
 
When I add new a row to a table very often many of the columns are the same
and only dates, amount, and locations change. All other columns remain the
same. I want to build a table with rows made up of the standard information
so that it can be selected, and appended to the new table then only the
additional columns need to be filled in. If the append could pop up a copy
of the form I use now to add to the table that would be great.
Click to expand...

I'll make suggestions on two levels.

First, you could have a command button on your Form "Get Template".
This button could run an append query to append a (actually the only!)
record from a Template table to the form's table. You can create and
save the query, and run it from a Macro or code behind the button, and
then move to the newly created record.

On the second level... if you have a great many records with several
fields duplicated, I wonder if you should actually have two tables in
a one-to-many relationship? That way you could have one table with the
common information, and a second table (which you could display and
edit on a Subform) with the dates, amount and location. Relational
databases are all about getting rid of redundancy after all, and you
appear to be storing a lot of information redundantly!
 
I Think your first idea fits well with what I am trying to do. I would have
to do something like a get template, and have that bring up another for to
select the appropriate template. There are many of them. Once selected I
could run the append query. Does that sound feasible, and any insight where
I might look for an example of that?

On another note, I understand your recommendation of 2 tables so fields are
not duplicated but even the templates change, not frequently but somewhat
frequently and they can only have impact from that time forward.

Thanks again for the help Anyplace I can see an example of what we
discussed is a big help. I learn better by seeing something and figuring
out how it works than trying to make it work in the first place.
 
I Think your first idea fits well with what I am trying to do. I would have
to do something like a get template, and have that bring up another for to
select the appropriate template. There are many of them. Once selected I
could run the append query. Does that sound feasible, and any insight where
I might look for an example of that?
Click to expand...

Well... the online help for Append, for one place. Since I know
nothing about your table structure nor what criteria would be used to
select a template, any suggestion I could give would probably be
rather remote from what you need!

Basically, you'ld create an Append query based on the template table,
with whatever criteria you need to select the target table; the query
would append to the target table. The SQL would be something like

INSERT INTO targettable(field, field, field...)
SELECT field, field, field...
FROM templatetable
WHERE <criteria>;
 
Back
Top