Great!
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/
(Villefranche-sur-mer, France)
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of agent1of6
Sent: Tuesday, April 17, 2012 2:22 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Importing Data
Jonh, I just thought I would let you know that I made the necessary changes to
my database and removed Some of the Lookup numbers. Since I am in Minnesota and
all the addresses will be Minnesota I just used a default value of "MN". This
will actually be easier than the lookup box.
Thanks for the help.
Bill
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Bill-
>
> Note that the query depends on *real* values being stored in plaCity and
> plaState, not codes. Using lookup numbers for something this trivial is just
> confusing.
>
> 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@yahoogroups.com] On Behalf Of Bill Singer
> Sent: Tuesday, April 10, 2012 9:19 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Re: Importing Data
>
>
> John,
>
> Thanks for the code. I will give it a try in the next few days after I get
> the first table input with the correct associations.
>
> I am also going to work my way through the code to make sure I understand it
> rather than just plugging it in.
>
> I will also work on setting a Unique index on the 3 fields you mentioned.
> That does make sense. I should have already done that.
>
> I did pick up a copy of "SQL Queries for Mere Mortals". Just getting to
> the SELECT statements.
>
> Thanks,
>
> Bill
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
> Sent: Tuesday, April 10, 2012 11:13 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Re: Importing Data
>
> Bill-
>
> It will help to have a Unique index on plaFN, plaLN, and plaDOB to
> facilitate
> doing an update JOIN with t_import. Then you can do:
>
> UPDATE t_import LEFT JOIN t_Players
> ON t_Import.FirstName = t_Players.plaFN And t_Import.LastName =
> t_Players.plaLN
> And t_Import.DateOfBirth = t_Players.plaDOB
> SET t_Players.plaFN = t_Import.FirstName, t_Players.plaLN =
> t_Import.LastName,
> t_Players.plaStreet = t_Import.StreetAddress,
> t_Players.plaState = t_Import.State, t_Players.plaCity = t_Import.City,
> t_Players.plaDOB = t_Import.DateOfBirth;
>
> Note that I blew away your silly ID codes for city and state - they're
> overkill.
> Put the real city and state names in the table, but you can keep a lookup to
> the
> values in your lookup tables to help with spelling errors.
>
> The above queries will update existing rows and insert new rows for any
> players
> not already entered.
>
> You can write some simple code to rumble through the import data after that
> to
> insert rows into the registration table.
>
> 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 Bill
> Singer
> Sent: Tuesday, April 10, 2012 5:35 PM
> To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: RE: [MS_AccessPros] Re: Importing Data
>
> John,
>
> Well over design is not something I have been accused of before. I will
> take that as a complement. J
>
> From the looks of the spreadsheet there is no unique number per player. Just
> a long list of everyone who registered and paid online. I am guessing I
> will be matching player's first name, last name and maybe Date of Birth
> (DOB)
>
> My player table looks like this.
>
> t_Players
>
> plaPlaID PK
>
> plaFN First Name
>
> plaMI Middle Initial
>
> plaLN Last Name
>
> plaDOB Date of Birth
>
> plaStreet Street Address
>
> plaStID FK to t_State
>
> plaCitID FK to t_City
>
> the table that is imported will look something like below , mostly text
> fields.
>
> t_Import
>
> FirstName
>
> LastName
>
> StreetAddress
>
> City
>
> State
>
> DateOfBirth
>
> ParentsName
>
> ParentsPhone
>
> ParentsEmail
>
> SameTeam Yes/No field
>
> So for those players that are already in the system I will just have to add
> a quick record registering them for the next year in the following table.
>
> fpaFpaID PK
>
> fpaPlaID FK to t_players
>
> fpaSeaID FK to t_Season (2012, 2013, etc)
>
> fpaSpoID FK to t_Sport (to indicate which sport they are enrolling
> in)
>
> fpaSameTeam yes/no (to indicate if they want a new team)
>
> Thanks,
>
> Bill
>
> 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 John
> Viescas
> Sent: Tuesday, April 10, 2012 9:36 AM
> To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: RE: [MS_AccessPros] Re: Importing Data
>
> Bill-
>
> It's not normal to build a lookup on city and state. I have built a lookup
> on
> ZIP code that then fetches the default city and state for the selected ZIP.
> You
> may have overdesigned your database.
>
> Is there any key information for the players. For example, does his online
> player registration generate a unique ID for each player? Or do you have
> just
> names to try to match?
>
> It would help to know the layout of your current design.
>
> 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%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Bill
> Singer
> Sent: Tuesday, April 10, 2012 4:05 PM
> To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: RE: [MS_AccessPros] Re: Importing Data
>
> 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%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
> Viescas
> Sent: Saturday, April 07, 2012 2:40 AM
> To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.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>
> <mailto:MS_Access_Professionals%40yahoogroups.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>
> <mailto:MS_Access_Professionals%40yahoogroups.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]
>
> _____
>
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.1913 / Virus Database: 2411/4926 - Release Date: 04/10/12
>
> [Non-text portions of this message have been removed]
>
> _____
>
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.1913 / Virus Database: 2411/4926 - Release Date: 04/10/12
>
> [Non-text portions of this message have been removed]
>
Tidak ada komentar:
Posting Komentar