Query 1:N

[Arturo Guzman]

Hello i have a problem with a query of the folowing table:

Client

IdClient
Name
Adress
......
IDBANKBUY
IDBANKSELL

Banks

IDBANK
NAME
ADDRESS

IDBANKBUY and IDBANKSELL is relationed to IDBANK, so I need to get the
client data (Name,Address) and the name of the bank where the client
buys and sells.

Im using Access 2003.

Regards.

[teddysnips]

Arturo Guzman wrote:

Hello i have a problem with a query of the folowing table:

Client

IdClient
Name
Adress
.....
IDBANKBUY
IDBANKSELL

Banks

IDBANK
NAME
ADDRESS

IDBANKBUY and IDBANKSELL is relationed to IDBANK, so I need to get the
client data (Name,Address) and the name of the bank where the client
buys and sells.

<AIRCODE WARNING>

SELECT
Banks.NAME,
Banks_1.NAME
FROM
(Banks AS Banks_1 INNER JOIN
Client ON Banks_1.IDBANK = Client.IDBANKBUY)
INNER JOIN Banks ON Client.IDBANKSELL = Banks.IDBANK;

</AIRCODE WARNING>

You might need to make the JOINs OUTER JOINs if there is a possibility
of NULL values in the Client FKs

Since I've provided this service, can I point out some flaws in your
design - mainly cosmetic, but which are very irritating for picky
people such as I?

Name your tables in the PLURAL. Clients and Banks

Don't use Reserved Words like NAME. It's fine in your SQL, but if you
ever want to use it in VBA you'll have to bracket it. Better to name
it BankName, or ClientName. In any case that way you'll know, when you
see it which table it comes from.

HTH

Edward

[Arturo Guzman]

te********@hotmail.com escribió:

Arturo Guzman wrote:

>Hello i have a problem with a query of the folowing table:

Client

IdClient
Name
Adress
.....
IDBANKBUY
IDBANKSELL

Banks

IDBANK
NAME
ADDRESS

IDBANKBUY and IDBANKSELL is relationed to IDBANK, so I need to get the
client data (Name,Address) and the name of the bank where the client
buys and sells.

<AIRCODE WARNING>

SELECT
Banks.NAME,
Banks_1.NAME
FROM
(Banks AS Banks_1 INNER JOIN
Client ON Banks_1.IDBANK = Client.IDBANKBUY)
INNER JOIN Banks ON Client.IDBANKSELL = Banks.IDBANK;

</AIRCODE WARNING>

You might need to make the JOINs OUTER JOINs if there is a possibility
of NULL values in the Client FKs

Since I've provided this service, can I point out some flaws in your
design - mainly cosmetic, but which are very irritating for picky
people such as I?

Name your tables in the PLURAL. Clients and Banks

Don't use Reserved Words like NAME. It's fine in your SQL, but if you
ever want to use it in VBA you'll have to bracket it. Better to name
it BankName, or ClientName. In any case that way you'll know, when you
see it which table it comes from.

HTH

Edward

It was an example. Thank you very much.