Should be simple but...

  • Thread starter Thread starter Aria
  • Start date Start date
A

Aria

Hello,
This should be simple but maybe I'm missing something easy. I have multiple
versions of phone directory reports for our school. I am not experiencing
problems with the mobile/cell phone directory nor the reverse directory by
location. So far, it list everyone as it should.

I seem to be having a slight problem with the staff directory. It is sorted
by last name, first name and then phone location. There can be multiple staff
members assigned to a location phone. I have one location that lists 6 staff
members to a phone. No problem here. My problem is the office phone. There
are 3 staff employees assigned to the office phone as well as their direct
line phones. The report list 2 of the staff members for the office phone and
only one for the direct/desk phone. All 3 staff employees should be listed
for the office phone as well as 3 seperate entries for direct lines. Thank
you for any ideas or suggestions.

Here's the SQL:
SELECT tblPhonesEmps.PhoneID, tblPhonesEmps.EmpID, tblEmployees.LastName,
tblEmployees.FirstName, tblPhones.PhoneNumber, tblPhones.PhoneDescription,
tblPhones.PhoneType
FROM tblPhones INNER JOIN (tblEmployees INNER JOIN tblPhonesEmps ON
tblEmployees.EmpID = tblPhonesEmps.EmpID) ON tblPhones.PhoneID =
tblPhonesEmps.PhoneID
WHERE (((tblPhones.PhoneType)=2))
ORDER BY tblEmployees.LastName, tblEmployees.FirstName;
 
I would confirm the phonetype values and just check your data. The query
looks fine. What section of the report are you displaying the data?
 
Hi Duane,
Thank you for responding. The phone type values are:
1= District cell
2= campus phone

There is a text box for phone number and a subform listing the employees
associcated with the phone number. There *should* be 6 phone listings for the
office, 2 per person. Each person only has one. Since it is my office that
seems to be having the issue, I can confirm that the data is correct.

What section of the report am I displaying the data? Hmm...is that the
problem? It's displaying in the LastName Header.
 
You kinda blind-sided us by not mentioning anything in your first post about
"subform". If this is truly a subform, change it to a subreport.

Then tell us about the record sources of your main report & subreport, their
link master/child properties. We should also get the true values of your
sorting and grouping levels.
 
I'm sorry Duane. I didn't think think the subform mattered since I'm not
having a problem with the reverse directory and it's pulling the same
information but in a slightly different order. Ummm...I'm afraid I'm going to
need help with this. I tried using the subform/subreport wizard but it
doesn't look the way I would like. The Phone Associations subform has a
delete row button which I do not want to show on a report but when the wizard
finished there it was.

Currently, the recordsource is the query (qryPhoneReportCampusPhones).

"True Values of the Sorting and Grouping levels?"
I'm sorry to be such a novice. I'm not quite sure what you mean here. I can
tell you what I see and maybe that will help you, help me.

Sorting and Grouping (All groupings are ascending.):
Last Name
********
Group:
Header, yes
Footer, no
On, Each value
Interval, 1
Keep Together, No

First Name, Phone Description/Location, Phone #
********
Group Header, No
All other groupings same as above.

Thank you for your help.
 
I should also mention something strange that is happening. I wasn't sure. I
always thought it was just me losing my mind but it seems to be switching
which names and phone #s it list for the office lines. I originally posted
that it listed 2 employees for the office and one for the direct line, well
now it shows 2 for their direct lines and only one for the office line. And I
am *absolutely* sure that what I originally posted was correct. I had the
report open when I posted. I have not input all of the data. I only wanted to
check to see if it was working. I purposely chose locations with multiple
employees to double check that everything was OK.
 
First you state: "sorted by last name, first name and then phone location"
Then you have only 2 levels and I can't figure out why you would have a
group header for Last Name.

Can you give a more complete and accurate description of your report and
subreport with the groupings and location of the subreport? What is in the
subreport and how is it linked to the main report?

Looking back through multiple postings and trying to make sense of them
makes my head spin.
 
Oh my gosh! I am so, so sorry! Let's see if I can do a better job with this.
Thanks for hanging in there with me.
The directories I would like to have:

1. Staff directory- I need it to list last name, first name, phone location
(classroom, office, etc.), phone #. I need this directory sorted by last name
then first name. We have staff who share a last name which is why I used sort
ascending on first name as well as the last name.

2. Reverse directory--I need it to list (in this order) phone location,
phone #, last name and first name. I need it to list all employees assigned
to the phone in each location. I need a reverse directory because we may not
always know where each employee is (we're split over 2 campuses). We may be
in contact by radio transmission as well as phones. There doesn't appear to
be a problem with this directory.

3. Mobile/District cell phone-- This directory simply list the last name,
first name and the phone #. There doesn't appear to be a problem with this
directory.

The problem I currently have:
The staff directory only appears to list a staff member once. After I
posted, I added additional records for our Attendance office because they
have the same situation that we do, office line and their direct phone lines.

In the Reverse Directory, it lists Attendance Office, phone # and then each
person assigned to the office. Right below that in alpha order it then list
"Attendance Office, direct". Then it list the phone # and each person
assigned to that phone. It will not do the same for the main office even
though it is the same information, just in a differnet order.

I did as you suggested and created a subreport. I linked it to the main
report but I am still not getting the results I need. Now it doesn't list
*any* of the direct line #s. I'm trying to give you the information you need
but I seem to be falling short of the mark. I may be making this much harder
than it needs to be.

Here is what I did:
1. Created a query for the subreport. Employee Type 1= Site Employee Type
Employee Type 4 = District Employees (Special Education, Police Services,
etc.)

SQL:SELECT tblEmployees.EmpID, [LastName] & ", " & [FirstName] AS LastFirst,
tblEmployees.LastName, tblEmployees.FirstName, tblEmployees.Active,
tblEmployees.EmployeeType
FROM tblEmployees
WHERE (((tblEmployees.Active)=True) AND ((tblEmployees.EmployeeType)=1)) OR
(((tblEmployees.EmployeeType)=4))
ORDER BY [LastName] & ", " & [FirstName], tblEmployees.LastName,
tblEmployees.FirstName;

2. Created a subreport (srptSiteDirectory).

3. Used the wizard to link the srpt to the main report. Link Child/Link
Master Field = Emp. ID

4. The sorting and grouping is as previously stated:
I just don't understand why it works one way and not the reverse. I hope
I've given you enough information. I was just trying to keep it brief. I do
tend to run on in my posts. <g>
 
First you state: "sorted by last name, first name and then phone location"
Then you have only 2 levels and I can't figure out why you would have a
group header for Last Name.
Click to expand...

OK, I kept thinking about this statement and couldn't sleep so I got to
work. When I created the reports I think it was my first time dealing with
sorting and grouping levels. I mis-interpreted something I read in the Help
file and ended up with a group header for Last Name.

I just wanted you to know that I deleted that group header and put the
controls in the detail section. It works now! How easy was that! I knew I
was making it too complicated.

Can I ask one last question? Is there any way that I can get it to just list
the employees' name once and then list all the phone #s associated with that
employee (still grouping using last name first)?

Thank you so much for the push in the right direction!
 
Hi Duane,

No, I had not considered that because I was unaware of this property. I
tried it; changing it from "No" to "Yes". Unfortunately, it didn't work. The
subreport consists of two text boxes with a Control Source of LastName for
one and FirstName for the other. When I look at the subreport preview, I see
it hides the last name for those staff who are affected, but leaves the first
name visible.

This does not affect my sample report because I have not input all staff,
but it doesn't hide the duplicate names I do have on the report. I realize
that sounds a bit confusing. What I mean is that if the employee is
*different* (same last name but the first name is different), it will hide
the last name. If it is the same employee (the only difference being the
phone #), it doesn't hide the first or last name. The report appears
unchanged.

I understand if it can't be done. I was just wondering if there was a way to
do it.
 
If the records are in the same report section then hide duplicates should
work. Have you considered using a single text box for the full name?
 
Hi Duane,
I'm sorry to keep taking up your time. Yes, the records are in the same
report section (the detail section). My first text box and subreport was
txtLastFirst. I deleted it because the page header labels were so far away
from the first name so I switched to 2 text boxes to line them up better. You
just have to change the property from "No" to "Yes" correct? Then in my
report it doesn't hide the names. I'll try again and also the single text box
"LastFirst" and post back the results. Thank you so much for your time.
 
Hi,
I've tried it both ways and the result is the same, the duplicates show. I'm
a little frustrated right now. It should be so easy. I can't for the life of
me figure out why this isn't working. I guess I'll just have to keep it with
the duplicates.
 
It sounds like the text box might be in the same subreport section but in
different instances of the subreport. Is this the case?
 
Please forgive my ignorance but I'm not following you. The textbox inside the
subreport control is in the same section (detail section) as the other text
box controls (phone #, phone location). Do you want me to move it elsewhere?
 
I am not suggesting you move it. If you have the Hide Duplicates set but you
are seeing duplicates in the report, then I suspect the duplicates are from
either different sections of the report or in a subreport linked to a
different record in the main report.
 
Hi Duane,
It's a simple phone directory, not much to it. The only sections I have are:
1. Report Header

2.Page Header with labels depending on version:
a. if using srptLastFirst, there are 3 (lblstaff, lblphonelocation and
lblphonenumber)
b. if using srptSiteDirectory, there are 4 (lblLastName, lblFirstName,
lblphonelocation and lblphone#)

3.Detail section:
a.srptSiteDirectory (depending on version 1 or 2 text boxes)
b.txtphonedescription (location)
c.txtphonenumber

4. Page Footer
a. txtDateFooter
b. txtPageFooter

That's it. There isn't anything else. The duplicates come from the same
staff members responsible for 2 phone lines, the office phone and their
direct line. The main report is linked to the subreport by Employee ID. No
matter which way I run this (subreport, no subreport) the result is the same.

This is what I see:

Smith Jane (or Smith, Jane) Attendance 555-1111
Smith Jane Attendance, direct 555-1007

This is what I would *like* to see:

Smith Jane (or Smith, Jane) Attendance 555-1111
Attendance, direct 555-1007

Brown Amanda Main Office 555-1000
Main Office, direct
555-1003

Teachers and classrooms will be mixed in as well. I know it's a subtle
difference, but if at all possible...
 
Display the border of the subreport and see if the consecutive employee names
are in their own boxes.
 
Back
Top