problem in inserting records in new design of my database (1 Viewer)

masoud_sedighy

Registered User.
Local time
Today, 04:40
Joined
Dec 10, 2011
Messages
132
Hello​
In previous design of my db as I describe like below I was running 3 append queries [FONT=&quot]regularly[/FONT] (by 1 macro) for inserting records in my tables after extracting data by queries (3 simplequery) from 1 base table that comes from imported excel file in my database.​
Now I have changed design of my tables to the new ones like below and I wanted to use auto number fields as primary and foreign key of tables. Now I do not know how I have to make my append queries from base table and inserting records to these 3 new tables. Specially for table 3 (new) because I do not know docid,tranid?​
Please help.​
Best regards.​
[FONT=&quot]I have 3 tables.[/FONT]​
[FONT=&quot]Table 1:[/FONT]​
[FONT=&quot]Doc no (Primary Key) text (50) [/FONT]​
[FONT=&quot]Title text (100)[/FONT]​
[FONT=&quot]Originator text[/FONT]​
[FONT=&quot]-------------------------------------------------[/FONT]​
[FONT=&quot]Table 2:[/FONT]​
[FONT=&quot]Trans no (Primary Key) text (15)[/FONT]​
[FONT=&quot]Date date[/FONT]​
[FONT=&quot]----------------------------------------------[/FONT]​
[FONT=&quot]Table 3:[/FONT]​
[FONT=&quot]Doc no (Foreign key) text (50)[/FONT]​
[FONT=&quot]Trans no (Foreign key) text (15)[/FONT]​
[FONT=&quot]Size text[/FONT]​

[FONT=&quot]Base table (import from excel file)[/FONT]​
[FONT=&quot] [/FONT]​
[FONT=&quot]Doc no[/FONT]​
[FONT=&quot]Trans no[/FONT]​
[FONT=&quot]Title[/FONT]​
[FONT=&quot]Originator[/FONT]​
[FONT=&quot]Date[/FONT]​
[FONT=&quot]------------------------------------------[/FONT]​
[FONT=&quot] [/FONT]​
[FONT=&quot]New design[/FONT]​
[FONT=&quot] [/FONT]​
[FONT=&quot]Table 1 (new):[/FONT]​
[FONT=&quot]Doc id (Primary Key) auto number[/FONT]​
[FONT=&quot]Doc no text (50) required (yes) no duplicate[/FONT]​
[FONT=&quot]Title text (100)[/FONT]​
[FONT=&quot]Originator text[/FONT]​
[FONT=&quot]--------------------------------------------------------[/FONT]​
[FONT=&quot]Table 2 (new):[/FONT]​
[FONT=&quot]Transid (Primary Key) auto number[/FONT]​
[FONT=&quot]Trans no text (15) required (yes) no duplicate[/FONT]​
[FONT=&quot]Date date[/FONT]​
[FONT=&quot]------------------------------------------------------------[/FONT]​
[FONT=&quot] [/FONT]​
[FONT=&quot]Table 3 (new):[/FONT]​
[FONT=&quot]Doc id (Foreign key) auto number required (yes)[/FONT]​
[FONT=&quot]Trans id (Foreign key) auto number required (yes)[/FONT]​
[FONT=&quot]Doc no text(50) required (yes)[/FONT]​
[FONT=&quot]Trans no text (15) required (yes)[/FONT]​
 

GinaWhipp

AWF VIP
Local time
Today, 07:40
Joined
Jun 21, 2011
Messages
5,901
Before any queries, how are you going to relate these tables? Those FK's in Table 3 won't be updated as an AutoNumber... they would need to be Number (Long) or are you not creating Relationships? And why are you duplicating...

[FONT=&quot]
[FONT=&quot]Doc no text(50) required (yes)[/FONT]
[FONT=&quot]Trans no text (15) required (yes)[/FONT]

...in Table 3 when you can just pull those values via a query when you need them.[/FONT]​
 

Users who are viewing this thread

Top Bottom