SQLTeam.com | Weblogs | Forums

Linking Tables on M$ Access using SQL Syntax


#1

Hello there, how are you?
I'm using Microsoft Access and using SQL syntax:

UPDATE tbl_Operacao_Pendente
SET tbl_Operacao_Pendente.Data_Inicio_Processo = DateValue(IIF(tbl_Operacao_Pendente.Data_Negociacao > Nz(tbl_Operacao_Pendente.Data_Boletagem, 0), tbl_Operacao_Pendente.Data_Negociacao, tbl_Operacao_Pendente.Data_Boletagem))
WHERE (((tbl_Documento_Pendente.Data_Envio_Documento) Is Null)) AND tbl_Documento.Risco_PDD = -1 AND tbl_Documento.Passivel_Bloqueio = -1;

But I'm not so acquainted to SQL that much. I need to link tables in order to make that SQL syntax work, right?

I just can't, I think I've done what I need but it still not working. Can you help me, please?

Image of Linking Tables on MSoft Access https://ibb.co/j2Kt25


#2

Linking tables is only to enforce Referential Integrity, it won't cause your SQL to automatically reference the linked tables.

The syntax I normally use for this (hopefully will also work in Access) is to use a Table Alias:

UPDATE U
SET ...
FROM MyTable1 AS U
    JOIN MyTable2 AS X
        on X.SomeID = U.SomeID

Perhaps:

UPDATE U
SET ...
FROM tbl_Operacao_Pendente AS U
    JOIN tbl_Documento_Pendente AS D
        on D.Data_Envio_Documento = U.Data_Envio_Documento

but my doubt is that you have WHERE tbl_Documento_Pendente.Data_Envio_Documento) Is Null which won't "join" to anything


#3

If you mean linking them in the Relationships Dialog, the thing that looks like an entity relationship diagram. The Relationships Dialog establishes foreign key relationships. It has no effect on joining columns in Access SQL. If you are talking about creating linked tables to a SQL Server database then I don't understand what you're asking.