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

K

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="class: grid, width: 102"]
<tbody>[TR]
[TD="width: 72, bgcolor: transparent"]Table 1

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Identifier
[/TD]
[TD="bgcolor: transparent"]name
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1234
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4321
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]axl001
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]dxl001
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Table 2
[/TD]
[TD="width: 74, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]EmpID
[/TD]
[TD="bgcolor: transparent"]UserName
[/TD]
[TD="bgcolor: transparent"]UserCode
[/TD]
[TD="bgcolor: transparent"]Name
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1234
[/TD]
[TD="bgcolor: transparent"]axl001
[/TD]
[TD="bgcolor: transparent"]al4321
[/TD]
[TD="bgcolor: transparent"]Al
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4321
[/TD]
[TD="bgcolor: transparent"]dxl001
[/TD]
[TD="bgcolor: transparent"]dl1234
[/TD]
[TD="bgcolor: transparent"]Dave
[/TD]
[/TR]
</tbody>[/TABLE]

All help welcome.

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

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
Welcome to the Board!

I'm between a basic and intermediate user of Access.
Click to expand...
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).
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.

 
Upvote 0
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:
Upvote 0


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


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


Mech​
TotBrakes​
TotOil​
TotFilter​
TotFluids​
TotRotation​
<thead> [TD="colspan: 6"]
Mech1StatQry​
[/TD]
</thead> <tbody> [TD="bgcolor: transparent"] 11272 [/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"] 13804 [/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"] 13861 [/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"] 15048 [/TD]
[TD="bgcolor: transparent"]
2​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"] 17097 [/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"] 19717 [/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"] CXB334 [/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"] D3442 [/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"] D6514 [/TD]
[TD="bgcolor: transparent"]
2​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
2​
[/TD]
[TD="bgcolor: transparent"] RJE701 [/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"] RJN000 [/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"] SAG703 [/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
0​
[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
</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;



<tbody> [TD="width: 534, bgcolor: transparent, colspan: 6"] Name (or EmpID) [/TD]
[TD="width: 89, bgcolor: transparent"] [/TD]
[TD="width: 89, bgcolor: transparent"] Brakes [/TD]
[TD="width: 89, bgcolor: transparent"] Oil [/TD]
[TD="width: 89, bgcolor: transparent"] Filter [/TD]
[TD="width: 89, bgcolor: transparent"] Fluids [/TD]
[TD="width: 89, bgcolor: transparent"] Rotation [/TD]
[TD="width: 89, bgcolor: transparent"] EmpID [/TD]
[TD="width: 89, bgcolor: transparent"] 1 [/TD]
[TD="width: 89, bgcolor: transparent"] 0 [/TD]
[TD="width: 89, bgcolor: transparent"] 1 [/TD]
[TD="width: 89, bgcolor: transparent"] 1 [/TD]
[TD="width: 89, bgcolor: transparent"] 0 [/TD]
[TD="width: 89, bgcolor: transparent"] AppID [/TD]
[TD="width: 89, bgcolor: transparent"] 1 [/TD]
[TD="width: 89, bgcolor: transparent"] 1 [/TD]
[TD="width: 89, bgcolor: transparent"] 1 [/TD]
[TD="width: 89, bgcolor: transparent"] 0 [/TD]
[TD="width: 89, bgcolor: transparent"] 1 [/TD]
[TD="width: 89, bgcolor: transparent"] UserID [/TD]
[TD="width: 89, bgcolor: transparent"] 0 [/TD]
[TD="width: 89, bgcolor: transparent"] 1 [/TD]
[TD="width: 89, bgcolor: transparent"] 0 [/TD]
[TD="width: 89, bgcolor: transparent"] 1 [/TD]
[TD="width: 89, bgcolor: transparent"] 0 [/TD]
[TD="width: 89, bgcolor: transparent"] Totals [/TD]
[TD="width: 89, bgcolor: transparent"] 2 [/TD]
[TD="width: 89, bgcolor: transparent"] 2 [/TD]
[TD="width: 89, bgcolor: transparent"] 2 [/TD]
[TD="width: 89, bgcolor: transparent"] 2 [/TD]
[TD="width: 89, bgcolor: transparent"] 1 [/TD]
</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.
 
Upvote 0
I can’t find a way to group/combine the totals from eachemploy to get one total for each service.
Click to expand...


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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
VBA to take a field value from 1 worksheet and update that field value to another worksheet based on another cell value
Replies
4
Views
473
Peter_SSs
Peter_SSs
M
  • Question Question
Match cell contents exactly for find and replace based on a table
Replies
3
Views
771
mumps
mumps
cyrilbrd
  • Question Question
VBA display pictures in a table
Replies
2
Views
478
cyrilbrd
cyrilbrd
ajjava
  • Question Question
Procedure to find MAX VALUE in a SPECIFIC pivot table value field, in the case of MULTIPLE pivot table value fields
Replies
0
Views
2K
ajjava
ajjava
R
  • Solved
Compare similar company names between two tables and return one of the values
Replies
2
Views
1K
realtoast
R

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top