Populate Datapage Column According To A Separate Table's Field

[kme]

[Please skip to the first comment I made]

 

I have a slightly complicated procedure I'd like to implement.

 

DataPage D's data source is from View V

In the Results Page of DataPage D, I'd like to include a field F that's from Table T

But Table T is not included in View V.

 

This is a sample Table T:

 

[iD] [fieldF]

1        A

1

1        B

2

2        C

2

2

3

3

 

This is a sample View V, according to Table T:

 

[iD] [fieldF]

1       A

1       A

1       A

1       A

1       A

1       A

2       C

2       C

2       C

3

3

3

3

3

3

 

ID from Table T = ID from View V

 

Here is how field F in View V is inputted:

The top most field F value for ID 1 is A, so all field F for corresponding ID's will be A.

The top most field F value for ID 2 is C (blank is not included), so all field F for corresponding ID's will be C.

There are no field F value for ID 3, so all field F for corresponding ID's will be blank. 

 

Periodically the top most row will be removed, and then new values will be added, etc. though I'm not sure if that really matters.

 

Now how do I do this?

 

Here are some problems I'm thinking:

- There needs to be a way to link Table T's ID with View V's ID, when Table T is not included in View V

 

Here are the steps I'm thinking:

1. In View V, create a new field/column for the Results Page, named fieldF

2. For each ID in View V, select Table T ID's field F

3. Loop through the field F values from top to bottom to search for the top most value, like I explained above. If there are no values, leave blank.

4. Populate all of the View V's field F for each ID

 

..How do you code this?! 

 

Thanks for any help 

[kme]

This is the solution:

 

SELECT TOP 1 RonOpportunity

FROM Routing_1

WHERE RonOpportunity !='' AND Current_Open_NEF_Data_Ship=AcNum

[MayMusic]

You can add calculated field and use SQL syntax probably like

 

Select XXX From TableName where XXXX

[kme]

This is the SQL that works, for Table A and Table B.

Table B is the data source for the DataPage, and Table A isn't. 
I want to update Table B's column "City" according to Table A's column "Letter".

 

TABLE A

+----------+-------+
| Number | Letter|
+----------+-------+
| 1           |   A     |
| 1           |          |
| 1           |          | 
| 2           |          |  

| 2           |          |  

| 3           |          |  

| 3           |   B     |
| 3           |          |
| 3           |   C     |
+----------+-------+

 

TABLE B

+--------+-------+
|   AC    |  City  |
+--------+-------+
| 1         |     A   |
| 1         |     A   | 

| 1         |     A   | 

| 1         |     A   | 

| 2         |          | 

| 2         |          | 

| 2         |          | 

| 2         |          | 

| 3         |     B   | 

| 3         |     B   | 

| 3         |     B   | 

+--------+--------+

UPDATE TableB
SET City = Letter
FROM
(
SELECT Number, Letter, ROW_NUMBER() OVER(PARTITION BY Number ORDER BY number ) AS SortOrder
FROM TableA
WHERE Letter IS NOT NULL AND Letter != ''
) AS A
WHERE SortOrder = 1 AND TableB.AC = A.Number;

Where do I add this code and format it for it to work in the DataPage?