## Access 97 Query Calculation (Access 97)

I produce a crosstab query in Access 97 that runs against a database
of fixed assets and sums the asset cost by county by year acquired by
property group as follows:

County YearAcq Group1 Group2
Shelby 1995 100000.00 235000.00
Shelby 1996 83000.00 332392.33
Shelby 2001 8000.00 13000.00
Thompson 2001 981000.00 500.00

I've since learned that the person I create the query for takes the
information and then plugs it into an Excel spreadsheet in order to
allocate the county totals to a number of locations by square footage
in order to prepare property tax returns for each location (the
location information is not included in the fixed asset database).
This is quite time consuming since we have a large number of
locations.

What I want to do is create a table that contains either the square
footage amounts or the resulting allocation factors for each location
in a county and let Access do the math in order to allocate the county
totals among several locations. I can then produce an attachment of
property for each location's property tax schedule. I'm struggling
with the steps since I think I'll end up trying to join two items
(tables, queries, etc.) that have many occurences in each. To use the
previous example, if there are 3 locations in Shelby County, Bartlett,
Memphis and Raleigh, I can create a table of allocation factors with a
County field, Location Name field and a Factor field, but how would I
link that to the summarized fixed asset information to multiply the
factor for each location by the county totals for each year since
there will be several Shelby County line items for the various years
property was acquired? What I would like to be able to produce is a
query with the following calculated and summarized information
(assumes factors of .3333 for each of the 3 locations in Shelby County
applied to the previous amounts in the example):

County Location YearAcq Group1 Group2
Shelby Bartlett 1995 33000.33 78333.33
Shelby Bartlett 1996 27666.67 110797.44
Shelby Bartlett 2001 2666.67 4333.33
Shelby Memphis 1995 33000.00 78333.33
Shelby Memphis 1996 27666.67 110797.44
Shelby Memphis 2001 2666.67 4333.33
Shelby Raleigh 1995 33000.00 78333.33
Shelby Raleigh 1996 27666.67 110797.44
Shelby Raleigh 2001 2666.67 4333.33
Thompson ........and so forth

Can someone give me some direction? I'm not much at coding but I'm
willing to try anything because I see a potential time savings of
several hours if not days over a number of years. TIA.

W. Alan Lemly