Bill -
I'm with John. While it may prevent misspelling of city names during data entry, another way would be to use a combo box with "Limit To List" set to No and let the user select a city or type in a new one.
You could even limit the city combo box if the user filled in the state first and you used that as a filter.
Lookup tables can sometimes be too nit-picky.
Bill
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> 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@yahoogroups.com] On Behalf Of Bill Singer
> Sent: Tuesday, April 10, 2012 4:05 PM
> To: MS_Access_Professionals@yahoogroups.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@yahoogroups.com] On Behalf Of John Viescas
> Sent: Saturday, April 07, 2012 2:40 AM
> To: MS_Access_Professionals@yahoogroups.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@yahoogroups.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>
> 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> , "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]
>
Tidak ada komentar:
Posting Komentar