RE: Should be simple but...

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:
Sorting and Grouping (All groupings are ascending.):
Last Name
********
Group:
Header, yes
Footer, no
On, Each value
Interval, 1
Keep Together, No

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

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>

--
Aria W.


"Duane Hookom" wrote:

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.
--
Duane Hookom
Microsoft Access MVP


"Aria" wrote:

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.




--
Aria W.


"Duane Hookom" wrote:

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.

--
Duane Hookom
Microsoft Access MVP


"Aria" wrote:

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.

--
Aria W.


"Duane Hookom" wrote:

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?
--
Duane Hookom
Microsoft Access MVP


"Aria" wrote:

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;

--
Aria W.
.

Loading