Beginner issue with creating a report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Katperryberry - 16 Jun 2006 00:44 GMT

I'm working on my first Access database and don't know if a lookup is
appropriate for what I want.

I'm creating a social/legal services directory listing over one
organizations by field of law. The goal is to create hardcopy report that can
be given out to the community. The trouble I'm having is that most agencies
need to be listed under several fields of law and I can't figure out how to
set up the report (or the underlying query) so that the report will list each
field of law and then all of the applicable agencies in alpha order.

I have a table set up with all of the agency contact info as well as all of
the fields of law. I can't figure out if these should be two tables or one
and either way how I set up the query/report so that the final product sorts
the agencies by field of law in a way that any agency that works in multiple
fields of law shows up under each category.

Thanks in advance for any help!

Kat

Reply to this Message

Duane Hookom - 16 Jun 2006 01:08 GMT

It sounds like to wrongly set up fields of law as fields in a table. The
solution is to have at least three tables:
tblAgencies
===============
AgencyID
AgencyName

tblServices (I prefer to not use the term Fields)
==========
ServiceID
ServiceTitle

tblAgencyServices (1 record per Agency per Service)
==================
AgenServID primary key
AgencyID
ServiceID
ServiceStatus

Your report is based on tblAgencyServices with agency and service titles
coming from the related tables.

Signature

Duane Hookom
MS Access MVP

> I'm working on my first Access database and don't know if a lookup is
> appropriate for what I want.
[quoted text clipped - 23 lines]
>
> Kat

Reply to this Message

Katperryberry - 16 Jun 2006 18:55 GMT

Thank you for your help :)

I'm obviously way out of my league here though. I tried what you suggested
but after creating the 3 tables and entering a few records and then creating
the report with all of the fields from the tblAgencyService it is not
providing me with a report that lists the agencies by Service. The report
doesn't provide me with anything but the AgencyID ServiceID AgenServID and
ServStatus columns and no data from the records I created shows up at all.

I think I am either completely misunderstanding your suggestion or I'm not
articulating what I want to set up very well.

What I would like the report to look like is something like this:

CIVIL RIGHTS -- (Legal Service Category- 1 of 14 Service Categories)

The Law Center - (Agency Names - 1 of 300 Agencys)
736 Ferry Street
Napa, CA 94558
(866) 254-8652
Provides referrals for low income clients in labor discrimation, police
brutality and immigration issues. (Short description)

ACLU
2000 Broadway
Oakland, CA 94612
(800) 456-8952
Provides litigation and lobbying for a variety of civil rights issues

Each Service category would list agencies that provide those services. Most
agencies would fall into more than one Service category.

Thank you again for your help and patience!

~Kat

> It sounds like to wrongly set up fields of law as fields in a table. The
> solution is to have at least three tables:
[quoted text clipped - 44 lines]
> >
> > Kat

Reply to this Message

Duane Hookom - 17 Jun 2006 04:15 GMT

You should have included all three tables in the report's record source.
There should be one record in the report per record in the junction table
"tblAgencyServices".

Signature

Duane Hookom
MS Access MVP

> Thank you for your help :)
>
[quoted text clipped - 86 lines]
>> >
>> > Kat

Reply to this Message