Bill,
I think the key is to make sure you have a unique identifier for every record. This might be a combination of the last and first names with the birthdate.
As stated previously you can then import to a temporary table (or link to Excel) and create append/update queries. You can join in the city and state lookup tables to get the key values.
Duane Hookom
MS Access MVP
--- In MS_Access_Professionals@yahoogroups.com, "Bill Singer" <Bill.Singer@...> wrote:
>
> John,
>
> I have put together a very simple form. I think it is simple, about 6 combo
> boxes. However with 700 records the user was just hoping he could dump a
> spreadsheet into the system. He does not understand and just wants it to be
> easy. His online player registration system can output the data into an
> excel spreadsheet so that is why he has it in excel. I have a copy of that
> output and I would have to convert the data to match the access table. For
> example, the city of Coon Rapids, MN needs to be converted to a FK = 2 to
> indicate Coon Rapids and a FK = 1 to indicate MN.
>
>
>
> I believe I will be able (with the help of this group) to convert the excel
> data for the first year and import it into the database, but the second year
> is what I am working on in my mind. Most of the players the second year
> will be the same ones as the first year. (about 8% turnover). I am trying
> to figure out how to not import duplicate players (based on first name and
> last name). Unfortunately the needs of the database or moving faster than I
> can get through my access books.
>
>
>
> Thanks for your help.
>
> Bill Singer
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
> Sent: Saturday, April 07, 2012 2:40 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Re: Importing Data
>
>
>
>
>
> Bill-
>
> What is the layout of the tables where you want to import the data? How are
> the
> tables related? Perhaps there's a way to define a "special" input form so
> they
> can enter the relevant data all in one line like they can in Excel. You can
> show them that when they're entering data for an existing player, they only
> need
> to enter the registration info. That should get them off Excel.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
> -------------------------------------
>
> From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Duane
> Sent: Saturday, April 07, 2012 5:22 AM
> To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.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]
>
Tidak ada komentar:
Posting Komentar