Input with Access table

[theak47]

Hello I have one questionI have simple table in Access 2003 with three fieldsOne is (field name), type - autonumberone is (field name), type - numberone is (field name), type - textmy problem is that i need in number field nubers between1 and 2000 and in text fields same numbers between 1 and 2000 with letter G at the end.that will look likeAutonumber 1 ... 574 ... 2000Number 1 ... 574 ... 2000Text 1G ... 574G ... 2000GProblem is that I dont know how to write a code for that i think that there would be a macro.So can someone help me please!

[Skemcin]

is this being used in a be based application?If so, I'd get out of Access and build this in MySQL or MSSQL.As for the question, a server side scripting language (if this is web based) would be able to do it as would a stored procedure in either of the two databases I mentioned.

[theak47]

This aint web based.Im just using database from Access and im wondering if here is way to fill it without typing values on keyboard, that will realy help me out!

[jesh]

If the value in the text column will always be the value in the number column plus "G", then, rather than storing extra data in your database, why not just append the G when you return the data?Alternatively, you could simply store the letter in the text column and then append the text value in a row to the number value in a row to get the number+"G".

ID,	Number, Text1	  1		 G...547	547	   G

[theak47]

yes, that is good idea, and i now just wondering what the code will be for fill 2000 numbers from 1 to 2000

[theak47]

let's explain ideahave one field autonuber just to count... and this table will have more than that 2000 numberseach number is presenting a year 0 - 2007...each number + G is presenting relationship primary key... so i know what year im usingthats the point

[packrat]

it sounds like you have a lot of redundancy, the only real data is the run of numbers 1-2000. I don't understand the rationale for having an autonumber column and a number column that must necessarily have the same data. Perhapps I misunderstand the purpose of the autonumber column.If your goal is to expose the number in the number column and a string equivalent with 'G' as a suffix you may accomplish this using a access query.

SELECT [Table].[NumberColumn]  [Table].[NumberColumn] & 'G' AS TextColumnFROM  [Table]

You reference the query rather than the table in your select statements.If you need to populate the initial run of numbers I'd recommend using Access VBA, write a little module to populate the initial data. This example might not be a perfect match for what you're trying to do, but it seems close.

Sub PopulateNumberTable()	Dim AdoCmd As ADODB.Command	Dim iNbr As Integer	AdoCmd.ActiveConnection = Access.CurrentProject.Connection		While iNbr < 2000		AdoCmd.CommandText = "INSERT INTO [table] ([NumberColumn]) VALUES (" & iNbr & ")"		AdoCmd.Execute		iNbr = iNbr + 1	Wend		Set AdoCmd = NothingEnd Sub

... Also, if your number + 'G' is the intended primary key and the letter will always be 'G', then use the number as the primary key in itself as there is no differentiation introduced by adding a constant onto the end of the entire range.I also don't see any problem with jesh's suggestion that you append the 'G' at runtime.