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