make field with sql or iif

[NetworkTrade]

have this:

Key Field1 Field2

1 X 100
2 ABC 123
3 111 aaa
4 X 101
5 asd 1212
6 frr 321

Need this:

Key Field1 Field2 Field3

1 X 100 100
2 ABC 123 100
3 111 aaa 100
4 X 101 101
5 asd 1212 101
6 frr 321 101

If field1=X then Field3 = Field2 (so far so good - no problemo)
If field1 is not X then Field3 = Field 3 of preceeding record per Key sequence

am struggling with this second part.......

 

[Ken Snell \(MVP\)]

SELECT Key, Field1, Field2,
IIf(Field1="X", Field2,
(SELECT F.Field2 FROM
TableName AS F
WHERE F.Key =
(SELECT Max(FF.Key) AS MaxKey
FROM TableName AS FF
WHERE FF.Key < TableName.Key)) AS Field3
FROM TableName;

 

[NetworkTrade]

well that is pretty neat but not quite right..... fixed a slight syntax error
- just ( )) :

SELECT Key, Field1, Field2,
(IIf(Field1="X", Field2,
(SELECT F.Field2 FROM
TableName AS F
WHERE F.Key =
(SELECT Max(FF.Key) AS MaxKey
FROM TableName AS FF
WHERE FF.Key < TableName.Key)))) AS Field3
FROM TableName;

but the sql result is this:

It makes field3 to be the preceeding field2 (when field1 is not x) - which
is pretty cool...am going to keep this code in my library for when that is
needed. What I need to do is alter so that it is the preceeding field3
value....I think I can noodle that thru... much thanks KS

 

[Ken Snell \(MVP\)]

Sorry about the typo.

This change should be what you seek:

SELECT Key, Field1, Field2,
(IIf(Field1="X", Field2,
(SELECT F.Field3 FROM
TableName AS F
WHERE F.Key =
(SELECT Max(FF.Key) AS MaxKey
FROM TableName AS FF
WHERE FF.Key < TableName.Key)))) AS Field3
FROM TableName;
--

Ken Snell
<MS ACCESS MVP>

 

[NetworkTrade]

I don't think one can 'Select Field3 from TableName' because it is not in the
table; it is being created by the query

This is what works now. For sanity I just focused on select and put the Iif
to the side for awhile. In normal design mode selected my Key, Field1,
Field2 and then created this column:

Field3: (Select Field2 from TableName as A
Where A.Key=(SELECT Max(Key) FROM TableName AS F
WHERE F.Field1="X" AND F.Key<=TableName.Key))

It works perfectly. But it is real slow. And if I attempt to scroll down
the results data of the query - it sometimes freezes the application. Have
never had an sql be so slow with such a modest quantity of records in the
trial ~3000. Am afraid it won't work at all once the table grows.

 

[Ken Snell \(MVP\)]

Your original post did not say that the displayed field structure was from a
query; I assumed that Field3 (and all the other fields) was in a table. So,
post the query's SQL statement that is giving you Field3. You'll likely need
to use that SQL as the subquery in my posted example.