Break field out into separate table (aka normalize).

[mountainclimber]

Hello. I have created a test table Values1. I have a field in that table that has repeating string values. I am trying to break that field out into its own table with keys etc. (aka normalize).

When I do this with the Analyze Table option under Database Tools, Analyze group, I get "Query input must contain at least one table or query" pop-up.

Here is what I am doing:
1) select table Values1 under tables on the right side "All Access Objects"
2) Click Analyze Table in the ribbon at the top
3) click on Values1 table in the list
4) click next
5) Check "no, i want to decide."
6) click next
7) select, drag, and drop the Report Ccy field out of the default table1
8) Name it RptCcy
9) click next and I get the pop-up "Query input must contain at least one table or query"



Anyone have any suggestions?

[pbaldy]

I've never used that wizard. I'd simply create the new table with the desired fields, and run append queries to populate it using the original table (once for each repeating field).

[June7]

I've never used this tool. I generally avoid the design wizards.

I would run sql actions and/or write VBA procedure to fix structure.

If you want to provide db for analysis, follow instructions at bottom of my post.

[ssanfu]

I am a little confused.

I have a field in that table that has repeating string values.

If you have a table with 1 field that is not a primary key field, you could,would and should have "repeating string values".

Would you explain further what you are trying to do?
What is the table structure?
What are the "repeating string values"?

Further thinking, are you trying to move the "repeating string values" to a different table to create a "look up" table?
A field in the "Values1" table would hold the FK to the look up table PK field?

[mountainclimber]

Would you explain further what you are trying to do?

see below

What is the table structure?

One table, with different fields imported from Excel. It has a primary key and it is called Values1. Values1 is a table that is not normalized. I pull one of the fields that should have its own table (because it is a field of strings that repeat like: a, a, z, c, z, etc... a and z are duplicated) and I get the issue I describe.

What are the "repeating string values"?

text, varchar, strings, non-numerics, etc... they are literally currency codes like USD and GBP the corrispond to that record (each record represents a stock ticker and the currency is the company's reporting currency).

Further thinking, are you trying to move the "repeating string values" to a different table to create a "look up" table?
A field in the "Values1" table would hold the FK to the look up table PK field?

Exactly.

Right now, I am assuming that is because my data isn't "clean". I suspect this because of the "Type conversion Failure" notices I am getting when I bring the table in without normalizing it. I am fixing those (since they have to be fixed anyway) to see if the issue is resolved.