Duane and Crystal,
I just wanted to let you know that I figured our how to import one large excel table into multiple tables in access.
I created another colume that was like an auto number in the excel spreadsheet that started at the same number as my AutoNumber in Access. I imported the whole table into Access and then as I imported the data into the seperate tables I used that unique number as the FK to keep all my data related. It worked great.
Both of you helped me figure this out, even though at first I did not understand.
Thank you,
Bill
--- In MS_Access_Professionals@yahoogroups.com, "Duane" <duanehookom@...> wrote:
>
> 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