Create a new column problem

[hunoob]

Hi there Everyone! This is driving me real mad! It should be very easy but (as I am very new to access) I just cannot cope with it. My problem is the following: I have a very simple table which has the following fields/ columns:
Table1.Code, Table1.Name, Table1.Address. What I would like to do is to create a new field/column which would be the same as the code field/column. So the new table would look like this:
Table1.Code, Table1.Code2, Table1.Name, Table1.Address. The Code and Code2 table should contain the same data. (I tried to copy and paste but I got an error massage that access can copy only 65.000 rows, but my table has 300.000+ rows.) Thank you in advance for your help!

 

[boblarson]

1. Rename your field that is named "NAME" as that is one Access Reserved Word that will just play havoc with you later. You do not want to name fields or objects the same as Access Reserved Words, but NAME is probably the worst one to do.

2. Can you explain what the 2nd code field is for? Why would you have the same data in another field (that definitely is non-normalized) and simply does not make sense.

3. You don't use copy and paste to add the data. You would use an Update query in this case after you had created the field.

 

[hunoob]

Hi Bob! Thank you for your reply.

"1. Rename your field that is named "NAME" as that is one Access Reserved Word that will just play havoc with you later. You do not want to name fields or objects the same as Access Reserved Words, but NAME is probably the worst one to do."
Actually it is in another language, so it is not reserved word (I have english access, and I have the "Name" column in Hungarian.)

"2. Can you explain what the 2nd code field is for? Why would you have the same data in another field (that definitely is non-normalized) and simply does not make sense."
I would need this column to be trimmed later. I need the last two characters of the code.

"3. You don't use copy and paste to add the data. You would use an Update query in this case after you had created the field."
This is what I don't know how to do it. That is why I wrote this post. I am very very very new to access. (Just started using it)
Thank you for your reply!

 

[hunoob]

Ok! I was able to solve my problem. If someone is interested here is the solution:
I created a new empty column in the table. Then I set copied the Code column to this column: (Update query, select the empty column, modify to: ='tablename'!'columnname'). Then I trimmed the code ( I needed the last two characters): again update query, select the column that needs to be trimmed, modify to: =right('tablename!columnname', 2). And that's it! Thank you!

 

[boblarson]

Just so you know - you really DON'T need that other column. All you need to do is to create that by creating a base query where you can use the RIGHT function to create that extra column and then you use that query in your forms and reports instead of the table. Adding the extra column goes against the rules of normalization and stores redundant data.

 

[hunoob]

Hi Bob! Thank you for your comment. Actually I need this extra column in the pivot table, and as I am totally new in Access, I really do not know how to put this data in the pivot table without creating the column!

 

[boblarson]

Hi Bob! Thank you for your comment. Actually I need this extra column in the pivot table, and as I am totally new in Access, I really do not know how to put this data in the pivot table without creating the column!

All you need is to use a query instead of the table for the basis of your pivot table. That query can have an extra field:

YourNewFieldNameHere:Right([YourOtherFieldNameHere],2)

And that's it. You don't have to base everything on a table, you can use queries almost anywhere you can use a table.

 

[hunoob]

Thank you Bob! I will try it!

Could you please help me with this or five me a hint...

Table1
COUNTRY...... CITY
usa............ new york
uk............... london

and Table2:
NAME.......AGE
michael.... 32
linda........ 25

I would like to create a new table which displays the columns of both table1 and table two, so it should look like this:

Table3:
COUNTRY.........CITY........NAME.......AGE
usa.............new york....michael........32
uk...............london........linda...........25

Please try to help if you can! Thank you!

 

[boblarson]

If the two tables have a common field you can do this, but I don't see any common fields.

 

[hunoob]

They do not have common field...

 

[boblarson]

Then how do you know which goes with which? You'll have to do it manually or add it as a cartesian product (one record of each times the number of the records in the other - so if you have 3 rows in table 1 and 3 rows in table 2 you would end up with 9 rows in table 3 and then need to remove the incorrect ones).