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

[Ka0ticSt8]

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.

 

[Joe4]

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]

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]

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]

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]

as food for thought:

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 |
------------------------------

 

[Ka0ticSt8]

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.

 

[xenou]

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]

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]

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):

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:

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):

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]