Import to a Lookup Field

[colasam]

Hi folks, greetings from UK.
I am new to forums, and this is my first effort in Access, having failed to grasp it on previous occasions.
I am building a horse racing based database, two of my tables being Courses (details of each track) and Races (Type of race, where run, prize money etc). In my Courses table I have CourseID (autonumber, key) and CourseName (text, max 25). The longest name in the list is 19 characters.
In my Races table, I have CourseID set up as a lookup, related to CourseName in the Courses table. This works fine if I type the CourseName in manually, or select it from the drop-down list. My problem arises when I import it via excel, when the whole CourseID column is deleted, as not the correct data type. I have tried formatting the CourseID column in excel as text, general, and even number prior ti importing, all to no avail.
How should I format this column in excel please? I am getting increasingly frustrated, but am determined to learn Access this time!
Cheers,:banghead:
colasam

 

[Isskint]

first off, welcome

Second, from what you have typed, you have a lookup IN your table. You should not use lookups in tables but at the form level. There are SO MANY problems caused in this way.

Ok, so specifics. The format of the CourseID in both tables is, i assume, a number? The bound column in your dlookup is on the number?
I would suggest changing the lookup to a normal text field (if you have lots of records, add an extra temp column and use an update query to populate the temp column with the course name, then delete the lookup field and change the name of the temp field to the Coursename field). Then when you import from Excel everything should work.

 

[colasam]

Hi Isskint,
Thanks for the welcome and prompt reply.
Being new to Access, I hadn't realised this lookup would cause problems.
You have undoubtedly saved me much grief, as I would have repeated the process many times. I will slowly work through your advice, and hopefully get it to work.
Many thanks, colasam

 

[Isskint]

If you need any specific advice (horse related) just shout. I designed Equibase 15 years ago (updated 3 years ago to EquibaseII) for my wife to store Race results, Course/Track details & data and breeding. It got so big i had to split it (front end/back end). Lots of pitfalls and cul de sacs to be avoided

 

[colasam]

Thanks Isskint,
I'm currently using Raceform Interactive for my analysis, but want to ask more questions than the program allows. Therefore I want to download all their data into my own database, add some data of my own, and analyse it in a different way. I appreciate your offer, and no doubt will get stuck again in the near future. I think my way forward now is to read further into my book (Access 2010, the missing manual).I think I was trying to populate my tables without fully understanding the consequences, albeit on a test basis. If you're interested in horse racing, I hope we keep in touch, and I'll let you know how I'm progressing.
Cheers, colasam