Group Header with Multiple Keywords

[Guest]

I am trying to create a phone list. What I want it to do is to list all
names as top level groupings with their phone numbers and misc information
underneath, but also I want it to group each person by company and list all
employees of that company under the group.

==== Example ====

ABC Corp 555-1234
Black, Joe 555-5546
Smith, John 555-3133

Black, Joe 555-5545

Doe, John 555-4444

Smith, John 555-3133

XYZ Corp 555-9876
Doe, John 555-4444

==== End Example ====

Any guidance would be greatly appreciated.

 

[Allen Browne]

The answer will depend on how you have your tables set up.

It seems that you need to be able to have:
- Comapnies
- Persons
- Persons at companies.

The best solution will be to use one table, where everyone (companies and
persons) are in the one table of Clients. Then use a related table to
connect the clients with the companies. With that structure, the report you
want would be dead simple to create.

Here's an example of how to set that up:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"Scott Whetsell, A.S. - WVSP"

 

[Guest]

My persons are in one table with their contact information, and the companies
are in another. There is a lookup field in the persons table that references
the companies table. Although I do not recall what that middle table is
called that is supposed to bridge them, but I have not been able to master
setting it up. Each person has a UniqueID, First Middle and Last Name
fields, and home and cell phone fields, the companies have UniqueID, Name,
Addr City and Zip, and Phone / Fax fields.

Can you explain how to create the middle table and which fields to relate to
where?

 

[Allen Browne]

In that case you will need to use a UNION query on your Companies and
Persons tables, to combine them into one long list.

That's the main report. Use a subreport for the indented people-at-company
listing.

Access can't show a UNION query graphically, but it's quite simple.
You create 2 queries with *identical* columns.
Switch the 2 queries to SQL View (View menu in query design.)
Remove the semicolon from the end of one query statement, and add
UNION ALL
in its place.
Then paste in the other query statement.

This kind of thing:

SELECT CompanyName AS ClientName, Phone, "Company" AS SourceTable
FROM Comany
UNION ALL
SELECT Surname & ", " & FirstName AS ClientName, Phone, "Person" AS
SourceTable
FROM Person;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"Scott Whetsell, A.S. - WVSP"

 

[Guest]

Your last reply does help quite a bit, thank you. It has merged the two
tables, but it is still not listing the individuals under the Company header
when the company comes up in the list.

My query responds each individual name and company name as top level now.

My two tables are related, and if I go to the company table, it lists the
companies with the expand box that it will show everyone attached to the
company. It is possible to return these results by way of query also?

 

[Allen Browne]

From your earlier reply, I take it you have a junction table between
Companies and People. This middle table will have fields:
CompanyID Number relates to Companies.CompanyID
PersonID Number relates to People.PersonID

You need the CompanyID field in the UNION query, so the subreport can then
show the right people for that company:
SELECT CompanyID,
CompanyName AS ClientName,
Phone,
FROM Company
UNION ALL
SELECT Null AS CompanyID,
Surname & ", " & FirstName AS ClientName,
Phone,
FROM Person;

Now you have the CompanyID field in the main report, so you can use it in
the Link Master Fields property of the subreport control.

The subreport will be based on a query that uses your Person table joined to
your middle table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"Scott Whetsell, A.S. - WVSP"

 

[Guest]

I've it it down to just a few formatting issues to lay it out properly, but
otherwise it is doing what I wanted it to do. Thank you very much for your
assistance!