Split one filed into many

[ctreeves]

Is there a way to split one field into many?
The field always has the same number of characters, and the characters are always numeric.
Here is an example:
9009000000000000000000000000000000000000
9009000000000000000000000000000000000000
9009000000000000000000000000000000000000
9009000000000000000000000000000000000000

The field is named ACC_LVL.
I want to created 40 new fields named Group 1, Group 2, Group 3...
Each field will have a single value in it from the corresponding position position, ie Group 1 will list the value from position 1, Group 2 will list the value from position 2...

Can this be done with a query?

Thanks,
Cliff

 

[jzwp22]

Welcome to the forum!


If you were to create actual table fields: Group1, Group2...., you would have what would be called repeating groups which violates normalization rules. Typically you would have a series of records (not fields) called Group1, Group2 etc. and then associate the data to their respective groups.

There are ways to extract an individual number from the longer number you presented in your post. I'm just unclear of what you want to do with it once you are able to separate the individual numbers. Could you explain further?

 

[Cliff]

Thanks for the welcome, and for replying.
The table I referenced is a system function table - this was extracted from a bigger database that runs an application for us.
This table lists a function, let's say "Create Reports", then there are 40 different groups each with an access level of 0 to 9. As a user I might be assigned to group 15 with an access level of 5. That is then compared to the Access Level table to see if I have access to that function.
I want to build a report that shows:

Group 1    Group 2    Group 3
   0         5          4

There will also be a table of users that has their individual access level listed.
For now, the person requesting the report would like to be able to separate out the individual columns so they can sort them in Excel. That's the biggest reason I'd like to split the field into 40 fields. I understand it isn't necessarily a normalized way of doing it, but it will work for what we need.

Thanks

 

[Cliff]

Ugh, I just noticed I typed the name of the post wrong, if a moderator could change it to "Split one field into many" I'd appreciate it.

 

[jzwp22]

How do you plan on getting this: 9009000000000000000000000000000000000000 into Access? Are you typing or importing? What is the datatype of the field that will receive the value?

If you try to bring the value in as a number, the field's datatype would have to be a double precision number. The number will then be formatted in scientific notation which will be more difficult to work with.

I would recommend inputting or importing the values into a text field in Access, and then you can use a query similar to the following to extract each individual character and convert each to a number.

SELECT Int(Mid([mynumberastext],1,1)) AS Group1, Int(Mid([mynumberastext],2,1)) AS Group2, Int(Mid([mynumberastext],3,1)) AS Group3, Int(Mid([mynumberastext],4,1)) AS Group4.......
FROM Table1;


The INT() function just converts the extracted character to an integer.

The mid() function has the general format as follows:

mid(string, startposition, length)

In your case, the length will always =1. The start position needs to be incremented for each group.

 

[John Big Booty]

Re: Split one field into many

Have a look at the Mid() function, this should be able to extract the data you require without de-normalising it (your data).

 

[John Big Booty]

Ugh, I just noticed I typed the name of the post wrong, if a moderator could change it to "Split one field into many" I'd appreciate it.

You would also appear to have a sock puppet

 

[Cliff]

How do you plan on getting this: 9009000000000000000000000000000000000000 into Access? Are you typing or importing? What is the datatype of the field that will receive the value?

If you try to bring the value in as a number, the field's datatype would have to be a double precision number. The number will then be formatted in scientific notation which will be more difficult to work with.

I would recommend inputting or importing the values into a text field in Access, and then you can use a query similar to the following to extract each individual character and convert each to a number.

SELECT Int(Mid([mynumberastext],1,1)) AS Group1, Int(Mid([mynumberastext],2,1)) AS Group2, Int(Mid([mynumberastext],3,1)) AS Group3, Int(Mid([mynumberastext],4,1)) AS Group4.......
FROM Table1;


The INT() function just converts the extracted character to an integer.

The mid() function has the general format as follows:

mid(string, startposition, length)

In your case, the length will always =1. The start position needs to be incremented for each group.

Excellent, thank you.
It is a text field, some of the values start with a 0.
So will I need to create those 40 fields in the table first then update them or is there a way to create the fields with the update query?

 

[Cliff]

You would also appear to have a sock puppet

Yikes, I knew I had registered for this awesome forum before, but couldn't find that registration.
If an admin could merge Cliff with ctreeves, it would be appreciated.

 

[jzwp22]

Since you said that you wanted to provided the extracted data to someone in Excel, you can copy the query results and paste them directly into a spreadsheet.

If you still need to put the info into a table, you would create the table first with the 40 fields and then you can just change the query from a select query to an apend query and run it to put the data into the table.

 

[Cliff]

Thanks for all the help!

 

[jzwp22]

You're welcome. Good luck with your project.