Filling database with 2000 numbers

[theak47]

Hello
I have one question
I have simple table in Access 2003 with three fields
One is (field name), type - autonumber
one is (field name), type - number
one is (field name), type - text

my 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 like
Autonumber 1 ... 574 ... 2000
Number 1 ... 574 ... 2000
Text 1G ... 574G ... 2000G

Problem 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!

 

[GaryPanic]

Weird but
do-able
I'll skip the first part
but from then onwards
run a qry to
one is (field name), type - number = Autonumber
one is (field name), type - text = G + Autonumber

however this is sloppy thinking
using the same number in 3 fields

use a uniqe number once -
and then a letter code G

no idea on what you are doing - but it does not look quite right
as a rule - cut down on the fields you require espically if you are repeating yourself (sometimes there's no other choice) - keep you table thin not wide
the guys here will tell you thin and long and not wide and short

 

[theak47]

ok i have one field autonuber just to count... and this table will have more than that 2000 numbers

each number is presenting a year 0 - 2007...
each number + G is presenting relationship primary key... so i know what year im using

thats the point

 

[The_Doc_Man]

Here is the issue to consider.

Autonumbers are TERRIBLY dangerous because they are not as well-behaved as you might think. They are guaranteed to be unique but are NOT AT ALL guaranteed to be contiguously numbered (or, another way of saying this is that there is no guarantee of monotonicity.)

Now, the next issue is don't EVER double-store the number like that. Having two fields "yyyy" and "Gyyyy" is bad design from the get-go. There is no need for your "G" field unless you are trying to link to some other abominable table that already has the G-field. The year-number itself is adequate as a key. As a number, probably more efficient than a text field. Duplicating the number in two formats is just plain wasteful.

If you are doing this as an exercise and are trying to learn, let me save you a big headache. You are asking for trouble in doing this with ANY query because there is an issue in stopping your query where you want it to stop.

See, you could write a query to build a record that has an incrementing field, any one of several ways including a DMAX + 1 or a DCOUNT + 1 as a way to increment things monotonically. But... SQL will not want to stop building such records. Even if you put a WHERE clause that says "WHERE [MyYear] < 2008" or something like that, SQL will keep on looking at records, it just won't insert them. There is at least a chance of an infinite loop in the SQL statement depending on exactly how you coded it.

You would do best - if you honestly HAD to do this - to write some VBA code with an explicitly tailored loop that will add the entries you want using a recordset and some explicit code to limit your loops.

 

[theak47]

i must link "G"field with other table, i have it in my relationships

so autonumber is optional and i dont need to have it. and ill delete it from table

Only thing now is that i dont know VBA that good to write loop code ... i know that i must have for 1 to 2000 .... and that way but i dont know how to write it

and can you help me please with this code

 

[Len Boorman]

Keep it simple

Use Excel !!!!!

Keep the table simple

Two Integer fields and a text field.

In Excel easy to produce 1 to 2000, So easy to copy it

So easy to add G to one of them

CXopy and paste Job done.

I am an avid Database developer but I also use other tools available

L

 

[Anthony George]

A cheap and cheerful solution

Here is an old fashioned BASIC way of doing it, it might not be pretty by todays standards but it will do the job and I think

you will understand it.

Just for practice and to keep it simple I have only used 2 fields

(1) Create a Table with 2 fields:

Name Field 1 number and set it's property to a Number
Name Field 2 text and set it's property to Text

Close the Table and name it counter.

(2) Using the Forms wizard, create a Form for the Table counter using the following method:

Select:

Forms
New
Select counter from dropdown box
Select Autoform Columnar
Click OK

Open the Form

Switch to design view by clicking green triangle under the word File

On the tool bar Click View/Properties

When the properties box opens click the Event Tab
Click the Mouse into the white box next to On Open
Select Event Procedure from the dropdown box
Click the 3 little dots to the right of the word Event Procedure

The code window will open

The code will start with something like


Private Sub Form_Open(Cancel As Integer)

End Sub

Between these lines type in the following code so that it ends up exactly like this:


Private Sub Form_Open(Cancel As Integer)
For x = 1 To 2000
DoCmd.GoToRecord acDataForm, "counter", acGoTo, x
Me![number] = x
Me![text] = Str(x) & "G"
Next x
End Sub

Be careful to use the square and round brackets as shown

Also make sure you leave the correct spaces and commas, so it's probably better if you print this out first

Close all of the form boxes and re-open the Form by double clicking it.
The cursor may go busy for a few seconds as 2000 records are being created
Close the Form

Open the Table and it should be how you want it.

You can play around with the code for instance if you wanted a space between the number and G
you could use this line instead: Me![text] = Str(x) & " " & "G"
you could add extra fields with extra numbers, providing you put them just above the words Next x

Ok this is a bit cheap and cheerful but it might get you started and help you to realise just how powerful
a bit of VB code can be.

Regards

Tony

 

[theak47]

thanks man
regards