Query Relationship, Show All Line (even those without relationship)

stepanstas

New member
Local time
Today, 12:44
Joined
Feb 13, 2009
Messages
5
Hopefully I can explain this well.

I have linked 2 items from 2 tables. Amount and Invoice #. I then plan to have another column to bring back a Name. It looks like this.

Journal Table
Amount | Description | Date | AP (Yes or No) | etc

(the Description contains an Invoice number if the Journal chage is an AP Charge)

AP Table
Amount | Vendor Name | Date | Invoice Number

I will be using the Journal. I want to link back the vendor name. I want to have a relationship between the amount and the invoice number/description only if the AP column is "Yes".

Is my approach the best way to do this?

Basically, long term goal is to have more detail in the Journal. If it is an AP charge, which vendor, if not, just leave it blank.

So far, i was able to get this to work, but because of the relationships, it removes anything that is not AP.
 
Probably the simplest way to do it is by using the Immediate If (Iif) function in your query, for example:

SELECT MyTable.ID, MyTable.Field1, IIf([Field1] like "banana",[Field2],"") AS Expr1
FROM MyTable;

The above query displays rows from MyTable (all rows) - a column named Expr1 is also shown - on rows where Field1 is the word "banana", Expr1 contains the value from Field2 - on all other rows, it's blank.
 

Users who are viewing this thread

Back
Top Bottom