Multiple answer/response in field

[Don5215]

I must compile a list of government contrat agencies who serve multiple government territories.<br>e.g. Agency A served areas 1,3,5<br><br>How can I get ACCESS files formatted to accommodate this. I shall also need to total each district for the agency and for the entire program in that district. I shall also have to come up with a grand total.<br><br>This is critical for me. Your help would be immensely appreciated!<br><br>Don5215

 

[Guest_imported]

to answer need to know more about your DB structure. Is this data in tables now? What info is in what tables? If the database is structured properly looks like what you want is a piece of cake. But can,t say that without more info.

 

[elizabeth]

tblAgency: AgencyID (Primary Key), AgencyName, etc.<br>tblTerritory: TerritoryID (Primary Key), TerritoryName, etc.<br>tblAgencyTerritory: (optional ID as PK), AgencyID, TerritoryID<br><br>relationship of tblAgency to tblAgencyTerritory is one to many<br>relationship of tblTerritory to tblAgencyTerritory is one to many<br><br>Is a territory a district?

 

[Don5215]

Elizabeth-<br>Here's what I'm doing with some specificity:<br>Listing 5 districts in county government<br>Serviced by 102 agencies, many serving more than one district, in 7 categories.<br>Got to generate following reports:<br>List of Agencies by all districts served, and include categories with total clients for each.<br>List of budget for each district, totalled: by Agency and by District<br>Need to total the budget for each district, and also come up with grand total.<br><br>There's the usual deadline (July) and the complexity grows daily as we're comparing data for 1999 and 2000.<br><br>Appreciate your help. Thank You!<br>DON5215

 

[elizabeth]

OK, so the structure may need a couple more modifications. I get that you have:<br><br>1 county that contains 5 territories/districts (assuming they are the same thing)<br>102 agencies that serve 1 to many districts<br>7 Service categories<br>x Clients (is a client an individual resident of the county?)<br><br>This sounds pretty easy but let me make sure I understand all the relationships:<br><br>1) Do the agencies provide services in 1 to 7 Service Categories each? Or is each Agency providing only 1 category of service?<br>2) Does any given Client&nbsp;&nbsp;fit into only one Service Category? Or can it belong to multiple Service Categories?<br>3) Is the territory/district served a function of the client location or the agency location?<br>4) If the answer to the last question is &quot;client location&quot;, can any single client exist in multiple territories or only one territory per client?<br>5) Re: budget, you let me know how you want to roll it up, but what is the level at which you are capturing it? The kind of answer I'm looking for is structured like this: <br>&quot;We capture the Dollars spent by each Client per Agency&quot;, or<br>&quot;Dollars made available to each Agency per district&quot;.

 

[Don5215]

Elizabeth- Aooreciate interest and assistance. THANK YOU!<br>1) Agency provide service in from 1 to all 7 categories.<br>2. Multiple service caterories for a client, each seperately counted, monthly total and annualy for each agency, by category (7).<br>3) Agency location is important factor (actually presence in supervisorial district in LA County) because district supervisor needs to know how many served, amount spent, and outcomes (we have a matrix measuring movement out of poverty).<br>4) One client per teritory, but agency works in one or more districts.<br>5) Dollars/agency grant made available to each agency, by territory, and then aggregated into total per month, and per annum. Need to compile three (3) totals: clients served; percent of goal served according to annual grant funding plan for that agency; grant monies expended monthly, and annually of grant funding.<br><br>Don5215

 

[elizabeth]

Hope this covers it, let me know if you find something lacking:<br><br>tblAgency: AgencyID (Primary Key), AgencyName, etc.<br>tblDistrict: DistrictID (PK), DistrictName, etc.<br>tblClient: ClientID (PK), ClientName, etc.<br>tblService: ServiceID (PK), ServiceName, etc.<br>tblBudget: BudgetID ExpenseID ( PK), AgencyID, DistrictID, Year, BudgetAmt<br>tblExpense: ExpenseID: ExpenseID(PK) AgencyID, DistrictID, Year, Month,&nbsp;&nbsp;ExpenseAmt<br>tblAgencyClient: AgencyClientID (PK), AgencyID, ClientID<br>tblAgencyDistrict: AgencyDistrictID (PK), AgencyID, DistrictID<br>tblAgencyService: AgencyServiceID( PK), AgencyID, ServiceID<br><br><br>On the tblAC, tblAD & tblAS, I use autonumbers witha&nbsp;&nbsp;separate ID but some people prefer to skip it and use concatenated keys in that type of table. <br><br>Just holler if all the one to many relationships aren't obvious.<br><br>If you're not familiar with crosstab queries, I'd suggest you consider that format for your rollups. If you're not familiar with crosstabs, they must be based on a single recordsource (query or table), so you'll have to a write a query with all the data you want, and then base the crosstab on that query. Access allows you to nest queries, or build one query on the result of another.