Hi Bill,
add a field for your main key (assume long Integer if key is an AutoNumber) to the import table.
1. Update Main key in Import table where records exists
2. Append records that don't
3. update Main key for the newly appended records -- same query as #1 where MainKey Is Null
4. now all the import records should have the Main Key and you can shuffle fields out to your related tables.
If you have more than one main key, do the same process fpr each
Warm Regards,
Crystal
Microsoft MVP, Access
remote training and programming
www.AccessMVP.com/strive4peace
www.YouTube.com/LearnAccessByCrystal
www.YouTube.com/LearnByCrystal
* (: have an awesome day :) *
________________________________
From: Bill Singer <Bill.Singer@at-group.net>
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, April 9, 2012 2:58 PM
Subject: RE: [MS_AccessPros] Re: Importing Data
Duane,
I will try importing all the data into one table and then appending it into
different tables. I am still having a hard time figuring out how to
maintain the integrity of the data as the two tables are related. I will
somehow have to make sure the FK in the second table lines up with the PK in
the first table. I have not yet figured out how to do that as the PK in the
first table will not be created until I append the records. I will have
to add that to my append query somehow so the date in the second table
related.
Thanks,
Bill Singer
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Duane
Sent: Friday, April 06, 2012 10:22 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Importing Data
Bill,
Consider importing into a temporary table where you can use the records to
update or append to multiple tables.
Duane Hookom
MS Access MVP
--- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , "agent1of6"
<Bill.Singer@...> wrote:
>
> I have imported 1 Excel spreadsheet into 1 Access table before. Now I am
being asked if I can import 1 Excel table into two tables. Actually the user
does not care how many tables I have, they just do not want to type the
information in. (700 records, 6 fields per record.) I have two table that
store the data,with a one to many relationship. Can I import through a query
to seperate the data the way it should be seperated?
>
> The first table stores a players name, address etc.
> The second table stores what season the player is registering for.
>
> Over the years one player will register for multiple seasons... which
brings me to the second issue. Next Year I may be asked to import records
for players that are already in the database, How do I prevent that? In that
case i would just want to import the new year they will be registering for.
>
> The data base works better if they inut each person one at a time but I
understand that it is a lot of typing.
>
> Thanks
> Bill
> MN
>
_____
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.1913 / Virus Database: 2409/4918 - Release Date: 04/06/12
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar