Solved Query returns an Employee Number. I want my Report to list the name instead. How do I do that? (Rookie user) (1 Viewer)

Umpire

Member
Local time
Today, 03:07
Joined
Mar 24, 2020
Messages
120
I have a QA test results Database where the employee enters their name from a dropdown list every time the make an entry. I have a table that has their name and number.
In the database I am only storing their number. I have a query to show how many entries an employee made during an entered date range. I have pop up boxes that ask for the number and the dates.

When I make a report based on that query, I want to display the employee name instead of their number.

Joe Smith is number 5. Insead of the report saying number 5 entered 350 results, I want it to say Joe Smith entered 350 results.

How do I do that?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:07
Joined
Oct 29, 2018
Messages
21,357
Hi. Two ways:

1. Add the names table to your query and then add the name column to the result set, or
2. Change your number Textbox into a Combobox to display the name from the names table.

Hope that helps...
 

Umpire

Member
Local time
Today, 03:07
Joined
Mar 24, 2020
Messages
120
Thank you. That worked. I did find an additional problem however. I have an employee using number 9 and I had 99 for tests by those without their own number. My query was lumping test by 9 and 99 together. I solved it by changing my not assigned number from 99 to 0. Might ot be hte best way but it worked.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:07
Joined
Oct 29, 2018
Messages
21,357
Thank you. That worked. I did find an additional problem however. I have an employee using number 9 and I had 99 for tests by those without their own number. My query was lumping test by 9 and 99 together. I solved it by changing my not assigned number from 99 to 0. Might ot be hte best way but it worked.
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:07
Joined
Feb 19, 2013
Messages
16,553
I have an employee using number 9 and I had 99 for tests by those without their own number. My query was lumping test by 9 and 99 together

sounds like you are using

like '*9*' or similar in your query.

If so, use = 9

0 is better than 99, otherwise what do you do when you have 99 employees? another alternative is to use -1
 

Umpire

Member
Local time
Today, 03:07
Joined
Mar 24, 2020
Messages
120
sounds like you are using

like '*9*' or similar in your query.

If so, use = 9

0 is better than 99, otherwise what do you do when you have 99 employees? another alternative is to use -1
Although a valid point, I highly doubt we will ever have 99 employees doing this job at this location. in 10 years the most we ever had was 7 in the section and only 3 actually did work that the would have used the database had it existed then.

I thought I was using just 9 or just 99 instead of *9* or *99*. Not sure the difference and I just used what Access used. I used the wizards etc to set this up. I have no clue about the VBA behind it. I used Like "*" & [Enter the QA Number of the Tester ] & "*" for the query to ask what number to look for. This was the where criteria.

It was easy enough to manually edit the database to change all my 99 to 0. So now If a person runs a test that is not assigned a number they will use 0. It would be rare. the unassigned was mainly for the historical data of test done before we started using tester numbers.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:07
Joined
Feb 19, 2013
Messages
16,553
It's a mute point since you appear to have 9 or less employees. But if for the sake of argument you have employees 1 to 21, using like *1* will return employees 1, 10,11 ,12 13, 14, 15, 16, 17, 18, 19 and 21

Further, the initial * negates the use of indexing so your query will be slower, although with less than 10 records it won't really be noticeable.

My point is, if you are creating a database application, do take the time to learn to use it properly. If you develop bad habits such as this, you will waste an awful lot of time trying to fix it. The like method is to use with strings, not numbers, although it will work with numbers, it does not make it a good idea.

good luck with your project
 

widemonk

Registered User.
Local time
Today, 10:07
Joined
Jun 16, 2005
Messages
48
Hi,
Also make sure you have chosen the correct data type for this field.
If its set to a Short Text or something else is converting this info in to a String, it may sort the data like this

1
10
11
2
20
21
3
30
31
 

Users who are viewing this thread

Top Bottom