Selasa, 10 April 2012

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