John,
Yes!!, I got two numbers. Below is a copy of the SQL just for fun. The next step is to turn it into a Append query, which should be easier for me.
The one thing I need to check is what will happen if some of these parent player associations are already in the table. If a player was in the system last year and has the same parents, the association should be there so I do not want to duplicate it.
I will let you know.
Thanks,
Bill
SELECT t_Players.plaPlaID, t_Parents.parParID
FROM t_Parents INNER JOIN (BBimport INNER JOIN t_Players ON (BBimport.LastName = t_Players.plaLN) AND (BBimport.FirstName = t_Players.plaFN)) ON (t_Parents.parLN = BBimport.ParentLN) AND (t_Parents.parFN = BBimport.ParentFN);
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Bill-
>
> I don't see anything right off the bat. Perhaps a field name is misspelled?
>
> You can do this in the design grid. Put all three tables in the query. Create
> the join between Players and the imported data by draggin the field for first
> name from players and dropping it on the matching field in the import table. Do
> the same thing with last name and address. Link Parents to the import on parent
> first name and last name. Then double-click the player ID in the Players table
> and the parent ID in the Parent table to add them to the fields in the bottom
> part of the grid. If you switch to SQL view, it should look something like the
> SELECT portion of the query below. Go to the Datasheet to see if it returns any
> rows. If it does, then go back to Design and turn it into an Append query,
> specify the target table, and then specify the fields in player-parent-assoc
> that should receive the two values.
>
> 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: Saturday, May 12, 2012 10:47 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Re: Importing Data
>
>
>
>
> John,
> Here is the SQL
>
> 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
>
> Happy Saturday,
> Bill
>
> -----Original Message-----
> From: John Viescas [mailto:JohnV@...]
> Sent: Saturday, May 12, 2012 04:14 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Re: Importing Data
>
> Bill-
>
> If you tried to include a picture of your SQL window, you can't do that on this
> forum. Just copy and paste the SQL text that you've tried so far into a reply.
>
> 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 agent1of6
> Sent: Saturday, May 12, 2012 9:26 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [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]
> >
>
> [Non-text portions of this message have been removed]
>
Tidak ada komentar:
Posting Komentar