Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon  
 
Home > Forums > Access
 
Access Forum


Back to Access Forum
 

VLOOKUP for Text Upload Images   Link  
Kent Jamison 
6 months ago
I know Access does not use VLOOKUP (an Excel function). I have also watched the TechHelp video on VLOOKUP. That video explains how to use DMAX and DMIN to look up and calculate letter grades for students. That is great, but I do not require calculations of numbers. Therefore, it does not solve my problem. I need to pair up text data from different (and appropriate) columns/fields found in a table I receive and import every week into Access.  
This imported table has over 40 columns or fields, which makes searching for data cumbersome for the hundreds of employees in our organization. The pertinent fields in this table to my query are:
PositionNumber (a text field for each employee, including each supervisors position number; several numbers begin with zeros and I don't want to TRIM them)
PositionTitle (for each employee)
Name (first and last, for each employee)
ReportsTo (the position number for each employee's supervisor)
What the table does not/not include is the supervisors name (last or first). I want to create a report that identifies the supervisors name for each employee.  
Thoughts? Solutions?
Thanks in advance,
Kent J.
Kevin Robertson
6 months ago
DLookup
Scott Axton
6 months ago

Definitely check out the Search Seminar.

That seminar is GOLD in my opinion and will take your database to the next level.  Much of the seminar requires at least a little bit of programming.  Richard shows you what to do but you will be more comfortable if you progress through the Expert courses first.
Scott Axton
6 months ago
Check out the Self Join Relationships video for how to relate people to people.  
Could be family to family but you can also use it for relating employees to supervisors.

It looks like you are ready to start the Expert Series.  Expert 1 and Expert 2 will go into a lot of what you just asked about  in depth/  Especially normalization of your data to me it more efficient.
Scott Axton
6 months ago
Sorry can't type today.  "to MAKE it more efficient."
Kent Jamison
6 months ago
Still having trouble with this effort.  DLOOKUP doesn't seem to work.  Join relationships only work between tables.  This data is all in the same table, different columns.  I hope this is covered in Expert levels.  Of course, I still need to buy those courses.  Uggh!
Richard Rost
6 months ago
Let's see some screen shots of your tables (hide sensitive data, of course) and exactly what you're trying to look up.
Kent Jamison
6 months ago

Kent Jamison
6 months ago
I've uploaded a fictitious supervisor table to protect the names of the innocent.  I want add a field(column) with Supervisor's name so I can who reports to whom.  Again the real table has hundreds of names, which I have to import regularly, but this one shows what I am trying to do.
Alex Hedley
6 months ago
Create a Query and join ReportsTo on Position#
Kent Jamison
6 months ago
But how do I get Access to tell me what the supervisor's name (a field I haven't yet created) is?  That's what I'm after, the supervisor's name.
Adam Schwanz
6 months ago
You can do the query way, or use DLOOKUP.

Dim R
Dim Supervisor

'this part finds who the supervisor is, note will need to use double double quotes if that is a short text field
R = DLOOKUP("ReportsTo","LooneySupervisorChainT","[Position#]=" & [Position#]

'this part finds the name of the supervisor based on the reports to number we just set to R
Supervisor = DLOOKUP("NameLF","LooneySupervisorChainT","[Position#]=" & R

'this part message boxes the supervisors name, you can also have it assign the value to the field or whatever you want.
Msgbox Supervisor
Adam Schwanz
6 months ago
Likewise you could use DLOOKUP inside of a query, same thing, make an expression

R: DLOOKUP("ReportsTo","LooneySupervisorChainT","[Position#]=" & [Position#]

Then make another expression

Supervisor: DLOOKUP("NameLF","LooneySupervisorChainT","[Position#]=" & [R]
Adam Schwanz
6 months ago
Add a ) to all of those dlookups at the end, just realized I skipped the closing parenthesis, oops.
Richard Rost
6 months ago
Or the query way, which is to join the two tables together: Self Join Relationships
Kent Jamison
6 months ago
OK.  I've tried to work all these solutions.  But much of these solutions require working ACROSS multiple tables, which I don't have in this case, or with the use of COMBO boxes, which I don't want.  My data is imported IN ONE SINGLE TABLE.  I simply want to create a query (or some method) that simply creates an additional column (supervisor's name) in the SAME TABLE I import weekly. I've also tried using the code Adam Schwanz from above (with the additional parenthesis at the end of each string). But I get a syntax error.  Not sure what's wrong there.
I also watched the genealogy videos on Self Join relationships. I found the main video interesting.  The extended cut video was completely confusing. I couldn't tell what the final solution was.  Those two videos show how to join data from a table/query IN A FORM.  Again, I merely want add a column to my existing imported table.  NOT CREATE A FORM.  
Short answer: I'm still no closer to solving this problem.  
Any other ideas?
Kent Jamison
6 months ago
I also get Data mismatch errors, but all my fields are short text.  Can't figure that out either.
Scott Axton
6 months ago
Kent what type of file is the import file you are getting?  Excel, CSV, other?
Are you importing into a temp table or trying to go directly into you main table?
Kent Jamison
6 months ago
Excel. I'm importing it into its own table to keep it from corrupting my other tables.  I pull various fields from it for various other queries, forms, and reports.  But I cannot even get the dummy table above to work.  The syntax seems messed up and I just don't see where the error is.
Scott Axton
6 months ago
Bingo - I figured it was Excel since you indicated they were all text fields. but you were getting type mismatch.
Excel is notorious for trying to "help" you out.  If it thinks a field is a number it will over ride you sometimes.

Try this.  In you Excel file insert a row right under you header row (Row 2). Then in each cell put "xxx" in all the way across.  That forces Excel and Access to both think that the fields are text (whether they are or not).
Once you have in the temp table - verify that all fields are indeed Short Text data type.

Richard Rost
6 months ago
You should still be able to join the Position# and ReportsTo fields if they're both text. I'd take that # out of the field name.
Kent Jamison
6 months ago
So why can't I even get my Looney Toons mock-up table to work (see picture above), which I created from scratch in Access?  It never touched Excel.
Richard Rost
6 months ago
Not sure. It's pretty straight-forward. Make a query. Add your table to it TWICE. Join the Position# to the ReportsTo fields. Voila. Watch the Relationships extended cut too. I would also recommend my Relationship Seminar if you're having trouble with relating tables together.
Kent Jamison
6 months ago
Ok. I switched changed Position# to PositionNumber. Still no joy. I get an error that says "Syntax error (missing operator) in query expression '[PositionNumber]='.  
Current expression is as follows: SupervisorName: DLookUp("NameLF","LooneySupervisorChainT","[PositionNumber]=" & [Results])
Richard Rost
6 months ago
If it's a text field, the criteria needs to be enclosed in quotes. See Double Double.
Richard Rost
6 months ago
And you don't need DLookup if you're joining the tables in a query.
Kent Jamison
6 months ago
My other expression is currently: Results: DLookUp("ReportsTo","LooneySupervisorChainT","[PositionNumber]=" & [PositionNumber])
I don't get anything showing up in the field SupervisorName and Results gives me #Error entries for every record with error "Data type mismatch in criteria expression."
Kent Jamison
6 months ago
There's ONLY ONE TABLE.  I'm NOT JOINING ANYTHING.  I've said this numerous times.
Kent Jamison
6 months ago
I added the Table to my Design Query window TWICE.  Added the join from PositionNumber to ReportsTo.  Now I get an error "The specified '[PositionNumber]' could refer to more than one table listed in the FROM clause of your SQL statement."
Kevin Robertson
6 months ago
If the field name appears in more than one table you need to use the following syntax when you refer to it:

[TableName].[FieldName]

So we can help you more, could you post some screenshots?
Kent Jamison
6 months ago

Kent Jamison
6 months ago
Of course, the position number of supervisors should be repeated for employees.
Kevin Robertson
6 months ago
Like Richard said above, you don't need the DLookups. Remove them and what do you get?
Richard Rost
6 months ago
Kent, take a step back and relax. We're trying to help you. Yes, I know that you only have one table. Did you read what I said?

Add your table to it [the query] TWICE.

Now join them together. Remove your DLookups. Did you watch the Relationships extended cut? I literally show how to do EXACTLY this.

If you don't follow my instructions, then it's useless for me to continue to give them to you.
Richard Rost
6 months ago
I strongly recommend you watch the first 2 Expert classes. They deal with basic relationships, and will give you a good foundation moving forward. A self-join relationship is a lot more advanced.
Kent Jamison
6 months ago
I'm trying to follow instructions guys.  Problem is I've received conflicting instructions.  I only used the DLOOKUPs because Adam Schwanz above said to do so.  So, I'm following instructions.  
And I know you're trying to help me.  THANK YOU!!!!
I've watched all the videos you've mentioned, some multiple times. I just watched the extended cut of the Relationships video.  That looks like the closest to what I'm trying to do. Working through that now.  I've added the same table to my query to my query TWICE, like you said. Creating the join...  
Richard Rost
6 months ago
I understand that. His method isn't wrong... but as we learn more about what it is that you're trying to do, we might think of ways it would be easier for you... and I think joining the same table twice in your query is probably the best bet.
Kent Jamison
6 months ago

Kent Jamison
6 months ago

Kent Jamison
6 months ago
OK. I think I have it figured out kinda...  See 2 new screenshots.  I mimicked what you did Richard in the relationship extended cut video.  I had to manually add a field named SupervisorID.  That works for this dummy table, but will take some time to do each time I import my Excel spreadsheet each week. An automated way to do that would be nice.  (Is that where DLOOKUP would help? )  I also note that SupervisorID and RecordNumber show up as the same number on the far left and far right of the Query datasheet, once run.  Not sure why.  Because they are not the same in the table below.  
So, we're getting there....  THANKS AGAIN!
Any additional thoughts?
Richard Rost
6 months ago
You shouldn't need to add IDs. You should be able to form a relationship directly between your position number and reports to fields, provided they're both text (which they look like). No, DLookup won't help you in this situation, however, if you do want to keep the IDs, then you could use update queries to set them, much like I do in the Yes/No Fields extended cut.
Scott Axton
6 months ago

Kent one of the best things I could recommend to you is to continue on with the courses.  
Please don't take this in the wrong way , but it seems to me that some of what you are trying to do you don't have the base knowledge to do.  
Unless you have Access experience outside of what I'm seeing here, it appears to me that you've gotten into territory that is somewhat over your head,  Things that are covered in the Expert and Developer lessons that are beyond the courses I see that you have.
Richard Rost
6 months ago
Yeah, that's another problem... it's hard for us to sometimes know HOW to answer a question unless we know how much Access you know. Usually I can tell by looking at the courses a student has taken, but you're new to the site and only have the Beginner lessons... and what you're trying to do is well beyond the Beginner levels. I don't get into self-join relationships until much later on.
Kent Jamison
6 months ago
Thanks, guys. My background: I've been working with Access now for abo t two years. I had some basic training from other venues.  Not as good as what you guys have here, though. I do understand relationship basics: one-to-one, one-to-many, many-to-many and joins; inner and outer joins (left and right).  I've set all those up successfully in my Access database.  But I'm not too clear on programming syntax/language. That is new to me.  
In the meantime, I have watched and use many, many TechHelp videos from you, Mr. Rost.  They are great.  Thanks.  I've almost waded through almost all the beginner classes. Only one left. Unfortunately, I have to spend the hours going thru the beginner lessons first (while I do my day job). (I've still learned some additional tricks along the way, though, going thru the beginner lessons.)
I look fwd to the expert courses and learning basics of functions and expressions there. That's where I'm in over my head.
Have a great weekend!
Richard Rost
6 months ago
Keep plugging away. It'll come to you. I've been doing this for almost 30 years, so it's second nature to me... but it's not rocket surgery. Anyone can do it. :)
Scott Axton
6 months ago
Kent above you said, " I mimicked what you did Richard in the relationship extended cut video.  I had to manually add a field named SupervisorID.  That works for this dummy table, but will take some time to do each time I import my Excel spreadsheet each week. An automated way to do that would be nice."
That means you are getting there.

NOW - Would you please tell us a bit about your Excel sheet that you are importing?  Maybe make an exact mock up of that for us to see  - use your fictitious data if you like.  You don't have to put in tons of records. 2-3 would be adequate.

Some questions I have about that:
   1. Are the records you are importing all new? Or...
   2. Are the records you are importing updates of the data already in your table?  Or...
   3. Are the records you are importing a mix of 1. and 2.

Do the records in import file contain the supervisor information?
Are the field names the real field names in your actual table?
Scott Axton
6 months ago
You also said, "This imported table has over 40 columns or fields, which makes searching for data cumbersome for the hundreds of employees in our organization."

How much of that data is repeating?  It may be that you need other tables that relate to your primary table.
Maybe you could just list your fields for us to see. Without data.
Kent Jamison
6 months ago
I hope you all had a great weekend.  
Good news!  I toyed with this more on Friday afternoon and was able to get it to work WITHOUT adding a SupervisorID column.  I simply added the table to the same query TWICE and created a join between them.  SUCCESS!!!  It works!!!  (See latest screen shots.)  

This approach does create some weird naming for the new columns, but that will be irrelevant once I put them into a report where I can put my own labels on the fields.  

Scott, Thanks for the questions. The table I'm importing from Excel includes columns A all the way to AU (47 columns/fields). None of the data fields are the same, nor repeating.  Most of those fields are irrelevant to this quandary.  Many of them I use in other reports and forms.  No issues there.  The main report for this is to show my Department head which updates need to be made to get the company's data system current with our data, which is a constant battle with people coming and going.
Kent Jamison
6 months ago

Kent Jamison
6 months ago

Kent Jamison
6 months ago
Excuse the type-o in my last comment.  The last sentence should read:  "The main ***reason*** for this report...."
Alex Hedley
6 months ago
Have you heard of an Alias?
Kent Jamison
6 months ago
BRILLIANT IDEA!!!!
To your question: Yep. Mr. Rost uses them all the time in his TechHelp videos, in queries, in Design mode. I know they usually begin with a term, followed by a colon, such as "Results: [expression]".  I've seen him use them to create NEW fields, but never retroactively add them to existing fields.  But why not?  I just tried it and voila!  No confusion.  
That's absolutely exciting!!!
THANK YOU!!!
Scott Axton
6 months ago

Scott Axton
6 months ago
Kent if you go to the property sheet for the second instance you can alias the whole table.  That way you don't have to create an alias for each field you bring down to your query.  See Pic above.
Scott Axton
6 months ago

I just got to thinking (I know, dangerous).  The method above will work if you don't mind CustomerT.FirstName and SupervisorT.FirstName
If you want to bring it in so you don't see the table.field you would have to do it the way you did above.
SupervisorFirstName: CustomerT_1.FirstName

Hope I didn't just add a whole bunch of confusion to the mix.
Richard Rost
6 months ago
There you go, Scott, thinking again. :)
Scott Axton
6 months ago
I know Richard gets me into trouble every time.

This thread is now closed. If you wish to comment, start a NEW discussion, below.
 


Back to Access Forum Comments
 

Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 

You may want to read these articles from the 599CD News:

7/5/2022Product Catalog
7/3/2022Access Developer 38
7/1/2022Date Criteria
6/30/2022Sales Chart
6/27/2022Rounding Errors
6/26/2022Calendar Seminar Students Only
6/24/2022Multi Combo One Field
6/23/2022Modal & Popup
6/21/2022Week of Year
6/16/2022Value List Combo
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn