Query with result if relation not found

  • Thread starter Thread starter Bob G
  • Start date Start date
B

Bob G

Is it possible to create a query where you can also generate a result if a
relation is NOT found?

Here's a simple example.
I have two databases
1. database 'articles' with fields articlenumber and suppliernumber
2. database 'suppliers' with fields articlenumber, suppliernumber and price.

The relations between the database are with articlenumber and
suppliernumber.
Is it possible to make an Access query with result : articlenumber,
suppliernumber, price

BUT, if a combination articlenumber+suppliernumber is NOT found in the
suppliers-database
I want a result too, but with price = zero.

Is this possible? Maybe in more then one step?

Bob
 
HI

MAY BE THE BELOW QUERY WILL HELP U

SELECT ARTICLE.ID, SUPPLIER.ID,
IIf(IsNull([SUPPLIER.PRICE])=True,0,[SUPPLIER.PRICE]) AS PRICE_AMT, *
FROM ARTICLE LEFT JOIN SUPPLIER ON
ARTICLE.ID = SUPPLIER.ID;


PLS LET ME KNOW IF THIS HELPS U

THANKS

WITH REGARDS

SUNIL.T
 
I think you are talking about two tables versus two databases.

If you are using the query grid to build the query and you have a line between
the two tables then
Double-Click on the JOIN line
In the dialog box, select the option that says All records in Articles and only
.... in Suppliers

Close the dialog and run your query.
If you have two join lines - ArticleNumber to ArticleNumber and SupplierNumber
to SupplierNumber then do both join lines

You should see all the records in the Articles and blanks where there is no
related record in Suppliers. You can force a zero in the price field by
wrapping the price field in the NZ function.

If you are doing this in SQL window (text), you would probably have something like

SELECT Articles.ArticleNumber,
Articles.SupplierNumber,
NZ(Suppliers.Price,0) as ThePrice
FROM Articles LEFT JOIN Suppliers
 
This works fine,

Thx

Sunil said:
HI

MAY BE THE BELOW QUERY WILL HELP U

SELECT ARTICLE.ID, SUPPLIER.ID,
IIf(IsNull([SUPPLIER.PRICE])=True,0,[SUPPLIER.PRICE]) AS PRICE_AMT, *
FROM ARTICLE LEFT JOIN SUPPLIER ON
ARTICLE.ID = SUPPLIER.ID;


PLS LET ME KNOW IF THIS HELPS U

THANKS

WITH REGARDS

SUNIL.T

Bob G said:
Is it possible to create a query where you can also generate a result if
a
relation is NOT found?

Here's a simple example.
I have two databases
1. database 'articles' with fields articlenumber and suppliernumber
2. database 'suppliers' with fields articlenumber, suppliernumber and
price.

The relations between the database are with articlenumber and
suppliernumber.
Is it possible to make an Access query with result : articlenumber,
suppliernumber, price

BUT, if a combination articlenumber+suppliernumber is NOT found in the
suppliers-database
I want a result too, but with price = zero.

Is this possible? Maybe in more then one step?

Bob
Click to expand...
Click to expand...
 
Back
Top