Combining more than one IRDA database into a single database requires a good understanding of MS Access tables and queries. It is not a difficult process, but there are several places in the process that could result in creating problem data if not done properly. Before you attempt the following process, be sure to save copies of all your original files in a safe place. The following instructions are not a "cookbook", but describe one example of the steps that could be used to combine more than one data set.

1) Copy an unused copy of IRDA2 into a new directory.

2) Using the new copy of IRDA, add at least two fields to each of the four data files (irda2, Costs2, Activity2, and Misc2 ). One will hold the PROJ_ID value from each of the combined files, and the other field will identify which unit/file the record came from. e.g. UnitID (type: Number - LongInteger) ∓mp; UnitName (type: Text).

3) Create links to the four data tables to be added that are in one of the unit databases. Since the table names are the same in both the database with the combined values and the unit databases, the names of the linked tables in the Access database will have an extra number appended to them. You can rename the linked tables to keep track of them more easily, if you wish (e.g. irda2_dist1). Only the linked name will be changed. The name of the table in the unit database (the one being linked to) will not be changed.

4) Create an append query for the irda2 table, similar to the following:

INSERT INTO irda2 ( UnitID, UnitName, AGENCY, Admin1, Admin2, Admin3,
MM, YYYY, FY, ProjectName, INSTREAMS, INSTREAMP, RIPARIAN_A, RIPARIAN_M,
UPLAND, ROADS_DEC, ROADS_IMP, WETLANDS_F, WETLANDS_C, CreateDate,
ModDate )
SELECT irda2_unit.PROJ_ID, "Unit1" AS UnitName, irda2_unit.AGENCY,
irda2_unit.Admin1, irda2_unit.Admin2, irda2_unit.Admin3, irda2_unit.MM,
irda2_unit.YYYY, irda2_unit.FY, irda2_unit.ProjectName,
irda2_unit.INSTREAMS, irda2_unit.INSTREAMP, irda2_unit.RIPARIAN_A,
irda2_unit.RIPARIAN_M, irda2_unit.UPLAND, irda2_unit.ROADS_DEC,
irda2_unit.ROADS_IMP, irda2_unit.WETLANDS_F, irda2_unit.WETLANDS_C,
irda2_unit.CreateDate, irda2_unit.ModDate
FROM irda2_unit;

This is a graphic showing part of the query in MS Access:


This inserts all of the fields in the existing unit table into the master table. The PROJ_ID value from the unit table goes into a new field (UnitID in this example) in the master table and the new PROJ_ID value is system generated. Another field, UnitName, is added to specify which unit (e.g. which Resource Area or District) the records belong to. The name to be inserted is specified in the query (e.g. "Unit1" in this example).

5) Create an append query for each of the remaining three tables, similar to the following:

INSERT INTO Activity2 ( ID, UnitID, UnitName, [Activity Type], ACTIVITY,
[Number], ActivMiles, ActivAcres )
SELECT irda2.PROJ_ID, Activity2_unit.ID, irda2.UnitName,
Activity2_unit.[Activity Type], Activity2_unit.ACTIVITY,
Activity2_unit.Number, Activity2_unit.ActivMiles,
Activity2_unit.ActivAcres
FROM irda2
INNER JOIN Activity2_unit ON irda2.UnitID = Activity2_unit.ID
WHERE (((irda2.UnitName)="Unit1"));

This is a graphic showing part of the query in MS Access:


This inserts all of the fields in the existing unit table into the master table. The ID value from the unit table goes into a new field (UnitID in this example) in the master table and the new PROJ_ID value comes from the irda2 table (based on the irda2 values of UnitID and UnitName). Another field, UnitName, is added to specify which unit (e.g. which Resource Area or District) the records belong to. The name to be inserted is specified in the query (e.g. "Unit1" in this example). This needs to be the same name that was used in the irda2 table.

6) Repeat steps 3 - 5 for each admin unit that is to be combined.

7) Add two new fields to the unit shape files: one for the UnitID and one for the UnitName. Copy the Proj ID values to the UnitID field; fill the UnitName field with the same value used in the database for that admin unit. From irda.mdb, link to the dbf files (related to each shape file) and create an update query to change the value of Proj ID to the PROJ_ID value in the combined irda2 table, using the UnitID and UnitName values to match up the records.

8) If you have more than one IRDA shapefile per feature type, please merge the shapefiles with the Viewlessmerge extension or some other application before zipping up the data for upload. The Viewlessmerge extension merges many shapefiles of the same feature type (i.e. polygon with polygon layer, line with line layer etc.).  Documentation on how to use Viewlessmerge is included with the download. This tool is particularly useful when combining up districts.