Update Query Help

[MTSPEER]

Hello guys,



I have a table called Table1 and fields called Field1 and Field2 and other fields that are populated. I have over 18,000 records, but I need Field1 and Field2 to be populated.

Field1 Field2
1 100
2 100
3 100
4 100
4 075
4 050
4 025

I need Field1 and Field2 to be populated like this throughout my table. Idk if there is a query that can do this or not. Or do I need to code this. Thanks for your help

[pbaldy]

Populated with what? Start with 100 and subtract 25 for each following instance of Field1? I can't envision a query that can do it. I'd use VBA and loop the table, sorted on Field1, which I assume is already populated.

[June7]

What are the rules for populating these two fields? Why do you have records with 1,2,3 in Field1 but same value in Field2 and 4 records with 4 in Field1 but different values in Field2?

Will probably need VBA.

EDIT: see Paul's reply now - agree with

[Perceptus]

what happens if field1 has more than 5 entries?

[MTSPEER]

Field1 and Field2 are null. And I just need these fields filled with the data I provided over and over again till the last record

[JamesDeckert]

Is this a one time need, or an ongoing thing.
If one time. You could create the columns however you want in Excel, then import into Access or copy/paste into the table.

[MTSPEER]

this is an ongoing things. Like once I import a new file I need those fields to loop that data in until the last record in

[JamesDeckert]

you need to explain the pattern better. In your example field 1 goes up to 4 and then sticks there, does it stay there forevermore? Field 2 starts at 100 for 4 records, then starts decreasing by 25, what happens when it gets to 0, does it go negative. I don't understand the pattern.

[MTSPEER]

Like I need those two fields to repeat themselves after they get to 4 and 025. and just keep repeating that set up numbers until the end of the file. starting at 1 and 100

[June7]

Why? What is purpose of these fields? Why do you have 18,000 records with these fields empty? What is in the other fields? Does it matter which records are assigned these value pairs?

The example give does not fit that latest rule.

[JamesDeckert]

So hard code the first 7 records, then write 4 025 to all the records with null.
Or call a function which is counting which record your on and once the record is past 7, then return 4 025.