problem in inserting records in new design of my database

[masoud_sedighy]

Hello

In previous design of my db as I describe like below I was running 3 append queries for inserting records in my tables after extracting data by queries (3 simple query) 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.

I have 3 tables.

Table 1:

Doc no (Primary Key) text (50)

Title text (100)

Originator text

-------------------------------------------------

Table 2:

Trans no (Primary Key) text (15)

Date date

----------------------------------------------

Table 3:

Doc no (Foreign key) text (50)

Trans no (Foreign key) text (15)

Size text

Base table (import from excel file)

Doc no

Trans no

Title

Originator

Date

------------------------------------------

New design

Table 1 (new):

Doc id (Primary Key) auto number

Doc no text (50) required (yes) no duplicate

Title text (100)

Originator text

--------------------------------------------------------

Table 2 (new):

Transid (Primary Key) auto number

Trans no text (15) required (yes) no duplicate

Date date

------------------------------------------------------------

Table 3 (new):

Doc id (Foreign key) auto number required (yes)

Trans id (Foreign key) auto number required (yes)

Doc no text(50) required (yes)

Trans no text (15) required (yes)

[June7]

I hope you aren't saying the Foreign key fields are autonumber, but are indicating that the related primary key is an autonumber.

Using autonumber as primary/foreign key is an issue with importing related records. I faced this with an application and abandoned autonumber as primary/foreign keys because of problems. However, my case is that the import would have been from another Access file (project site copy of the headquarters master) where records where already in related tables using autonumber. The import would lose the relationships.

In your case the records originate from a single table and you are separating them in the import. Include in the import to tables one and two a common record ID that can be used to link the two tables in a query that has this ID and the autonumber values from each table. Use this query as basis for select and insert of records to table 3.

Why do you feel the need to do this? Why not stay as is? Why have both autonumber foreign keys and the doc no and trans no in table 3? Need one pair or the other, not both.

[masoud_sedighy]

Thanks for your comment about table3. I will correct that.

Actually some of my queries are slow and my primary key in tables are text that some times are between 30-50 character. So I like to repair the slow queries and I do not know this is for bad design of queries or tables.

if changing design of tables does not have good effect on speed of queries, maybe design of queries has problem, and I have to repair them.

Please help about the problem.

Best regards.

[June7]

I have worked with databases that have long unique ID, not as long as 30-50 but maybe 15.

Have you set Index (Yes no duplicates)? I have doubts that the autonumber key will be faster but could try.

Would have to examine data and queries to analyze further.