Which is the best way to do this?

[Paul Milner]

Hi
I have an old database with a lot of unnecessary fields.
For example;
there are three fields to describe gender. male female
and both
I have created new database which has just the gender
text field with discriptive check buttons for male,
female and both, all directed to the one gender field
which is the best method to look at the male female and
both in the old database detect the one which is true
(ticked) and then place the answer in the gender field of
the new database with the discriptive name ie, female
This is a simplified example ,the actual database has in
one example 15 unwanted fields to be directed to one
field
Thankyou

 

[Ken Snell [MVP]]

Easiest way is to write an update query that uses the original table as a
source table that is linked to the target table via the personID field. This
update query should update the value of the gender field in the target table
using an IIf expression as the Criteria expression for the target table's
gender field. Something like this, perhaps, for the IIf statement:

IIf(SourceTable.MaleField = "Male" And SourceTable.FemaleField = "Female",
"Both", IIf(SourceTable.FemaleField = "Female", "Female", "Male"))

 

[Nikos Yannacopoulos]

Paul,

Yes/No fields return -1 if true, 0 if false, when used in a mathematical
expression. Taking advantage of this, if only one (max) of the 15 fields
will ever be true, you could use an expression like:

[field1] * -1 + [field2] * -2 + ... + [field15] * -15

which will return the number of the true field, or 0 if none.

In case there could be more that one true fields, you would need something
more sophisticated like multiplying by (negative) powers of two, so any
numeric result can identify uniquely the true fields; if you are familiar
with binary logic, you'll know what I mean.

A less sophisticated method would be to make a table of all the possible
combinations of the 15 fields, with an extra field to place the desired
output, then join the two in a select query and derive the outcome from the
second table field. Of course, this could only apply in the case of limited
number of combinations; if any combination is possible then you're looking
at 32,768 records (2^15), which is obviously out of the question.

HTH,
Nikos

 

[Guest]

Thanks Ken
It looks good. I am still trying to get my head around
the SQL statement though

lets say,
the name of my destination table is called, tblDest
the name of my original table is called, tblOrig
tblDest has a field called Gender

tblOrig has fields called male,female and both and are
text fields so there output is either yes or No

How can I change the SQl so that;
the query looks at male, female and both in tblOrig
putting them into tblDest replacing the yes fields with
discriptive fields. a bit like this;

if tblOrig.female field = yes then put into
tblDest.gender field as female. if female field = no then
dont put in

Thanks for your help
Paul M

-----Original Message-----
Easiest way is to write an update query that uses the original table as a
source table that is linked to the target table via the personID field. This
update query should update the value of the gender field in the target table
using an IIf expression as the Criteria expression for the target table's
gender field. Something like this, perhaps, for the IIf statement:

IIf(SourceTable.MaleField = "Male" And

SourceTable.FemaleField = "Female",

 

[Ken Snell [MVP]]

Query SQL would look something like this (untested):

UPDATE tblDest INNER JOIN tblOrig
ON tblDest.PrimaryKeyFieldName =
tblOrig.PrimaryKeyFieldName
SET tblDest.Gender =
IIf(SourceTable.MaleField = "Yes" And
SourceTable.FemaleField = "Yes",
"Both", IIf(SourceTable.FemaleField
= "Yes", "Female", "Male"));
--

Ken Snell
<MS ACCESS MVP>