Hi, I am running a SQL Server 2008 database with an Access 2010 .adp frontend and have two tables:
Table 1: TravelerToDrawing
with columns: Traveler, DrawingNumber
Table 2: Drawings
with columns: DrawingNumber, Description
The column DrawingNumber is the data that links the Traveler column with the Description column between the two tables.
What I would like to do is when I need to enter a slew of new travelers and drawing numbers (the latter of which could have repeats of already existing drawing numbers), I would like to copy and paste them from Excel into Access and have the travelers and drawings populated into their respective tables. The values in the DrawingNumber column in the TravelerToDrawing table gets values from the Drawings table.
In the past I would copy the Drawing Number column from Excel and paste it into the Drawings table which usually generates error messages for duplicate entries that I click out of which results in the duplicates skipped and the unique ones entered. I would then copy the Traveler and Drawing Number columns from Excel and paste it into the TravelerToDrawing table.
This allows me to cross-reference how many travelers have been sent out to make a certain part.
I'm mostly looking for a more streamlined way to enter new travelers and new drawing numbers. I'd like to have one form or query where I paste all my data from Excel and have it populated appropriately in the respective tables.
Curious if anyone has any advice? Sorry it's a really general question. I'm still pretty new to database programming so lots to learn.
Thanks!
Table 1: TravelerToDrawing
with columns: Traveler, DrawingNumber
Table 2: Drawings
with columns: DrawingNumber, Description
The column DrawingNumber is the data that links the Traveler column with the Description column between the two tables.
What I would like to do is when I need to enter a slew of new travelers and drawing numbers (the latter of which could have repeats of already existing drawing numbers), I would like to copy and paste them from Excel into Access and have the travelers and drawings populated into their respective tables. The values in the DrawingNumber column in the TravelerToDrawing table gets values from the Drawings table.
In the past I would copy the Drawing Number column from Excel and paste it into the Drawings table which usually generates error messages for duplicate entries that I click out of which results in the duplicates skipped and the unique ones entered. I would then copy the Traveler and Drawing Number columns from Excel and paste it into the TravelerToDrawing table.
This allows me to cross-reference how many travelers have been sent out to make a certain part.
I'm mostly looking for a more streamlined way to enter new travelers and new drawing numbers. I'd like to have one form or query where I paste all my data from Excel and have it populated appropriately in the respective tables.
Curious if anyone has any advice? Sorry it's a really general question. I'm still pretty new to database programming so lots to learn.
Thanks!
