Sabtu, 12 Mei 2012

RE: [MS_AccessPros] Re: Importing Data

 

Bill-

I see you didn't include the link on address with Players just as an extra
measure to avoid duplicates. Note also that you must run your player and parent
append queries first in order to find any rows.

To avoid duplicates, try this:

INSERT INTO t_PlayerParentAssoc (ppaPlaID, ppaParID)
SELECT X.plaPlaID, X.parParID
FROM t_PlayerParentAssoc RIGHT JOIN
(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)) As X
ON (t_PlayerParentAssoc.plaPlaID) = X.plaPlaID AND (t_PlayerParentAssoc.parParID
= X.parParID)

That will select only the new rows.

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: Sunday, May 13, 2012 1:15 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Importing Data

 
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]
>

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar