Fill in missing values in an array

[bkwilliams]

I have a database with annual counts of frogs at particular sites. There are
not observations from some sites in some years, resulting in many missing
values. Sites and years with no observations are not currently in the
database. However, the analysis software I'd like to use requires a full
matrix of all years and all sites, with missing values coded as -1. Is there
a way to "fill in" the missing years sequentially and then code all missing
values? Thanks very much.

 

[bkwilliams]

It's a table...I'm relatively new to Access and know how to overwrite nulls
as -1s, but I need to fill in the year/site combinations that are currently
missing before Access will know that some of the nulls exist.

 

[Jeff Boyce]

If this were mine, I'd create a procedure that iterated through "cells" of
the matrix, checking for values and overwriting nulls (or zls) with your
coded -1.

It all starts with the data... you don't mention whether this "array" is a
table, or an array in code.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Arvin Meyer [MVP]]

You can do it by adding a table with all the years, and using an Outer
(Left) Join in a query to include all the years.

 

[Jeff Boyce]

Arvin's response covers the Years. You might also want to create (if you
haven't already) a table that holds all sites. Then you could use a
Cartesian Product query to generate a "matrix" of all possible Year x Site
combinations. In a subsequent query, you could compare to your data to find
the combinations without data.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/