Help on creating a new field on a table and more complex stuff 1

[Idmpc]

Hi everyone

So, I was developing this database for someone, but now it needs an upgrade. They want me to add a field (lets call it NEWFIELD) to an already existing table, however, that field will need to store some information that's not present on the database. Also, the database is already filled with data, so creating a new one would take a very long time.

Here's an example:

I have a database with a table called TABLE1

That table has 2 fields: FIELD1 and FIELD2

I need to add a new field, called NEWFIELD

Now, here's the complex part of it:

I have a file with me with the following data

NAME                  CONTAINS
NAME1                 C1, C2, C3, C4, C5, C6
NAME2                 C7, C8, C9

However, on my existing database (TABLE1), field FIELD1 has a "C" value already, here's an example of my current existing database:

TABLE1

FIELD1     FIELD2
  A          C1
  B          C7
  C          C7
  D          C3

What I need is for my NEWFIELD to hold the "name" that contains the "C" value for each record, for example, the new table would look like this:

TABLE1

FIELD1     FIELD2     NEWFIELD
  A          C1         NAME1
  B          C7         NAME2
  C          C7         NAME2
  D          C3         NAME1

What I really want is to create a table for that file I have (NAME1 has C1, C2, C3...), so that when I use a SQL query, I can somehow check the C value on FIELD2 of TABLE1 and bring the correct NAME value for it.

Also, I need my table that contains the file information (NAME1 has C1, C2, C3...) to be "Updateable", i mean, I want to be able to add new "NAME" values with new "C"s on it.

I imagine that creating a table for each "NAME" value, and storing all the "C"s that name has is hardly the right way to go...

Can anyone help me?

Thank you very much!

PS.: If this post is too confusing, please don't hesitate to ask questions and I'll try to explain it the best I can!

 

[fneily]

I made two tables: FirstTable looks like your second example and SecondTable looks like your first example.

Place another field on the FirstTable and called it FieldNew.
Created a form based on the FirstTable and on the On Current event put:

Private Sub Form_Current()
Dim SearchString, SearchChar
Dim RS As DAO.Recordset
Set db = CurrentDb()
Set RS = db.OpenRecordset("Secondtable", dbOpenDynaset)
RS.MoveFirst
SearchChar = Me![FieldDes]
Do Until RS.EOF
SearchString = RS![NameDes]
If InStr(1, SearchString, SearchChar, 1) > 0 Then
Me![FieldNew].Value = RS![NameID]
Exit Do
Else
RS.MoveNext
End If
Loop
RS.Close
db.Close
Set RS = Nothing
Set db = Nothing
End Sub

NameDes is the string c1,c2,c3,etc.
NameID is Name1, Name2, etc.

 

[Idmpc]

Sorry, I'm kind of confused with that answer...

However, I thought of a solution that may be possible:

I created a new table called NAMES, which contains fields ID and NAME

Then I created another table called CONTAINS, with fields ID, C and NAME

Here's what my new tables look like:

TABLE NAMES

   ID     NAME
   1      NAME1
   2      NAME2
   3      NAME3
   ...

TABLE CONTAINS

   ID     C     NAME
    1     C1    1
    2     C2    1
    3     C3    1
    ...
    7     C7    2

Since each "C" can only be linked to one "NAME", I created both tables so that I can always add new "C"s or "NAME"s and link them through the "C"s table...

But now I need some help on a SQL query that will return me those values, for example, my original database has this table already:

TABLE1

FIELD1     FIELD2
  A          C1
  B          C7
  C          C7
  D          C3

I want my SQL query to bring me the following result, which it will look for on the other 2 new tables I created:

SQL Query result needed:

FIELD1    FIELD2    NAME
  A         C1      NAME1
  B         C7      NAME2
  C         C7      NAME2
  D         C3      NAME1

Does anyone know how to create a SQL Query that will bring me those results?

Thank you very much

 

[PHV]

Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Idmpc]

Thanks PHV!
I never really learned how to use those "JOIN" commands, so that link will prove very useful!