ACCESS FEED: [MS_AccessPros] Re: Importing Data

John,
I have been working on this but cannot get past a syntax error in the FROM clause. I have tried to recreate it but the multiple INNER JOINs have me confused. Below is the code I put into the SQL view. When I try to go into design view to see what it is doing I get a syntax error and the curser stops on the dot in "t_Players.plaFN". I cannot see what is wrong and I cannot get into Design view to try to recreate it. Actually I have just been trying to recreate the SELECT portion, I have not added the INSERT INTO portion yet.

Any idea what it is seeing as a syntax error?

Bill

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Bill-
>
> When you have multiple parents, I assume you have duplicate player values.
>
> Try this:
>
> INSERT INTO t_PlayerParentAssoc (ppaPlaID, ppaParID)
> SELECT t_Players.plaPlaID, t_Parents.parParID
> FROM (t_Players INNER JOIN BBimport
> t_Players.plaFN = BBimport.FirstName AND
> t_Players.plaLN = BBimport.LastName AND
> t_Players.plaStreet = BBimport.Address)
> INNER JOIN t_Parents ON
> t_Parents.parFN = BBimport.ParentFN AND
> t_Parents.parLN = Bbimport.ParentLN
>
>
> 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: Monday, May 07, 2012 10:21 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Re: Importing Data
>
>
> John,
>
> These are the columns in my Access table that I imported from the excel
> spreadsheet. In this test there is just one parent named but eventually
> there will be two.
>
> Table name is BBimport
>
> ID PK
>
> FirstName Players First Name
>
> LastName Players Last Name
>
> Address
>
> City
>
> State
>
> Gender
>
> Zip
>
> DOB
>
> GradeID
>
> SeasonID
>
> SportID
>
> ParentFN Parent First Name
>
> PartentLN Parent Last Name
>
> SameTeam
>
> SyncNumber which is a unique number I added to the excel spreadsheet just
> in case I needed it.
>
> I have successfully UPDATEd the
>
> t_Players
>
> plaPlaID PK
>
> plaFN
>
> plaLN
>
> plaGenID FK
>
> plaStreet
>
> plaCity
>
> plaState
>
> plaZip
>
> plaDOB
>
> plaSyncNo
>
> I have successfully UPDATEd the
>
> t_Parents
>
> parParID
>
> parFN
>
> parLN
>
> parHphone
>
> parEmail
>
> parSyncNo
>
> What I have not undated is the junction table.
>
> t_PlayerPartentAssoc
>
> ppaPpaID PK
>
> ppaPlaID FK to t_Players
>
> ppaParID FK to t_Parents
>
> Bill Singer
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
> Sent: Monday, May 07, 2012 11:17 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Re: Importing Data
>
> Bill-
>
> What does your incoming data look like? There should be a way to get the
> junction table updated.
>
> 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: Monday, May 07, 2012 5:35 PM
> To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: RE: [MS_AccessPros] Re: Importing Data
>
> John,
>
> To follow up on a thread that has been silent for a while.
>
> The UPDATE on LEFT JOIN (see below) worked will once I got all my table
> names correct. As I tried to recreate this I updated backwards and blew
> away all my data in my original table. Good thing I now make copies before I
> change things.
>
> One more question if you do not mind. My t_Players in now associated to my
> t_Parents through a junction table called t_PlayerParentAssoc. Many-to-many
> relationship. I used the UPDATE on LEFT JOIN to update the t_Players and
> the t_Parents but I am not sure if I will be able to update the Junction
> table with the association. Is this possible or should I just resign myself
> to the fact that the association in the junction table will have to be done
> manually, which would not be horrible as most of the typing is done.
>
> Thanks for your help.
>
> Bill
>
> Bill Singer
>
> MN
>
> 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 11:13 AM
> To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.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%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 5:35 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,
>
> 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%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: Tuesday, April 10, 2012 9:36 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-
>
> 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%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 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>
> <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%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 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>
> <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%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>
> <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>
> <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>
> <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]
>
> _____
>
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.1913 / Virus Database: 2425/4983 - Release Date: 05/07/12
>
> [Non-text portions of this message have been removed]
>