CODEWITHSUNDEEP
sqlms-access
malvin
malvin

Reputation: 80

Query on 3 tables

I've three tables in this mode:

Time1 with column "Data" in format gg/mm/aaaa

Manual with columns "valMan" and "dataora" same format.

Automatic with columns "valAuto" and "dataora" same format.

I must find values "valAuto" from table Automatic where Data.Time1 is = dataora.Automatic and if the value is null I take this value from valMan.Manual in the same Data.Time1 = dataora.Manual

In access sql or vb for access.

Is very important for me... Thank you a lot!!!

Upvotes: 1

Views: 79

Answers (2)

Adriaan Stander
Adriaan Stander

Reputation: 166356

For MS Access you can try Iif and IsNull

SELECT  Time1.Data, 
        IIf(IsNull([valAuto]),[valMan],[valAuto]) AS Expr1
FROM    (Time1 LEFT JOIN Automatic ON Time1.Data = Automatic.dataora) LEFT JOIN 
        Manual ON Time1.Data = Manual.dataora;

or

Nz

SELECT  Time1.Data, 
        Nz([valAuto],[valMan]) AS Expr1
FROM    (Time1 LEFT JOIN Automatic ON Time1.Data = Automatic.dataora) LEFT JOIN 
        Manual ON Time1.Data = Manual.dataora;

Upvotes: 1

fancyPants
fancyPants

Reputation: 51868

Don't know about access, but this should do it in MS SQL Server

SELECT
ISNULL(a.valAuto, m.valMan) AS whatIWant
FROM
Automatic a
INNER JOIN Time1 t ON a.dataora = t.Data
INNER JOIN Manual m ON t.Data = m.dataora

The ISNULL command replaces valAuto with valMan if valAuto is null.

Upvotes: 0

Related Questions