Creating table based on existing table

[Guest]

Hello. I imported data from an mysql Db into access. The structure is such
that all the data is contained in one column. i.e.:
RECORD ID ELEMENT TITLE VALUE
51 144 name joe smith
51 145 Address 36 any street
51 148 Phone 7778889999
78 144 " Fred Jones
78 145 " 17 my street
78 148 " 1112223333
etc...
I'd like to setup a new table so that i can query the table on the ID number
and have all the data associated with the ID number or the RECORD and have
the data display. I am unsure how to do this. I tried the table wizard and
was unsuccessful.
Any suggestions would be appreciated.
Thanks
Steve

 

[Rick Brandt]

Hello. I imported data from an mysql Db into access. The structure
is such that all the data is contained in one column. i.e.:
RECORD ID ELEMENT TITLE VALUE
51 144 name joe smith
51 145 Address 36 any street
51 148 Phone 7778889999
78 144 " Fred Jones
78 145 " 17 my street
78 148 " 1112223333
etc...
I'd like to setup a new table so that i can query the table on the ID
number and have all the data associated with the ID number or the
RECORD and have the data display. I am unsure how to do this. I tried
the table wizard and was unsuccessful.
Any suggestions would be appreciated.
Thanks
Steve

A crosstab query should give you the desired output and then you can use
that to populate a table.

TRANSFORM First(Table2.[TITLE VALUE]) AS [The Value]
SELECT Table2.RECORD
FROM Table2
GROUP BY Table2.RECORD
PIVOT Table2.ELEMENT;

 

[Guest]

Hi Rick, I am a noob to Access. How would I go about constructing a crosstab
query. I tried the wizard but it didn't help.
Regards,
Steve

Hello. I imported data from an mysql Db into access. The structure
is such that all the data is contained in one column. i.e.:
RECORD ID ELEMENT TITLE VALUE
51 144 name joe smith
51 145 Address 36 any street
51 148 Phone 7778889999
78 144 " Fred Jones
78 145 " 17 my street
78 148 " 1112223333
etc...
I'd like to setup a new table so that i can query the table on the ID
number and have all the data associated with the ID number or the
RECORD and have the data display. I am unsure how to do this. I tried
the table wizard and was unsuccessful.
Any suggestions would be appreciated.
Thanks
Steve

A crosstab query should give you the desired output and then you can use
that to populate a table.

TRANSFORM First(Table2.[TITLE VALUE]) AS [The Value]
SELECT Table2.RECORD
FROM Table2
GROUP BY Table2.RECORD
PIVOT Table2.ELEMENT;

 

[Rick Brandt]

Hi Rick, I am a noob to Access. How would I go about constructing a
crosstab query. I tried the wizard but it didn't help.
Regards,
Steve

Past the exact SQL I posted before (shown again below) into the SQL view of
a new query replacing "Table2" with the name of your table. Then you should
be able to switch to design or datasheet view. If the field names you
posted before were not the actual field names you will have to replace those
in the SQL as well.

TRANSFORM First(Table2.[TITLE VALUE]) AS [The Value]
SELECT Table2.RECORD
FROM Table2
GROUP BY Table2.RECORD
PIVOT Table2.ELEMENT;

 

[John W. Vinson]

Hello. I imported data from an mysql Db into access. The structure is such
that all the data is contained in one column. i.e.:
RECORD ID ELEMENT TITLE VALUE
51 144 name joe smith
51 145 Address 36 any street
51 148 Phone 7778889999
78 144 " Fred Jones
78 145 " 17 my street
78 148 " 1112223333
etc...

What's the distinction between ID and RECORD? It looks like RECORD is what
would be the "primary key" of a properly normalized table, but I'm not sure
what ID means: are all the NAME elements 144 and all the ADDRESS elements 145?

That said... I'd create a new empty table with fields like:

NewTableName
Record <Long Integer, Primary Key>
PersonName Text <though I'd suggest splitting into FirstName/LastName>
Address
Phone
<other fields as needed, e.g. City, Postcode, ...>

Then run an Append query followed by a series of Update queries:

INSERT INTO NewTableName(Record, PersonName)
SELECT Record, [TITLE VALUE]
FROM importedtable
WHERE [ELEMENT] = "name";

UPDATE NewTableName INNER JOIN importedtable
ON NewTableName.Record = importedtable.Record
SET NewTableName.Address = importedtable.[TITLE VALUE]
WHERE importedtable.ELEMENT = "Address";

UPDATE NewTableName INNER JOIN importedtable
ON NewTableName.Record = importedtable.Record
SET NewTableName.Phone= importedtable.[TITLE VALUE]
WHERE importedtable.ELEMENT = "Phone";

<etc for all the fields>

John W. Vinson [MVP]

 

[Guest]

Hi John,
Record is the primary key and ID is associated with the name of a column
heading. i.e. 144 = Name. That is how the Db was created within the CMS.
Shouldn't the ID number be in the query? Otherwise how is the column heading
"144" going to be able to associate the the value "Name" properly?
Again, I am a noob.
Regards, Steve

Hello. I imported data from an mysql Db into access. The structure is such
that all the data is contained in one column. i.e.:
RECORD ID ELEMENT TITLE VALUE
51 144 name joe smith
51 145 Address 36 any street
51 148 Phone 7778889999
78 144 " Fred Jones
78 145 " 17 my street
78 148 " 1112223333
etc...

What's the distinction between ID and RECORD? It looks like RECORD is what
would be the "primary key" of a properly normalized table, but I'm not sure
what ID means: are all the NAME elements 144 and all the ADDRESS elements 145?

That said... I'd create a new empty table with fields like:

NewTableName
Record <Long Integer, Primary Key>
PersonName Text <though I'd suggest splitting into FirstName/LastName>
Address
Phone
<other fields as needed, e.g. City, Postcode, ...>

Then run an Append query followed by a series of Update queries:

INSERT INTO NewTableName(Record, PersonName)
SELECT Record, [TITLE VALUE]
FROM importedtable
WHERE [ELEMENT] = "name";

UPDATE NewTableName INNER JOIN importedtable
ON NewTableName.Record = importedtable.Record
SET NewTableName.Address = importedtable.[TITLE VALUE]
WHERE importedtable.ELEMENT = "Address";

UPDATE NewTableName INNER JOIN importedtable
ON NewTableName.Record = importedtable.Record
SET NewTableName.Phone= importedtable.[TITLE VALUE]
WHERE importedtable.ELEMENT = "Phone";

<etc for all the fields>

John W. Vinson [MVP]

 

[Guest]

i tried the append query but receive an error

"Microsoft Off ice Access set 0 fields to Null due to a type conversion
failure, and didn't add 188 records to the table due to key violations, 0
records due to lock violations, and 0 records due to validation rule
violations"

I am not sure how to proceed. Any help appreciated.
Steve

Hi John,
Record is the primary key and ID is associated with the name of a column
heading. i.e. 144 = Name. That is how the Db was created within the CMS.
Shouldn't the ID number be in the query? Otherwise how is the column heading
"144" going to be able to associate the the value "Name" properly?
Again, I am a noob.
Regards, Steve

Hello. I imported data from an mysql Db into access. The structure is such
that all the data is contained in one column. i.e.:
RECORD ID ELEMENT TITLE VALUE
51 144 name joe smith
51 145 Address 36 any street
51 148 Phone 7778889999
78 144 " Fred Jones
78 145 " 17 my street
78 148 " 1112223333
etc...

What's the distinction between ID and RECORD? It looks like RECORD is what
would be the "primary key" of a properly normalized table, but I'm not sure
what ID means: are all the NAME elements 144 and all the ADDRESS elements 145?

That said... I'd create a new empty table with fields like:

NewTableName
Record <Long Integer, Primary Key>
PersonName Text <though I'd suggest splitting into FirstName/LastName>
Address
Phone
<other fields as needed, e.g. City, Postcode, ...>

Then run an Append query followed by a series of Update queries:

INSERT INTO NewTableName(Record, PersonName)
SELECT Record, [TITLE VALUE]
FROM importedtable
WHERE [ELEMENT] = "name";

UPDATE NewTableName INNER JOIN importedtable
ON NewTableName.Record = importedtable.Record
SET NewTableName.Address = importedtable.[TITLE VALUE]
WHERE importedtable.ELEMENT = "Address";

UPDATE NewTableName INNER JOIN importedtable
ON NewTableName.Record = importedtable.Record
SET NewTableName.Phone= importedtable.[TITLE VALUE]
WHERE importedtable.ELEMENT = "Phone";

<etc for all the fields>

John W. Vinson [MVP]

 

[John W. Vinson]

Hi John,
Record is the primary key and ID is associated with the name of a column
heading. i.e. 144 = Name.

Well... no. RECORD certainly CANNOT be the Primary Key of the table you
describe, because a primary key is by definition unique. You have three
records all with 51 as the value of RECORD; that is *not* unique.

RECORD may well be the primary key of a *new table* - that's what my code was
assuming.

That is how the Db was created within the CMS.

What's a "CMS"?

Shouldn't the ID number be in the query? Otherwise how is the column heading
"144" going to be able to associate the the value "Name" properly?

In the *example that you posted* - which is all that I can see - it appears
that the fields [ID] and [Element] are two alternative, redundant ways of
describing the same information. All records with 144 show people's names; all
records with "name" also show people's names. Does this pattern continue
throughout your table? I know that MySQL is capable of fully relational,
properly normalized tables; evidently for some reason this table is in the
(presumably intentionally) denormalized token-value structure, where each
logical record (your RECORD) is spread over multiple table records, with
ELEMENT being the "token" (e.g. "this table record contains the Address") and
TITLE VALUE being the value (e.g. "the address for this entity is 17 my
street"). What other tables (if any) are there in the MySQL application?

Again, I am a noob.
Regards, Steve

John W. Vinson [MVP]

 

[John W. Vinson]

i tried the append query but receive an error

"Microsoft Off ice Access set 0 fields to Null due to a type conversion
failure, and didn't add 188 records to the table due to key violations, 0
records due to lock violations, and 0 records due to validation rule
violations"

I am not sure how to proceed. Any help appreciated.
Steve

Did any data get in at all? Please post the exact SQL of the query, and the
fieldnames, datatypes, and primary key information on the table that you're
appending from and appending to.

It sounds like there are multiple records in the source table with the same
value of RECORD, all with "name" in the ELEMENT field. If you have records

RECORD ID ELEMENT TITLE VALUE
81 144 name Jayne Mansfield
81 144 name Phil Silvers

what result do you want in your table? What is the desired value of the Name
for record 81?

John W. Vinson [MVP]

 

[Guest]

Hi John
The CMS is the open source content management system website that was
created and installed utilising php and mysql to create the website and DB
that is used. When i referred to RECORD as the primary key I meant that it is
the identifier for the individual who filled out the form and auto increments
when someone else updates the DB with new info. You are correct when stating
that it is not the primary key in the format i displayed. Sorry for the
confusion.

I have been able to create the update queries and am adding the fields one
query at a time.
So in other words everything is working!!
Thanks for your help.
Steve

Hi John,
Record is the primary key and ID is associated with the name of a column
heading. i.e. 144 = Name.

Well... no. RECORD certainly CANNOT be the Primary Key of the table you
describe, because a primary key is by definition unique. You have three
records all with 51 as the value of RECORD; that is *not* unique.

RECORD may well be the primary key of a *new table* - that's what my code was
assuming.

That is how the Db was created within the CMS.

What's a "CMS"?

Shouldn't the ID number be in the query? Otherwise how is the column heading
"144" going to be able to associate the the value "Name" properly?

In the *example that you posted* - which is all that I can see - it appears
that the fields [ID] and [Element] are two alternative, redundant ways of
describing the same information. All records with 144 show people's names; all
records with "name" also show people's names. Does this pattern continue
throughout your table? I know that MySQL is capable of fully relational,
properly normalized tables; evidently for some reason this table is in the
(presumably intentionally) denormalized token-value structure, where each
logical record (your RECORD) is spread over multiple table records, with
ELEMENT being the "token" (e.g. "this table record contains the Address") and
TITLE VALUE being the value (e.g. "the address for this entity is 17 my
street"). What other tables (if any) are there in the MySQL application?

Again, I am a noob.
Regards, Steve

John W. Vinson [MVP]