Consolidate multiple fields into one list

[slreynolds]

I have a need to pull information out of multiple fields across multiple tables and consolidate them into one list.

I will need to update this list regularly so I'd like to write code to handle it, but I really can't think of the right approach.

Thanks

Susan

 

[mincefish]

Is there a Key field, which is unique across all these fields? Or at least fields that link all of these tables?

Why won't a query do it? Surely that would give you information from all of the tables?

 

[slreynolds]

I don't know of any query type that would allow me to take data from multiple fields and move it into one field...

I also don't understand your question about whether there is a key field unquie across all these fields.


I think I haven't made my problem clear:

I have multple tables for several different products. Each of these products contains many different components, each of which has a serial number. Because the products are structured differently it is not feasible to have one table structure. Any one table may have as many as 30 different components that are serialized - thus 30 different fields containing a serial number for each record.

What I need to do is create one list of every serial number in the database (from all tables and all serial number feilds within those tables) and this list needs to be updated periodically.

Th one list thing is a new requirement that arose years after this database was designed for other things or the tables would have been structured differently.

 

[mincefish]

OK - I think I get this now - so what we could do is open up the tabledefs collection (The list of tables in the database), and loop through that collection, then we'd be able to get at the name of every table. We could then open up a recordset on each table, and loop through every field in every record in every table.

Would there by duplicates between fields? Like below:

Product1,Serial1,Serial2,Serial3,Serial4
Product2,Serial1, Serial5,Serial6, Serial7

Well if there are, we can figure that out later.

This assumes we want to query all the tables in the database, with the exception of our results table (tblResults). It also assumes you are using DAO in Access 97.

Function fncGetAllSerialNums()
Dim tdfTemp as TableDef
Dim rstTemp as Recordset
Dim rstResults as Recordset
Dim strSQL as String
Dim iCtr as Integer

Set rstResult = CurrentDb.OpenRecordset("SELECT * FROM tblResults",,dbAppendOnly) 'Open Up a Recordset on Results table (which has one field (serial number)

For each tdfTemp In CurrentDb.TableDefs ' For every table in the collection of tables...
   strSQL = "SELECT * FROM " & tdfTemp.Name 'Select everything from that table
   If Not tdfTemp.Name = "tblResults" Then 'We don't want to add serial numbers from tblResults!!
      Set rstTemp = CurrentDb.OpenRecordset(strSQL, dbForwardOnly)
      Do Until rstTemp.Eof 
         For iCtr = 0 to rstTemp.Fields.Count -1 'Look at every field in the table
            rstResults.AddNew
            rstResults.Fields("SerialNum") = rstTemp.Field(iCtr) ' Add a new row, and put the serial number in there.
            rstResults.Update
         Next
         rstTemp.MoveNext
      Loop
   Endif
Next

'Tidy up!
rstTemp.Close
rstResults.Close
Set tdfTemp = Nothing

End Function

If there were going to be duplicates there, then what you could do, is run an SQL on the table -

SELECT DISTINCT SerialNum FROM tblResults

- that would get rid of any duplicate Serial Nums. Of course if there were loads and loads of dupes, then this may not be the best way to do this!!

Hope some of this helps....

Mincefish

 

[slreynolds]

Thanks for your reply. I had already begun a somewhat different approach which I think will work for me long term. I extracted all of the table & field names using Documenter and copied them into a lookup table that also has an autonumber field. I will be able to maintain this table for the almost certain updates to the database in the future.

Though I'm not entirely certain yet how to do it - I believe I can set up either vba code or SQL to step through the tables & fields and append the data into a global serial number table. I have gotten incredibly busy and won't be able to sit down to figure out the approach for several days (I am not overly versed in the programming end of Access so I have to look stuff up and probably try more than one approach).

Thanks for your help.

Susan