UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Using Dlookup For A Yes/no Field, Access 2016    
 
   
Scooter1
post Jan 12 2018, 07:01 PM
Post#1



Posts: 12
Joined: 8-January 18



UA,

fixed one problem and one more pops up. need the experts to assist.

tblStaff
-------
staffID---PK
firstname
lastname
dept---lookup table
supv----yes/no
manager---yes/no


lkpDept
-------
dept---Sales, procurement, marketing etc


I am trying to create a report that will group by Dept. My problem is how to show the manager's lastname, firstname next to the field Dept in the report.

Again the report is grouped by dept, and each dept will have different managers.
I tried every advice that I can find, and none works.

A sample would be nice to see and learn how to go about doing this.


Thank you!
Go to the top of the page
 
projecttoday
post Jan 12 2018, 07:43 PM
Post#2


UtterAccess VIP
Posts: 8,800
Joined: 10-February 04
From: South Charleston, WV


Can a department have more than 1 manager?

--------------------
Robert Crouser

Go to the top of the page
 
tina t
post Jan 12 2018, 08:06 PM
Post#3



Posts: 5,214
Joined: 11-November 10
From: SoCal, USA


QUOTE
tblStaff
-------
staffID---PK
firstname
lastname
dept---lookup table
supv----yes/no
manager---yes/no

this looks like a table normalization issue to me. you're storing data in field names (supv, manager) instead of in a field. i'd suggest a "lookup" (i call tables like this "supporting") table listing all job titles that are relevant to the business process the db will support. if there are currently only two - supv and manager - that's fine.

if a staff person may be assigned more than one job title at any given time, then you'll need a junction table between tblStaff and tblJobTitles: one staff person may have many jobs (at the same time), and one job may be assigned to many staff persons (at the same time, or over time).

however, if a staff person may only hold one job title at any given time, and if you don't care about tracking (history) of previous job titles for a staff person, then just remove the Yes/No fields from tblStaff, and add a foreign key field to hold the primary key value of tblJobTitles.

once the tables are set up according to relational design principles, it will be easier to create the report that you need.

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
Scooter1
post Jan 12 2018, 09:41 PM
Post#4



Posts: 12
Joined: 8-January 18



projecttoday,

The way I set up the table, there can only be one manager for each department. Where I got stuck is the fact that this is a field with a Yes/No. So each record in tblStaff would look something like this:

staffID firstname lastname dept supv manager

1 John Doe Sales Yes (Checked)
2 Mary Dunn Marketing Yes(checked)
3 Sue Spelling Sales Yes(checked)
4 Jane Doe Marketing Yes(checked)
5 Brian Slow Operation Yes(checked)
6 Tony Mann Operation Yes(checked)
7 Lucy Simms Operation Yes(checked)
8 Nolan Krempf Sales Yes(checked)
9 Maurice Grant Marketing Yes(checked)

Report
------
Grouped by Dept manager for each dept (one for each)
staffID firstname lastname


*** If there is a better way to do this, please let me know. Part of making this a Yes/No field is that I can use a Combobox in my form and have it show only the field that has manager "true". Again if there is a better way then I will take a look at.

Thank you





Go to the top of the page
 
projecttoday
post Jan 12 2018, 11:21 PM
Post#5


UtterAccess VIP
Posts: 8,800
Joined: 10-February 04
From: South Charleston, WV


Again, can a department have more than 1 manager? I mean the actual department, not your existing table.

--------------------
Robert Crouser

Go to the top of the page
 
Scooter1
post Jan 13 2018, 12:47 AM
Post#6



Posts: 12
Joined: 8-January 18



tina,

I changed my table to what you recommended.

tblStaff
-------
staffID---PK
firstname
lastname
dept---lookup table
jobTitle--FK (in table design, I made this a lookup table)

tblJobTitle
titleID---PK
title---text


lkpDept
-------
dept---Sales, procurement, marketing etc

How to do I group by dept on my report, and under the dept, I need to have the name of the manager. Again I am not too concern about tracking work history. I need to show in my report with each department(grouped) and with the assigned manager to each group of department.

So generically here is what I am looking for:

Sales John Doe(this staff is assigned a JobTitleID that corresponds to Manager in the tblJobTitle)
Names of everyone that assigned to this department is listed here

Marketing Jane Doe(this staff is assigned a JobTitleID that corresponds to Manager in the tblJobTitle)
Names of everyone that assigned to this department is listed here
Procurement Payne Lucas (this staff is assigned a JobTitleID that corresponds to Manager in the tblJobTitle)
Names of everyone that assigned to this department is listed here


and so on and so forth...
Go to the top of the page
 
RJD
post Jan 13 2018, 11:24 AM
Post#7


UtterAccess VIP
Posts: 7,969
Joined: 25-October 10
From: Gulf South USA


Hi Scooter: You seem to be struggling with this and it has been laying fallow since last night, so I thought having a db demo to work with might be helpful in communicating and developing. See the demo attached, using your data and a slight mod to your design.

In addition, you mentioned using "lookup tables." I hope you mean what I have incorporated into the database and not table lookup fields. Using table Lookup fields only complicates things and is not necessary at all.

Take a look and see if this is what you are trying to do. Or if you can bend it to your requirements.

HTH
Joe
Attached File(s)
Attached File  ScooterDB_UA.zip ( 32.45K )Number of downloads: 4
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Scooter1
post Jan 13 2018, 12:59 PM
Post#8



Posts: 12
Joined: 8-January 18



RJD,
You saved another Access Newbie once again! Thank you for your rescue in this mini project, couldn't figured this one out on my own. Tried everything out there and came up nothing. UA developers, you guys are awesome!

Thanks a million!!!!! no make that a Trillion!!!!!!
Go to the top of the page
 
RJD
post Jan 13 2018, 01:04 PM
Post#9


UtterAccess VIP
Posts: 7,969
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Glad that was helpful.

Regards
Joe

from phone

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th January 2018 - 12:45 PM