return value from other table in the same Database

[sal21]

I have this sql:

Code: Select all

    OCONN_RS.Open "SELECT AT_4, AGENZIA_4, AGENZIA_CENTRO, REGION, MERCATO_DISTRIB, ZONA FROM TGERARCHIA WHERE TIPO_UO_1='21' AND DES_STATO_PTF='ATTIVO' AND DENDVAL='" & Format(DtaInizio, "mm/dd/yyyy") & "' ORDER BY AGENZIA_4", GAF_CONN, adOpenForwardOnly, adLockReadOnly

in the same databse have Table1 with field name AGENZIA_4x and a field NUMBS.

Is possible to integrate the sql to match AGENZIA_4 with AGENZIA_4x and return the value of NUMBS...
I think is possible with Join statement but dont know the sintiax...

[HansV]

Try

OCONN_RS.Open "SELECT TGERARCHIA.AT_4, TGERARCHIA.AGENZIA_4, TGERARCHIA.AGENZIA_CENTRO, TGERARCHIA.REGION, TGERARCHIA.MERCATO_DISTRIB, TGERARCHIA.ZONA, Table1.NUMBS FROM TGERARCHIA LEFT JOIN Table1 ON TGERARCHIA.AGENZIA_4 = Table1.AGENZIA_4x WHERE TGERARCHIA.TIPO_UO_1='21' AND TGERARCHIA.DES_STATO_PTF='ATTIVO' AND TGERARCHIA.DENDVAL='" & Format(DtaInizio, "mm/dd/yyyy") & "' ORDER BY TGERARCHIA.AGENZIA_4", GAF_CONN, adOpenForwardOnly, adLockReadOnly

[sal21]

Try

OCONN_RS.Open "SELECT TGERARCHIA.AT_4, TGERARCHIA.AGENZIA_4, TGERARCHIA.AGENZIA_CENTRO, TGERARCHIA.REGION, TGERARCHIA.MERCATO_DISTRIB, TGERARCHIA.ZONA, Table1.NUMBS FROM TGERARCHIA LEFT JOIN Table1 ON TGERARCHIA.AGENZIA_4 = Table1.AGENZIA_4x WHERE TGERARCHIA.TIPO_UO_1='21' AND TGERARCHIA.DES_STATO_PTF='ATTIVO' AND TGERARCHIA.DENDVAL='" & Format(DtaInizio, "mm/dd/yyyy") & "' ORDER BY TGERARCHIA.AGENZIA_4", GAF_CONN, adOpenForwardOnly, adLockReadOnly

Sorry me!

But during the work i have changed the sql with a group by, sql new:

Code: Select all

OCONN_RS.Open "SELECT AT_4, AGENZIA_4, AGENZIA_CENTRO, REGION, MERCATO_DISTRIB, ZONA FROM TGERARCHIA WHERE TIPO_UO_1='21' AND DES_STATO_PTF='ATTIVO' AND DENDVAL='" & Format(DtaInizio, "mm/dd/yyyy") & "' GROUP BY AT_4, AGENZIA_4, AGENZIA_CENTRO, REGION, MERCATO_DISTRIB, ZONA  ORDER BY AGENZIA_4", GAF_CONN, adOpenForwardOnly, adLockReadOnly

[HansV]

So what do you want to do with NUMBS? Sum, average, count, ...?

[sal21]

So what do you want to do with NUMBS? Sum, average, count, ...?

Sample the value of NUMBS....

[HansV]

So you want to group by NUMBS?

[sal21]

So you want to group by NUMBS?

Yes!

[HansV]

You should be able to add the field to the SELECT part and the GROUP BY part yourself now.