Require a Macro... Help Please?

[JP]

I have a Database with two tables..... (tbl.Main) includes fld.observation_ID(Pk,autonumber), fld.Site_ID (unique identifier for the site.) and a bunch of other fields such as date, observer name etc.... The other table (tbl.Ground_Cover_Type) also has the fld.Site_ID and 15 ground cover names, such as rock, gravel, sand etc as field names, each with a value as Long

I am trying to convert my tbl.ground cover into another table that is more uniform with proper database design. The table I am trying to convert to is named (tbl.Ground_Cover_Characteristics) which has the fields GCT_ID(PK,Autonumber), Site_FK as text (linked One-Many with Site_ID in tbl.Main), fld.LCD_Type_FK as Long, and fld.LCD_Value as Long.

The reason behind the LCD_Type_FK is long is because I have changed the field names to numbers (1 to 15) in tbl.Ground_Cover_Type) and I created another table (tbl.Ground_Cover_Types) with fld.ID (PK, Autonumber), fld.GCT_ID as long and GCT_Name as Text

Any assistance would be greatly appreciated.

JP

 

[Steve Schapel]

JP,

A agree that the key to success here would appear to be getting your
table design correct. From the details you gave so far, I am not sure
that it is quite there yet, but I am not sure. If you post back with
some more details of the tables, fields, meanings of data, and
relationships, and someone will be able to offer some good advice.

In the meantime, as it will be a one-off process, I think that when
you have your new tables set up, it will then be a case of using a
series of Append Queries and Update Queries to get your existing data
into their proper places.

- Steve Schapel, Microsoft Access MVP

 

[JP]

Hi Steve

Your probably right, might not need a VB code, I am used to being pulled out of the wate by some genius that I think now that is my only option

My Original Database has multiple tables containing a series of data related to sites that we visit by helicopter. The purpose of the visits is to ground truth satellite imagery of the habitat shown in the images.

The main table in my database is called just that "Main". The main table contains a primary key autonumber field called named "Observation_Number", then the next field is a text field which is my site_ID which uniqely identifies each site we visit. The reason why it's a text file is because I utilize a series of coded combination of numbers and Hyphens. For example, the 60th site we visited has an observation number of 60, then the site_ID value is 60-06-2003. (60th site overall, visited on the 6th of August, 2003). All sites are visited in August so there is no code for the month. There are another series of fields that describe the basics about the site, including Observer's name, Waypoint Lat, Waypoint Lon etc.

Here is where my error in the design came in, and it is hurting me now as I try to present my data using a report. I created a table dedicated to the Land Cover Type of the site. This table has a Site_ID field which is related to the Site_ID in the Main Table. The the other fields in the Land Cover Table have fld.names like Rock, Sand, Gravel, mosses, water, etc. which described the general description of the land cover type of each site.

I should have originally created a table that identified the 18 different land cover types. Then create a bridging table that has an autonumber,PK field, Site_FK field to relate to the tbl.Main, a Land_Cover Type FK that connects to the land cover Identifier table and a final value field to enter the percentage of each of the 18 types of land cover available at each site

I am trying to get to that now. I am trying to transfer my Land Cover Data from the simple Land Cover Details table to the new and improved join table. and I can't seem to get how to do that

Thanks for your help again

JP

 

[Steve Schapel]

JP,

OK, thanks for the additional details, which helps a lot to see what
you've done.

One thing I am still unclear of... Is each Site visited only once, or
can there be more than one Observation related to each Site? If the
first, then you really should have a separate Site table as well as
the Observation table. If the second, you design is unnecessarily
awkward, in that each record has two "unique identifier" fields, and
once again I would review the design.

However, as regards the immediate issue, of getting the existing Land
Cover data into the new structure, it seems to me that you will need
18 Append Queries. The SQL view of each one will look something
like...

INSERT INTO NewLandCover ( Site_FK, Land_Type )
SELECT Site_ID, "Rock" FROM [Land Cover]
WHERE [Land Cover].Rock)=-1

INSERT INTO NewLandCover ( Site_FK, Land_Type )
SELECT Site_ID, "Sand" FROM [Land Cover]
WHERE [Land Cover].Sand)=-1

etc etc.

I assume I have understood you correctly, that the fields Rock, Sand
etc in the existing Land Cover table are Yes/No data types, and that
there can be more than one Land Cover entry for any given Site?

Making any progress?

- Steve Schapel, Microsoft Access MVP