populating a name based on one of three user ids in a table

Ka0ticSt8

New Member
Joined
Mar 5, 2019
Messages
6
Is it possible to populate a field in a table based on the entry of one of three user id's in another field?

I would like to populate the name field in Table 1 that associates to the Identifier based on the information in Table 2.

Table 1

Identifier
name
1234
4321
axl001
dxl001

<tbody>
</tbody>

Table 2
EmpID
UserName
UserCode
Name
1234
axl001
al4321
Al
4321
dxl001
dl1234
Dave

<tbody>
</tbody>

All help welcome.

I'm between a basic and intermediate user of Access.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,129
Office Version
365
Platform
Windows
Welcome to the Board!

I'm between a basic and intermediate user of Access.
So, then are you familiar with relational database design and the rules of normalization?
The rules of normalization include rules for good table design, that say that no field should be dependent upon other fields in that table, and calculations should not be done at the table level.

You should do your "lookup" in a query, by joining the the two tables on the Identifier/EmpID field, and return the fields from each table that you want to display in this query (Identifier form Table 1 and Name from Table 2).
 

Ka0ticSt8

New Member
Joined
Mar 5, 2019
Messages
6
If I were to create a form. Could I input the Identifier in the form but have the form record the name in a table?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,129
Office Version
365
Platform
Windows
Note that you can use Queries as the record source for Forms just as easily as you can Tables.
As matter as fact, you can use Queries as the record source for practically anything that you would also use a Table for.

So, you can create a query between these two tables, and include all the fields from both tables that you want, and use that Query as the Record Source for your Form.
Then all those fields will be available to you on your Form.

Is that what you are looking for?
If not, please explain in more detail the purpose of the Form, and how you would like it to work.
 

Ka0ticSt8

New Member
Joined
Mar 5, 2019
Messages
6
Essentially what I would like to do is enter one of (EmpID,UserName, UserCode) from [Table2] into the "Identifier" field (of a table or form) and have it stored as"Name" from [Table 2]. If that makes sense.


Because I use multiple systems and reports from variousareas I don’t always get the information based on one unique identifier (itcomes in 1 of 3 (EmpID, UserName, UserCode), but I need to collect stats foreach employee based on the work performed.

 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,508
as food for thought:
Code:
select [Name] from Table2 where CStr(EmpID) = '1234'
union
select [Name] from Table2 where UserName = '1234'
union
select [Name] from Table2 where UserCode = '1234'
Although off the bat it would be a problem if there were two "IDs" that are the same in two different columns, such as if Al had UserName axl0003 and Dave had UserCode axl0003 (so that you'd get back Al and Dave both as matches, rather than a single name).

Note that on principle you might want to fill in a primary user id as well as the name and that would possibly ease later querying to. Or build a mapping table just for this purpose (you almost have a mapping table in Table2 but it would be good to have something with two columns, not more than two - primaryID, OtherID).

Edit: More food for thought - an example of a lookup table that you could user for this (lookup any id in the first column and get the primary id in the second column - a big advantage being you could ensure no duplicates in column 1, although if business rules dictate you do need to allow duplicates then so be it but you could still work out a way to handle that using some kind of ordering or prioritizing or even just exception handling for the odd cases where it's a problem):
------------------------------
| IDOfUser | PrimaryIDOfUser |
------------------------------
|     1234 |            1234 |
|     4321 |            4321 |
|   axl001 |            1234 |
|   dxl001 |            4321 |
|   al4321 |            1234 |
|   dl1234 |            4321 |
------------------------------
 
Last edited:

Ka0ticSt8

New Member
Joined
Mar 5, 2019
Messages
6


StatTbl
StatID
CaseID
Role
Mech
Brakes
Oil
Filter
Fluids
Rotation
6
1
Primary
19717
Yes
No
Yes
Yes
No
7
1
EO
13804
No
Yes
No
No
Yes
8
1
MD
D3442
No
Yes
No
No
Yes
9
1
Assist
15048
Yes
No
No
No
No
10
2
Primary
D3442
Yes
No
Yes
No
No
11
2
EO
17097
No
No
Yes
No
Yes
12
2
MD
RJE701
No
Yes
No
Yes
No
13
2
Assist
15048
Yes
No
No
No
Yes
14
3
Primary
13861
No
No
Yes
No
No
15
3
EO
CXB334
No
Yes
No
Yes
No
16
3
MD
D6514
Yes
No
No
No
Yes
17
4
MD, Primary
11272
No
Yes
Yes
Yes
No
18
4
EO
SAG703
Yes
No
No
No
Yes
19
4
Assist
D6514
No
Yes
No
Yes
No
20
5
Primary
SAG703
No
No
Yes
No
No
21
5
EO
15048
No
Yes
No
Yes
No
22
5
MD
D6514
Yes
No
Yes
No
Yes
23
5
Assist
RJE701
Yes
No
No
No
Yes
<thead> </thead> <tbody> </tbody>


MechTbl
EmpID
UserID
AppID
Name
10079
SAG703
ST03015
Stephanie
11272
GGB000
GB00788
Greg
13569
CRB002
CB226503
Christopher
13580
SER124
Susanna
13723
CXP349
CM00046
Christina
13793
CXB334
D6957
Candice
13804
SXB188
Suzanne
13849
CXR331
D6514
Candace
13861
CRW120
Christine
13877
MXD531
MD00041D
Marc
13891
MBL126
Michel
13903
MGN121
Matthew
13985
MES132
D4189
Matt
13998
MXV168
Mark
14106
SMC711
D5073
Samantha
15048
RJE701
D6973
Robert John
16416
SSD002
D4035D
Susan
16938
SXR369
Stephen
17097
RXS171
RS9880
Robert J
19685
SMC002
D5206
Steven
19717
RJN000
D3442
Robert
20787
GGH000
D4396D
Gregg
21072
CCS002
CS00031
Chris
30436
MXG571
MG10583
Michael
35211
RCL130
Samuel
35285
AXD476
AD19988
Adam
35287
SYS105
SS19994
Saman
<thead> </thead> <tbody> </tbody>


Mech1StatQry
Mech
TotBrakes
TotOil
TotFilter
TotFluids
TotRotation
11272
0
1
1
1
0
13804
0
1
0
0
1
13861
0
0
1
0
0
15048
2
1
0
1
1
17097
0
0
1
0
1
19717
1
0
1
1
0
CXB334
0
1
0
1
0
D3442
1
1
1
0
1
D6514
2
1
1
1
2
RJE701
1
1
0
1
1
RJN000
0
1
0
1
0
SAG703
1
0
1
0
1
<thead> </thead> <tbody> </tbody>


Mech1StatQry (SQL)



SELECT DISTINCTROW Format$([StatTbl].[Mech]) AS Mech,Sum(Abs([StatTbl].Brakes)) AS TotBrakes, Sum(Abs([StatTbl].Oil)) AS TotOil,Sum(Abs([StatTbl].Filter)) AS TotFilter, Sum(Abs([StatTbl].Fluids)) ASTotFluids, Sum(Abs([StatTbl].Rotation)) AS TotRotation



FROM StatTbl

GROUP BY Format$([StatTbl].[Mech])

ORDER BY Format$([StatTbl].[Mech]);


I have been attempting what you guys have been tellingme. I changed my database design and it works much better as a whole, howeverI'm still stuck on how calculate the totals for each mechanic because of thedifferent ways to identify them (EmpID, AppID, UserID).



Note: None of EmpID, AppID, or UserID will be the same,they are all unique.




I can’t find a way to group/combine the totals from eachemploy to get one total for each service.




It would even work if I could get to;




Name (or EmpID)

Brakes
Oil
Filter
Fluids
Rotation
EmpID
1
0
1
1
0
AppID
1
1
1
0
1
UserID
0
1
0
1
0
Totals
2
2
2
2
1
<tbody> </tbody>




Or even just the totals by adding EmpID brakes to AppIDbrakes to UserID brakes.


p.s. I have re-evaluated my access ability to basic.

 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,508
I can’t find a way to group/combine the totals from eachemploy to get one total for each service.

Looks like you already did it: see your Mech1StatQry above. I could be wrong but it looks like totals for each service for each employee.
 

Ka0ticSt8

New Member
Joined
Mar 5, 2019
Messages
6
I have the totals, however 19717, RJN000 and D3442 are the same employee and are listed on different rows. The problem is I need the combined totals.

I need a way store in info in the table under 1 identifier or a way to pull a combined query of the 3 identifiers.

I'm more comfortable in excel. I know I cant think of a database as a spreadsheet.

In excel I used IF, INDEX and MATCH statements to achieve this result.

I've been exploring what Xenou suggested with a mapping or lookup table.


If I create a query that changes the MechTbl into 2 columns.

EmpID, EmpID
UserID, EmpID
AppID, EmpID

Could I use a lookup/combo box to store the EmpID.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,508
Based on what we said earlier, two queries on to get the distinct empIDs across different domains (appid, userid, empid), and another that uses the first query to summarize results:


First Query (called Query21 for no particular reason):
Code:
select CStr(EmpID) as ID, EmpID, [Name] from MechTbl where EmpID is not null
union
select UserID as ID, EmpID, [Name] from MechTbl where Nz(UserID, '') <> ''
union
select AppID as ID, EmpID, [Name] from MechTbl where Nz(AppID, '') <> ''
Second Query:
Code:
SELECT 
	t1.Mech,
	t2.EmpID,
	t2.[Name],
	Sum(Abs(t1.Brakes)) AS TotBrakes, 
	Sum(Abs(t1.Oil)) AS TotOil,
	Sum(Abs(t1.Filter)) AS TotFilter, 
	Sum(Abs(t1.Fluids)) AS TotFluids, 
	Sum(Abs(t1.Rotation)) AS TotRotation
FROM
	StatTbl t1
	inner join Query21 t2
	on t1.Mech = t2.ID
GROUP BY 
	t1.Mech,
	t2.EmpID,
	t2.[Name]

Edit: if you want to get down to one records that combines the employees listed under different Mech IDs then you can collapse them all to their unique EmpID (i.e., just remove the grouping on Mech from StatTbl):

Code:
SELECT 
	t2.EmpID,
	t2.[Name],
	Sum(Abs(t1.Brakes)) AS TotBrakes, 
	Sum(Abs(t1.Oil)) AS TotOil,
	Sum(Abs(t1.Filter)) AS TotFilter, 
	Sum(Abs(t1.Fluids)) AS TotFluids, 
	Sum(Abs(t1.Rotation)) AS TotRotation
FROM
	StatTbl t1
	inner join Query21 t2
	on t1.Mech = t2.ID
GROUP BY 
	t2.EmpID,
	t2.[Name]
 
Last edited:

Forum statistics

Threads
1,077,731
Messages
5,335,893
Members
399,056
Latest member
CityGirlLuv

Some videos you may like

This Week's Hot Topics

Top