Transpose data into fields

[Concepts01]

Hi,
I must extract data from a table consisting of (let's pretend)3 fields. The field names are IDNumber, Entity, and Data. There is no key field. The problem is, the Entity field contains data like FName, LName, Address, Phone, etc. The Data field would correspond with Jim, Smith, 222 Main, 222-2222, etc. The IDNumber would be the same for all 4 records. I need to transpose the Entity field data, to Field Names in a new table so I can have 1 record for Jim Smith. (example field names: FName, LName. . you get the picture). I am currently doing this by creating multiple queries, pulling the desired records from the Entity field of each query, then running a make table query from the previously made queries. The problem is, the database is over a million records (not an Access database) and growing by thousands every month. And I have 117 data fields that I need to pull. I though if I could do this in code, it would run faster and be more efficient. Can anyone help get me started? Thank you

 

[kathryn]

OK, I think that I understand. For each ID, you have X number of records, because you have X number of fields. There are two steps we are going to have to do. We need to create the fields in a table and then we need to put the data into the fields.

First question: are the Fields (Entities in your case) ALWAYS, absolutely, positively, the same for every ID? If so, we can use the first ID number's records to create the fields and then go through the table and load the data.

Let me know. Don't worry, this really isn't too complicated.


Kathryn

 

[Concepts01]

Hi Kathryn,
I appreciate the time you are taking to help me out. To answer your question, no, the first ID number may only use 100 fields, the second may use all 117 fields. Because of that, I need to do a match, or choose or lookup of some kind. The field name is always the same, an example is, &quot;address&quot; in the Entity field is always spelled the same way for each occurance of &quot;address&quot; and must return the information in the Data field for that record to the &quot;Address&quot; field in my new table. It sure sounds complicated to me!!!! <smile>

 

[kathryn]

OK, then we are going to need a few steps. In the first step, we will find a record that is using all 117 fields. Then we will use this IDNumber to create the fields in the table. Then we will create the table with those fields. Then we will go through the entire set of data and load it into the new table.

Here goes: (WARNING: this will NOT work on the first try!)

The first query you need will get one piece of information: the ID number with the most fields.

The SQL is:

SELECT TOP 1 IDNumber
FROM tblFieldData
GROUP BY IDNumber
ORDER BY Count(*) DESC;

Open a new query and from the menu choose View - > SQL View.

Copy and paste this SQL into the window that appears. Change tblFieldData to your table name. Choose View - > Design View and you should be able to figure out what the query does. Note the textbox up in the menubar with a &quot;1&quot; in it. That is what makes the query return only one record.

Save the query as qryIDNumber.

Now we will use that query to get all the records for that ID.

Create a new query and add your data table and qryIdNumber to it. Join the two on IDNumber. Move just the Entity field down to the grid. Run the query. It should return the list of fields. Name the query qryFieldNames.

Next, we will use that query in code to create a table with those field names.

Create a form and put a button on it, with the caption &quot;Create table&quot; In the click event of the button, copy and paste the following.


Dim rst as recordset
dim tdf as tabledef

'open a recordset based on the query, it will contain
'the list of field names
set rst=currentdb.openrecordset(&quot;qryFieldNames&quot
'move to the first field name
rst.movefirst


'create the new table, I called it tblData
Set tdfNew = dbsNorthwind.CreateTableDef(&quot;tblData&quot

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection of the
' database.
rs

'use the do...loop to step through all field names until you reach an
'EOF (end of file)
do until rst.eof
.Fields.Append .CreateField(rst!Entity, dbText)
rst.movenext
loop

' Append the new TableDef object to the
' database.
currentdb.TableDefs.Append tdfNew

OK, save this and click the button on the form and let me know what happens. If by some miracle, there are no typos or logic errors, you will have a new table with all the fields.

Kathryn

 

[jforstrom]

Concepts01,
Perhaps I'm a little late here (haven't checked Access Modules in a couple weeks), and maybe I'm missing something ... but it seems that all you need to do is create a Crosstab query to organize the data into the format you need. Then, perhaps a MakeTable query based on the Crosstab.

The Crosstab query (similar to Excel Pivot Table) would look something like this:

TRANSFORM First(Table1.Data) AS FirstOfData
SELECT Table1.IDNumber
FROM Table1
GROUP BY Table1.IDNumber
PIVOT Table1.Entity;

The resulting query will display one row for each &quot;IDNumber&quot; (i.e. each employee), and the values in &quot;Entity&quot; will show up as the field names.

If some employees don't have all 117 fields -- not problem, they will just show a blank.

As long as all 117 fields show up at least once in the million row table, they will show up as a field name in the crosstab.

You don't even need to have any one IDNumber that contains all 117 fields.

And I'm sure it's faster than doing anything in code. I have one database design where I purposely put some data in the format you are converting from because it is a quick and flexible way to view data ... especially if the &quot;fields&quot; in the Entity field are changing. I run it on tables with 100K rows, and it runs in a couple of seconds.

The MakeTable would certainly be slow with 1M records, but i don't think you can get around that ...

 

[simarik]

Hello, my problem is very similar to this one. There is a separate thread that mentions the problem in detail, called &quot;Creating a dynamic column report...&quot; or something, posted on the 29th of last year (gosh, 'tis been a year, and still no reply?

I have a cross-tab query that groups fields from another query as columns and under these columns, sums up a value (hours field).I am creating a report based on the cross-tab query.

See, as jstrom mentions above, &quot;If some employees don't have all 117 fields -- not problem, they will just show a blank.&quot;, the report would work just fine but only show dates as it's columns (transformed fields) that are present in the query already. If none of the records have summed up values under a particular date, that date would not show up at all! Basically put, there would be breaks in the dates. I don't want that.

My supervisor told me this, &quot;I want a report. This report has to be printed every tuesday. The data printed should be for last week, Sunday through Friday data. If there is no data for a particular date during this week, put a '0' in that crosstab (crossreferenced (sum) field)&quot;

See? So, I created a report based on the part of the crosstab query. The other part, dates that go as columns, I converted them into unbound fields in the report. These dates are filled with input from the user (active-x calendar on another form returns the start date to the report). I am trying to make this work for printing anyday, not just tuesday. The values under these fields, the sum, i do not know how to get...how can i reference a field that has been transformed into a column in the cross-tab query? How can i reference the 'sum' field? Please help. Many thanks!

Narayanan Srinivasan
coolie91@hotmail.com

Brothers and Sisters from India, if you have any recent news regarding F-1 to H-1B visa transfer, please e-mail me. Thanks.