Update field based on values in another -different data types. 1

[craigorama]

Hi there,

I have a large table (FISHERY) where there are a number of fields with boolean data types. I am trying to figure out how insert those values into one field in a new table's (GIS) field named species that is a text data type.

Example would be if FISHERY.Halibut= true then GIS.Species = Halibut, FISHERY.Salmon_Ck= true then GIS.Species= Chinook and so on.

This could require some programming but i'm just wondering if its possible to do this with an SQL statement or two.

I'm running Access 2002, btw.

Thanks in advance.

 

[dhookom]

If you have a FISHERY record with both Halibut and Salmon_Ck true, would this create 2 records in GIS? This would make sense in terms of normalizing the FISHERY table.

INSERT INTO GIS (PrimaryKeyField, Species)
SELECT PrimaryKeyField, "Halibut"
FROM FISHERY
WHERE Halibut = True;

You could try make the SELECT portion into a union query that includes other YN fields.

Duane
Hook'D on Access
MS Access MVP

 

[craigorama]

Yes if there is more than 1 value that is true then i would like to create more than one record in the new table. I still have to figure out a way to copy the values from the rest of the record onto the next row.

just a quick thanks duane, i will try this out later today.

 

[dhookom]

I still have to figure out a way to copy the values from the rest of the record onto the next row.

I believe you could use a union query in the insert query code I suggested.

If you can't figure this out, come back with some specific table and field names.

Duane
Hook'D on Access
MS Access MVP

 

[craigorama]

Hi duane,

I tried out your statement and it works great and solves one of my issues - that of how to get yes/no fields into a text field.

However I am having higher level problems. I am a little rusty on SQL so bear with me.

I need to get information from certain fields in my Fishery table into my Species table. I've attached a quick img I made showing what fields are going where. Some fields values can transfer directly such as:

PARTICIPANT_ID --> Part_ID
POLYGON_ID -->Poly_ID

However others are based on values in the yes/no field.

Eulachon,Halibut,Rockfish,Salmon ---> Species.
1920_40,1950,1960 etc--> Time_Frame
OBS_METHOD-->Info_Type

What is the best way to complete this conversion? An intitial INSERT INTO statment followed by a number of UPDATE statements? This might not work because the UPDATES will produce multiple rows.

I've never used a union query. How would that look?

Here is the tables pic:

 

[dhookom]

Your SQL might look something like:

INSERT INTO Species_Test (Poly_ID, Species, Interviewer, Part_ID, Fishery_Method)
SELECT POLYGON_ID, "Halibut" as Species, INTERVIEWER_ID, PARTICIPANT_ID, GEAR
FROM FISHERY
WHERE Halibut = True
UNION ALL
SELECT POLYGON_ID, "Eulachon", INTERVIEWER_ID, PARTICIPANT_ID, GEAR
FROM FISHERY
WHERE Eulachon = True
UNION ALL
SELECT POLYGON_ID, "RockFish", INTERVIEWER_ID, PARTICIPANT_ID, GEAR
FROM FISHERY
WHERE RockFish = True
UNION ALL
--- etc ---
SELECT POLYGON_ID, "Other", INTERVIEWER_ID, PARTICIPANT_ID, GEAR
FROM FISHERY
WHERE Other = True;

If this doesn't work, you may need to create a union query which starts at "SELECT ...." and goes to the end. Then your append query would be:

INSERT INTO Species_Test (Poly_ID, Species, Interviewer, Part_ID, Fishery_Method)
SELECT POLYGON_ID, "Halibut" as Species, INTERVIEWER_ID, PARTICIPANT_ID, GEAR
FROM quniYourUnionQuery

You will need to add the InfoType column and might need to handle the time frame which doesn't make much sense to me.

Duane
Hook'D on Access
MS Access MVP

 

[craigorama]

I created this extremely long query that still needs some more lines added but works so far. It will grow because I have to do a large amount of where statements for InfoType and Gear fields as well.

SELECT POLYGON_ID, "Halibut" AS Species,"CO" AS Interviewer, PARTICIPANT_ID, GEAR
FROM FISHERY
WHERE Halibut = True and  INTERVIEWER_ID=1
UNION ALL
SELECT POLYGON_ID, "Halibut" AS Species,"UN" AS Interviewer, PARTICIPANT_ID, GEAR
FROM FISHERY
WHERE Halibut = True and  INTERVIEWER_ID=3
UNION ALL
SELECT POLYGON_ID, "Eulachon" AS Species,"CO" AS Interviewer, PARTICIPANT_ID, GEAR
FROM FISHERY
WHERE Eulachon = True and  INTERVIEWER_ID=1
UNION ALL
SELECT POLYGON_ID, "Eulachon" AS Species,"UN" AS Interviewer, PARTICIPANT_ID, GEAR
FROM FISHERY
WHERE Eulachon = True and  INTERVIEWER_ID=3
UNION ALL
 ....
SELECT POLYGON_ID, "Steelhead" AS Species,"CO" AS Interviewer, PARTICIPANT_ID, GEAR
FROM FISHERY
WHERE Salmon_ST = True and INTERVIEWER_ID=1;

My question now is how do I insert the results of this query into my Species_Test table?

 

[dhookom]

Why do you need to create separate selects for each interviewer? You might be able to create a table of interviewers with the number and the "CO" or "UN" or whatever. Then join the interviewer lookup table to the final union query. Use data in tables rather than creating a ton of extra work.

Duane
Hook'D on Access
MS Access MVP