unmatched records for multiple reasons query/report (1 Viewer)

[tinyevil777]

Afternoon All!

I have a slightly confusing question regarding a query/report.

My database has two tables:

• hold
• order

These two tables have one related field. In hold, this field is called "PO". In order, this field is called "DocNo".

I have built a query to show me any record that do not match. That's all fine!


However, my aim is to build a report that will display ALL records from Hold. For anything that is matched, i want under "Reason", the words "#order no" to be displayed, but for anything that is un-matched, the reason to be blank.

Is this possible? Please find attached a copy of the database for your persual!

Thank you in advance!

Tom

 

[jdraw]

Here's a query you can try

SELECT
 hold.ID
, hold.SupCode
, hold.Ref
, hold.Date
, order.ID
, hold.Goods
, IIf([hold].[PO]=[order].[docno],"#OrderNo"," ") AS Reason
FROM hold LEFT JOIN [order] ON hold.PO = order.DocNo;

However, I strongly urge you to structure your tables. And use meaningful field names.
Look up the purpose of primary and foreign keys.
You may also want to be cautious of the # symbol which has special meaning in Access.

Some links you may want to pursue
http://www.allenbrowne.com/casu-22.html
http://www.utteraccess.com/forum/Suggested-Readings-and-t373096.html

 

[tinyevil777]

Thanks for your reply!

I resolved this by creating an update query that would input an "X" into a field, i then ran the report on the list, got exactly what i needed.

I'm just about to post a new thread though in Queries, so if you fancy helping that would be greatly appreciated!