appling a calculation to a whole table

[Dan]

Dear all,
In a table I need to apply a calculation (a multiplication, albeit a rather
complicated one with lots of iif statements) whereby I aim to mulitply the
value in one field (called length) to many (~50) other fields in each record.
The length value in each record is different, so each recieving field needs
to be multiplied by the length value in its record. Ideally I would like a
make table query, so I can have all the new calculations in parallel to the
raw data (as I need to summarise this new data as well). I have tried having
the calulation string in a new field representing each of the fields I want
to be mulitplied, but the whole thing was to complex and access refused it.
Is there a simpler way of designing this? Any suggestions gratefully
received!
cheers, Dan

 

[Rick B]

What are these 50 other fields? Sounds like your table is not designed
properly. If you have 50 associated values, you should probably have them
stored in a separate related table. You'd need to tell us what the data is
for us to be sure.

A properly designed table should have 20 or 30 fields at the most. There
might be some very rare cases where you have more, but it is unlikely.
Anytime your field names contain data you probably have a poorly designed
table. I would bet that each of your 50 fields contain the same type of
data, but have some additional identifyer on them. For example, if your
field name is "QtyMarch2007" or "CostEastCoastOffice" then you have a
poorly designed table. Instead, you would build related tables with a
separate field for the date or the office.

You don't store the calculation, nor do you need to make a new table with
that data. Just create your query and you can use that query as the record
source for a report, form, or even another new query. You can also export
query data.

Most likely, though not positively, your tables would need to look a little
more like this...


Main Table
SomeKeyNumberField
Description
Length
etc.


Relatedtable
SomeKeyNumberField (the link to your main table)
SomeDescriptionOrType (probably the current field name from your
mis-designed table)
SomeNumbericValue (to be multiplied by your length in the main table)


Hope that gets you on the right track. Feel free to tell us what type of
data is stored in the 50 fields if you want to run it by us before
redesigning your tables.

 

[Dan]

Hi Rick,
the 50 fields represent different plants species (from biological survey
work), the values in each are the abundances of each species at each location
(record). I am slightly constrained in that the PDA that I download the
survey data from gives the raw data in a large spreadsheet type format, with
records down the sheet and the individual species abundance values (fields)
along the top. Also, any 0 values, or no abundance needs to be recorded
aswell, as a species not being present is as valuable information as the
presence of several. I have linked tables with all the details of each
location and plant species data, so my main table has only the abundance
values. However, as you point out, I do have a lot, I am not sure what can
be done.
Thanks
Dan

 

[Jeff Boyce]

Dan

Even though your PDA offers up "spreadsheet" data, when using Access you are
NOT constrained to maintain that data structure. After all, a lot of folks
come to Access from Excel -- one of the first things they need to learn is
that Access is not a Spreadsheet! You won't get good use of Access'
excellent features and functions if you insist on feeding it 'sheet data.

Take a look at "normalization" as a HELP (and search) topic. It is quite
common to simply import data from a spreadsheet, then use one/more queries
to parse the data from this temporary import table into your more permanent,
well-normalized Access tables.

In the example you mentioned, a more normalized design might be:

trelSpeciesAbundance
SpeciesAbundanceID
SpeciesID (a foreign key value from your tblSpecies)
LocationID (a foreign key value from your tblLocation)
Abundance
...

Note that each valid species-location-abundance value gets its own row.

Regards

Jeff Boyce
Microsoft Office/Access MVP