Data Entry - REPEATING VALUES in a field (record to record) 2

[timbar]

I have a simple question. It probably has a simple answer. In entering data into a Access table, how do I repeat a value (a number) in a field from record to record, without having to type the value in for each record? Is there a non-programming function in Access? Or, do I have to write a program to do this? A cut and paste of previous field entries (all with the same data value) doesn't seem to work.

Thanks in advance for your input.

Tim Barbour

 

[GingerR]

you can hit keys ctrl+' (control + apostrophe) to copy the data from the same field in the previous record to the current field in the current record.

if all the data is always the same, you can set the defaults in the design of the table. however, i'm guessing that your data changes from time to time, it just is the same for a few records during a short period of time?

what exactly are you up to? maybe there is a different solution for you. how many fields are containing the same data as the previous record? how come so much repeated data?

 

[timbar]

I guess I wasn't clear enough in my initial message. I thought so, after I sent it.

I have a table with 171,389 records of x,y,z coordinate data in the first three olumns (fields). In the fourth and fifth fields I need to enter an integer number to represent the indices for subsequent mesh generation based on the x,y,z data. These numbers (fourth and fifth columns)go like this:
1 1
1 2
1 3
1 4
. .
. .
. .
the 1 repeats for 367 records and then 1,2,3,4 repeats incrementing by 1 to 367
then, 1 changes to 2 and repeats for another 367 records and the 1,2,3,4 repeats again incrementing by 1 to 367
this process repeats until the end
467 1
467 2
467 3
467 4
. .
. .
467 367 (the 171,389th record 467 x 367 = 171,389)

Entering data for the last column is easy (the incrementing by 1) since I can hold the down-arrow key down after entering a few numbers 1, 2, and 3 (for example) and the new entries are automatically incremented by 1. So this goes quickly. It's just amatter of holding the down-arrow key down until you get to the 367 entry in the set.

Entering the other column (say entering the number 5) has to be done manually (as I currently know it) one at a time for 367 records. I'd have to go through this process of entering the same number 367, then increase the number by 1, do it again for 367 times, and then continue until I enetered the number 467 in the last set of records 367 more times.

The point is although the data entry is possible, my index finger will be worn out after entering 171,389 numbers (1 for 367 times, 2 for 367 times, and finally 467 for 367 times).

If I could cut and paste field numbers that might ease the data entry. But, I guess doesn't allow that. I could write a script to provide automatic data entry. Although I'm write script for other applications, I'm not familiar with Access programming. I could split my 171,389 table into samller tables (467 of them) and set the default value for the particular field I'm interesetd in filling with the correct number (1 to 467) for that particular table. Then I could append all the tables together. I could create the numbers I wish to enter in Excel and then import the Excel file into Access. There are lots of ways to get the data entry done.

What I'd like is a recommendation on the most automatic method possible, with the least amount of hand entry. Any advise that you have, would be greatly apppreciated. It's not that I have this one table to fill-in, but will have have many more lengthy tables that require the same data entry with increments.

Thanks again for your advise.

Tim Barbour

 

[JeremyNYC]

First off, Ginger, have a star. That's wacky! I never knew that. Works in a bound form, too. Very cool.

Next, Tim, I would do this in excel, if you aren't up to writing the code that would be needed. It will be much easier to do there than in access. If I were to do it myself, I'd probably write the code, just cause it would be fun.

Hmm. What about having two tables and putting them together in a query with no join? Try that.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[GingerR]

thanks jeremy. i think a teacher in a beginning level class taught me that. go figure.

anyhow, i too would lean toward excel. cause i'm lame.
but since you have to do this over and over again.....i might work on a little code for you.

questions:
1) why 367? is it always '367' or is that derived some how by how many records you have?

2) what are you field names?

3) how do i know where to start the numbering? are x,y,z sorted ascending or something? are the x,y,z coords duplicated for 367 records? or are they diff? is there an ID field? or autonumber or anything? just trying to figure out where/how to number them.

hope you're in not too big of a hurry. i can probably come up with something this weekend.

 

[timbar]

Jeremy and GingerR thanks for your input.

The Excel approach won't work since Excel is limited to 65,536 (256x256) records. I have similar file size limitations in other apps as well, like AutoCAD. Some of my files are in the 170,000 + records range.

Rather than asking either of you to write code at this point (I have more testing to do before I conclude that I absolutely need to add the additional table fields and data that I've described previously.), let's wait You probably have better things to do with your weekends. Unfortunately, I'm hooked, 7 days a week.

If I use a query to combine the information from two tables, I think I'm still going to have to enter the additional data in one of the tables, so that may not be the solution. I'm trying to come up with a solution that is automatic.

With Excel out (file size limitation) and the query table-combination possibly still requiring the additional data input, coding seems to be the only solution. One of my requirements is to be able to export the completely filled in table as an ACSII text file. I can't export the results of a query. I need a single table that can be exported.

So, for now, I'd like to say thanks for your informative input. It's been a big help.

For your information, my table has the following fields:
Field 1: ID (autonumber primary key, 171,389 records)
Field 2: X (X coordinate in a 3D space)
Field 3: Y (Y cooridnate in a 3D space)
Field 4: Z (Z coordinate in a 3D space)

I need to add:
Field 5: M (the row number in a 2D matrix)
Field 6: N (the column number in a 2D matrix)

The number of records in the table can vary. The test table with 171,389 records representing a 367 x 467 matrix). 367 x 467 = 171,389. Another table might have 368 x 489 records.

The table numbers are actually data about the elevations of the ground surface in different geographic areas of the good ole USA. The X,Y,Z values (in fields 2,3,4) are the ground surface coordinates and elevations on a regular spaced grid (30m typical X and Y spacing). X and Y are sorted ascending (X first, then Y). There are no duplicates in the X and Y data. The Z (ground surface elevations) may duplicate.

An example of the data structure and information follows:

ID X Y Z M N
1 391770 4317180 2872 1 1
2 391800 4317180 2876 1 2
3 391830 4317180 2871 1 3
.
.
367 402750 4317180 2989 1 367

368 391770 4317210 3204 2 1
369 391800 431720 3207 2 2
.
.
17389 402750 4331160 3349 467 367

X and Y increment by 30m each time. The elevation Z is the ground surface on these 30m grid spacing.

If one or both of you (Jeremy and Ginger) have e-mail addresses that I can send an example file to you, I'd be happy to do that. If I have not answered you're questions, please let me know.

Thanks again for your help.

Tim Barbour
Colorado

 

[GingerR]

hi tim. code's the best choice for you.
i whipped up a little sample. it would take care of the test table you talk about. you have to tweak a little (table name).

paste this code into a new module, look thru the comments and tweak whatever you need to, then run it (put your cursor in front of the word FUNCTION and hit the run button on the menu bar):

Public Function NumberTim()
Dim rs As DAO.Recordset
Dim strSQL As String
Dim m, n, intMaxN, intMaxM As Integer

'initialize m,n to 1; will be used in looping
m = 1
n = 1

'set intMaxN; dont know how you know what this is, you can surely set it some diff way or just
'change it in here each time
intMaxN = 367


'TIM is the name of my table. you will have to tweak
'Open Recordset
Set rs = CurrentDb.OpenRecordset("TIM")

'calculate intMaxM as recordcount/N (in test, it will be 467)
intMaxM = rs.RecordCount / intMaxN

'Go to first record, then loop thru updating M and N fields
rs.MoveFirst
    For m = 1 To intMaxM
        For n = 1 To intMaxN
            rs.Edit
            rs!m = m
            rs!n = n
            rs.Update
            rs.MoveNext
        Next n
    Next m
    
'Close recordset and db
rs.Close
CurrentDb.Close
End Function

***************
hopefully this will be enuf for you to go on your own. if not, let us know!!

g

 

[timbar]

Wow! I'm impressed. Hopefully, I'd be able to return the favor someday. Will try code tomorrow. Will let you know how things go.

Thanks again. You're terrific.

Tim