Using the Lookup function

[jonobugs]

I've been trying to learn Access 2007 for awhile and one item that I have run across a few times is the lookup function.

On the surface, it seems like a wonderful feature because instead of looking at number IDs, you are looking at meaningful data.

However, I have noticed that many people comment (without elaborating) that they really dislike this feature. I'm guessing that it causes problems in some ways or makes like more difficult somehow.

My question is why is this?

I have noticed that my queries sometimes get all messed up with the lookup field section, but I'm not sure why and I don't know how to fix them without rewriting the whole thing.

One example is I have a student query that uses only a student table. The student table has a foreign key called "class" which uses a lookup table to find a class name from another table called classes.

tblStudent
Fname, Lname, ClassID, bday ... etc.

tblClass
Cname, TimeStart, TimeEnd, Day, DateStart, DateEnd ... etc.

In the query I'll have all the fields only from the student table. I would use the lookup function in the Student table to lookup the class name instead of the ID number because it's much easier to figure out the classes, but ocassionally what happens is that I lose the lookup function and I only get the ID numbers in the field.

I'm guessing that somehow the lookup function was taken out by something else and the only way I can get it back is to go back to the student table and use the lookup function again, but I have to delete any table relationships first.

Obviously, I'm new to this whole process so I'd love to hear from anyone who understands what's going on!

 

[arnelgp]

you need not use look up (is dlookup your saying?) , just create a join query.

 

[plog]

One of the hardest things Excel power users have to overcome is the spreadsheet mindset. A database isn't a super spreadsheet, its a whole other more abstract beast.

First you have to normalize your data (https://en.wikipedia.org/wiki/Database_normalization), setting up the correct tables to hold all the information you want to capture. Then instead of using a series of functions to tie all those tables back together, you use queries. Table A connects to Table B via field X, Table B connects to table C via field Y, etc.

That's how you get all the relevant information to show up, not via a lookup function. While there are lookup functions, those only work for one field. Suppose you wanted all the information from Students and Classes together--not just class name. That means 1 lookup function for every class field. Instead, with a JOIN, you connect the tables and all information that's related is easily available.

You only have 2 tables, classes and students with a class id attached to a student. That means a student can only take one class? Is that correct?

 

[jonobugs]

you need not use look up (is dlookup your saying?) , just create a join query.

No, I'm not talking about programming functions. Like I said, I'm a bit of a beginner.

What I was referring to was the lookup function when you use the wizard. In the table design, if you select "lookup" it creates a link to point to the data rather than just the ID number when you use a foreign key.

 

[jonobugs]

Thanks for the encouragement. I'm not an Excel user, but I understand what you mean. I've already looked at how to normalize my data and I believe I did that already. I'm still trying to learn how to create the relationships between the tables with foreign keys, etc., but sometimes it's a bit confusing.

As for my tables, I have more than two tables, but I only listed those as examples. I also have more fields in my tables, but I didn't want to list the fields that had nothing to do with the situation.

For the database, I did actually design it that a student can only be in one class. Right now, that's what we have. However, in the future, I can see that some students could take more than one class, which would mean I would need to create a many to many relationship.

However, for learning purposes, I wanted to figure out why my queries stop working when they worked previously.

Thanks.

 

[MarkK]

Beginners like lookups because once they normalize their data they have all these meaningless key fields everywhere, and understanding their data is hard. Lookups are awesome because they suddenly make the table look meaningful again.

Intermediate users don't look at tables anymore they write and look at queries. And when the queries they are writing run into snags, they go back to the tables trying to find the actual key values, which are now hidden by all those F*%&^ing lookups they wrote when they were beginners.



You can skip a step. Start writing queries right away and put your lookups in your queries, not in your tables.

 

[plog]

However, for learning purposes, I wanted to figure out why my queries stop working when they worked previously.

Can you post your database? And then try and explain any changes you made to your queries or data from the time it worked until now.

 

[jonobugs]

@MarkK...

Yes, you're so right. I thought the lookup field was great, but every time I make a change, something happens and the relationship disappears or something else weird happens and my query no longer works.

To be honest, my database is pretty awful right now because it's my first and I'm learning as I go along. I tried my best to normalize the data, etc., and make relationships, but somewhere along the way I ran into problems and for some reason the table relationships were deleted. I think I might have deleted them manually a long time ago because something wasn't working. Since I used foreign keys and lookup functions it appeared that all my links were still working but when I looked at my database relationship, I was surprised to see that all my links were gone.

In any case, I tried re-established one link and that ended up creating all sorts of havoc with the queries. Since my forms and reports were based on my queries everything came to a crunching halt.

Anyway, I took your advice and created a lookup with a query outside of the table. Is that okay to do, or am I going to run into the same problems again? I got the meaningful names back after I figured out how to make the lookup in a query.

I'm planning to slowly fix the links that were deleted, but I have a feeling it's going to be a lot of work since it wouldn't let me create a link between two tables.

I'd love to post the database for everyone to poke fun at, but unfortunately there is a lot of sensitive data in it (student's id's, etc).

Thanks for the tips! I'll work on it some more.

 

[Minty]

Just Post your empty tables or a picture of the relationships ?

 

[jonobugs]

Thanks for all the support! I think I'll try and work on the relationships a bit first (before I post my database) so I don't waste anyone's time. Now that I've been given a bit of direction I have some ideas on what to do.

 

[jonobugs]

Thanks again for all the help. I wrote a message yesterday, but my browser crashed and I didn't have time to rewrite it. Anyway, I worked out a solution to my problem.

I first deleted the lookup functions within the table itself and created proper links to all the tables. After that, I created a few queries and created a lookup in those. I'm still not sure if that is better or not, but it seems to work.

I guess this problem has been solved, but I'm not sure how to mark this thread as solved...so let this notice suffice.