query with insert but how

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mario Krsnic - 12 Feb 2008 22:10 GMT

Hello everybody,
I have two tables with this structure:
table1:
1) ID (Counter + primary
2) lfd_nr (integer)
3) Field1 (Text)

Table2:
1) ID (Integer)
2) lfd_nr (integer)
3) Field1 (Text)
ID from the first and second table are in 1:n Relation

Now I would like to insert the records from the first table where lfd_nr=1
in the same
table with lfd_nr=2. Further I would like to insert the records from the
second table where lfd_nr=1 in the same table with lfd_nr=2 but so that the
ID-field
in new records (lfd_nr=2) corresponds the ID-field in the first table where
lfd_nr=1.
Please help!
Mario

Reply to this Message

John W. Vinson - 13 Feb 2008 00:31 GMT

>Hello everybody,
>I have two tables with this structure:
[quoted text clipped - 18 lines]
>Please help!
>Mario

Why are you storing lfd_nr and field1 redundantly in two different tables?

It's VERY bad design and almost surely not necessary! What are the meanings of
these fields, and what are you trying to accomplish?

Signature

John W. Vinson [MVP]

Reply to this Message

Mario Krsnic - 13 Feb 2008 08:21 GMT

Hello John,

> Why are you storing lfd_nr and field1 redundantly in two different tables?
>
> It's VERY bad design and almost surely not necessary! What are the
> meanings of
> these fields, and what are you trying to accomplish?

Sorry, I expressed my problem bad.
Now I changed the structure to present better my problem.

First table
lfd_nr Name
1 John
2 Mario

second table
Id field1 Lfd_nr
1 "first task" 1
2 "Second task" 1
3 "third task" 1

Third table
Id timeField
1 5:00
1 7:00
1 9:00
2 7:00
2 14:00
3 13:00

lfd_nr is a counter in the first Table. Id is a counter in the second table
Now I would like to insert all tasks of the first
person (Lfd_nr=1) from the second table in this table
for the second person (Lfd_nr=2).
Further I would like to insert all task times from the
tasks of the first persone (tasks 1,2,3) in the third
table for the second person. The problem ist that
inserting tasks in the second table the new records get
new ID-numbers and these must be in the third table corresponding to
to the id-numbers of the second table.

Reply to this Message

John W. Vinson - 14 Feb 2008 05:50 GMT

>Sorry, I expressed my problem bad.
>Now I changed the structure to present better my problem.
[quoted text clipped - 20 lines]
>
>lfd_nr is a counter in the first Table. Id is a counter in the second table

By "counter" I presume you mean Autonumber?

>Now I would like to insert all tasks of the first
>person (Lfd_nr=1) from the second table in this table
>for the second person (Lfd_nr=2).

Create an Append query based on the second table, using the second table
(again) as the output table:

INSERT INTO [Second Table] ([Field1], [Lfd_nr])
SELECT [Second Table].[Field1], (2)
FROM [Second table] WHERE [Lfd_nr] = 2;

It's not clear to me how you'll determine which record to copy *from* and what
Lfd_nr you want to insert.

>Further I would like to insert all task times from the
>tasks of the first persone (tasks 1,2,3) in the third
>table for the second person. The problem ist that
>inserting tasks in the second table the new records get
>new ID-numbers and these must be in the third table corresponding to
>to the id-numbers of the second table.

Again, an append query run after the first query has completed.

Signature

John W. Vinson [MVP]

Reply to this Message