error update current column name (1 Viewer)

[btamsgn]

Dear Everyone,
I really appreciate "Thank you" all of you supported me.
Sometimes I confused by comments because I am not good English.
So that I usually send with the attachment.
I often wrote wrong code and output wrong result as follows:
I want to update number with current column latest records based on dem455 and type in tablea into "AD455" at column reDvi of table "Tso45". However output wrong result.
for example:type=T45, C1=8=>reDvi=C1 or C3=26=>reDvi=C3, etc.
AD455

Type	Dem455	C1	C2	C3	C4	C5	C6
T45	1174​	08​	14​	26​	28​	43​	45​
T45	1173​	01​	03​	05​	26​	30​	42​
T45	1172​	09​	11​	16​	29​	31​	33​

Looking forward to receiving your fix code and support from you.
The attachment for your ref., Thank you.

 

[June7]

Struggling to understand the rules. Comment in your code "' Construct the SQL statement to get the latest date for the current number and type" - do you mean "latest data"? Define "latest". Your example does not refer to dem455 so how does it apply to decision?

Why no T55 records in Tso45 - why is table named Tso45? What happens to T55 records?

Does Tso45 show results of code or the desired output? Would be nice to have sample of desired output.

 

[btamsgn]

Struggling to understand the rules. Comment in your code "' Construct the SQL statement to get the latest date for the current number and type" - do you mean "latest data"? Define "latest". How is dem455

Why no T55 records in Tso45 - why is table named Tso45? What happens to T55 records?

Does Tso45 show results of code or the desired output? Would be nice to have sample of desired output.

I have 2 table "Tso45" va "Tso55". I took one table Tso45 to check result first
for exmaple: latest records for T45: 1174:8,14,26,28,43,45 then next record 1,3,5,26,30,42,..
then number 26 appeared again then don't take the number because number 26 appeared in latest record in C3 already.
AD455

Type	Dem455	C1	C2	C3	C4	C5	C6
T45	1174​	08​	14​	26​	28​	43​	45​
T45	1173​	01​	03​	05​	26​	30​	42​
T45	1172​	09​	11​	16​	29​	31​	33​

 

[arnelgp]

a wild "guess" since i also find it difficult to understand your logic.
open the form and update your table.

 

[June7]

Please post code between CODE tags to retain formatting and readability.

I really don't see any reason for separate tables for T45 and T55.

Clarify what is wrong about output. Number 18 shows reDvi C1 but there is no 18 in AD455 for T45. Is that what you mean by "wrong result"?

I can use SQL to pull the "latest" reDvi for each number. Queries involve UNION and correlated subqueries and therefore would probably be very slow with large dataset.

SELECT Type, Dem455, C1 AS Data, "C1" AS Src FROM AD455
UNION SELECT Type, Dem455, C2, "C2" FROM AD455
UNION SELECT Type, Dem455, C3, "C3" FROM AD455
UNION SELECT Type, Dem455, C4, "C4" FROM AD455
UNION SELECT Type, Dem455, C5, "C5" FROM AD455
UNION SELECT Type, Dem455, C6, "C6" FROM AD455
UNION SELECT Type, Dem455, C7, "C7" FROM AD455;

SELECT *
FROM AD455_UNION
WHERE Type & Dem455 IN (
SELECT TOP 1 Type & Dem455 FROM AD455_UNION AS Dupe WHERE Dupe.Type = AD455_UNION.Type and Dupe.Data = AD455_UNION.Data
ORDER BY Dupe.Dem455 DESC);

SELECT Tso45.Type, Tso45.So45, AD455_Latest.Dem455, AD455_Latest.Data, AD455_Latest.Src
FROM Tso45 LEFT JOIN AD455_Latest ON (Tso45.So45 = AD455_Latest.Data) AND (Tso45.Type = AD455_Latest.Type);

 

[btamsgn]

If

Please post code between CODE tags to retain formatting and readability.
I really don't see any reason for separate tables for T45 and T55.

Clarify what is wrong about output. Number 18 shows reDvi C1 but there is no 18 in AD455 for T45. Is that what you mean by "wrong result"?

I can use SQL to pull the "latest" reDvi for each number. Queries involve UNION and correlated subqueries and therefore would probably be very slow with large dataset.

SELECT Type, Dem455, C1 AS Data, "C1" AS Src FROM AD455
UNION SELECT Type, Dem455, C2, "C2" FROM AD455
UNION SELECT Type, Dem455, C3, "C3" FROM AD455
UNION SELECT Type, Dem455, C4, "C4" FROM AD455
UNION SELECT Type, Dem455, C5, "C5" FROM AD455
UNION SELECT Type, Dem455, C6, "C6" FROM AD455
UNION SELECT Type, Dem455, C7, "C7" FROM AD455
ORDER BY Type, Data, Dem455 DESC;

SELECT *
FROM AD455_UNION
WHERE Type & Dem455 IN (
SELECT TOP 1 Type & Dem455 FROM AD455_UNION AS Dupe WHERE Dupe.Type = AD455_UNION.Type and Dupe.Data = AD455_UNION.Data
ORDER BY Dupe.Dem455 DESC);

SELECT Tso45.Type, Tso45.So45, AD455_Latest.Dem455, AD455_Latest.Data, AD455_Latest.Src
FROM Tso45 LEFT JOIN AD455_Latest ON (Tso45.So45 = AD455_Latest.Data) AND (Tso45.Type = AD455_Latest.Type);

If Type="T45" and No 18 not found then reDvi="" but my code is still wrong

 

[Gasman]

Looks like the DB is not normalised anyway?

 

[June7]

If Type="T45" and No 18 not found then reDvi="" but my code is still wrong

Didn't answer my questions. Wrong how? Provide example data and desired result. Did you try the queries?