Solved

Dlookup

Posted on 2011-09-25
5
405 Views
Last Modified: 2012-05-12
I am using the below on Dlookup to no avaii:

JckRank:  dllookup([Rank], "JckRanks", "AthroughB.[Jockey] = "Ranks.[Jck]")

I am trying to get the ranks of a jockey from a table called JckRanks as below:

Jock           Rank
Smith            1
Gomez          2
Solis             3
Nakatani      4
Velasquez   5

In another Table named AthroughB, there is a column named Jockey.

In this query, I am trying to get the name of the jockey from  table AthroughB and get the rank of that jockey from table JckRanks.  For Example if the jockey name in the column named Jockey from table AthroughB is Nakatani, I want the query to get  the rank  of 4.  There are 1800 named jockeys.
0
Comment
Question by:JackJackson54
  • 2
  • 2
5 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
Comment Utility

So, how about something like:

SELECT AthroughB.*, JckRanks.Rank
FROM AthroughB
LEFT JOIN JckRanks
ON AthroughB.Jockey = JckRanks.Jockey

Normally, I would use in autonumber (ID) field as the primary key of the AthroughB table and use that ID as the foreign key in my JckRanks table.  Trying to join on a name (I assume that [Jockey] field is a name) will be a problem with that many jockeys.
0
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
>I am trying to get the name of the jockey from  table AthroughB and get the rank of that jockey from table JckRanks.

dlookup only returns one value, not two.  For example, you can get a jockey's rank like this:


dlookup([Rank], "JckRanks", "[Jock] = ""Nakatani""")
0
 

Author Comment

by:JackJackson54
Comment Utility
The Jockey name changes from each row.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility

If you don't like my query recommended above, you could try:

SELECT AthroughB.*, DLOOKUP("Rank", "JckRanks", "[Jock] = """ & AthroughB.[Jockey] & """") as JockRank
FROM AthroughB
0
 

Author Comment

by:JackJackson54
Comment Utility
Thanks Fyed, the first one worked and worked great.  I have spent 2 hours trying to get mine to work
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now