Access query replace cell contents will cell value & variable

Question

Thank you very much for looking.  Situation is this..there are 16 yes/no flags in a table.  I would like a 17th field that is text to hold a string representing which fields are checked.

So imagine 16 update queries that simply look at a field and if that field is checked it adds a comment to the 17th field

Query1     if C_Date = -1 set string = string & "date; "                if for that record C_Date is checked String would equal "date; "

Query2     if C_State = -1 set string = string & "state; "               if for that record C_State is checked String would equal "date; state; "

Query3     if C_City = -1 set string = string & "city; "                   if for that record C_City is not checked String would equal "date; state; "

Query4     if C_Address = -1 set string = string & "address; "     if for that record C_State is checked String would equal "date; state; address; "

etc...

I hope this paints a picture of what I am trying to do

I tried using the replace function but I couldn't get it to work.

I can also run this as a DoCmd.RunSQL or a DoCmd.OpenQuery from from a button's click action. 

Again, thank you in advance for any assistance you can offer.  I have spent the last hour banging my head trying to make replace work and I am sure there is someone out there smarter than I who will solve this in 5 minutes.

-john

Answer 1

That is not a good idea, because the new field would be a "calculated value" which don't belong in a table but in a query.

So in a select query you might write:

select *, CalculateString(C_Date, C_State, C_City, C_Address, etc) from myTable

Then write a public function in a standard module:

public function CalculateString(byval blnDate as Boolean, byval blnState as Boolean, byval blnCity as Boolean, byval blnAddress as Boolean etc) as string

dim result as string

if blnDate then result = result & "date; "

if blnState then result = result & "state; "

if blnCity then result = result & "city; "

if blnAddress then result = result & "address; "

CalculateString = result

end function

Answer 2

I'm going to urge your to reconsider Ken's advice. As he said (and I'm sure Tom will agree), you have a flawed design which will continue to cause you problems in the future.

Answer 3

Tom, this will work...originally due to the number of records and how frequently the field would be referenced I didn't think I could keep the value calculated without affecting the performance of the tool...but I tested the function and it doesn't seem to be causing a hiccup...so I will use it.  Thank you Very much for your response :)

Answer 4

Unfortunately you are starting from a fundamentally flawed table design which does what is known as 'encoding data as column headings'.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1).  This requires that all data be stored as values at column positions in rows in tables, and in no other way.

With your current design what you have is a set of columns, each of which represents a value of some attribute type of whatever entity type the table is modelling.  The correct design would be to decompose the table into two related tables in which each attribute is represented by a value in a single column in a separate row in a related table.  This table would model a many-to-many relationship type between the entity type modelled by the current table and a table of different categories (date, state, city etc), so diagrammatically the model would be like this:

Entities----<EntityCategories>----Categories

NB: do not confuse 'entity type' and 'entity'.  The former is modelled by a table, the latter by a row in the table.

As you have used names for your columns which more or less correspond to the values, the process of recasting the data into a correctly structured set of tables can be quite easily automated, and you'll find an example as UnencodeColumns.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

You can either amend the code in my demo to strip of the 'C_' from each value when inserting rows into the table, or remove the first two characters of each value subsequently with a simple 'update' query.

If you then want to return the values as a delimited string you can do so with a concatenation function such as Allen Brown's at:

http://allenbrowne.com/func-concat.html

You'll also find one using the highly efficient GetString method of the ADO recordset object in Concat.zip in the same SkyDrive folder at the earlier link above.