DataBase - Determine final status of detail records related to a master record

Asked By Annette

01-Mar-08 11:59 AM

I have two tables - PO and PODetail. The PO table tracks such things
as date, po number, vendor, etc. The PODetail table links to the PO
table and contains detail information such as item, account number,
quantity and paid status. If there is a PO with 5 PODetails, each
entry in the detail can have it's own paid status. For example the PO
123 has 5 details and 4 have a paid status of "yes" and 1 has a paid
status of "no".

I have a query that lists all PO's and their status. If a PO has any
PODetail records that has at lease one paid status of "NO", I would
like the query that lists all PO's to list the status of unpaid. If
all the PODetail records are paid, then the PO status would be paid.

How can I parse the PODetail records to determine the final status of
PAID or UNPAID in the PO query?

28-Feb-08 08:46 AM

Post the SQL of your query. (Menu View: SQL).

You can use an exists clause to determine if any one of the detail records
has a paid status of No.
Assumption: You are using a Yes/No field for the Paid Status
You can use a calculated field with a subquery
Field: Paid: EXISTS(SELECT * FROM PODetail WHERE [PaidStatus] = False and
PODetail.[PO ID] = PO.[PO ID])

That will return True (-1) if the PO has any detail record that is not paid
and zero if there is no such record.

If you need to see the words Paid or Unpaid you can show that in the query
or use the format property of a control in a report (or on form).  You could
also use something like this in a query.

Field: Paid: IIF(EXISTS(SELECT * FROM PODetail WHERE [PaidStatus] = False
and PODetail.[PO ID] = PO.[PO ID]),"Unpaid","Paid")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

28-Feb-08 08:51 AM

Use a subquery to determine if there are any related records where the Paid
field is unchecked.

This example assumes the 2 tables are joined on a field name POID:
SELECT PO.*
FROM PO
WHERE EXISTS
(SELECT PODetail.PODetailID
FROM PODetail
WHERE (PODetail.POID = PO.POID)
AND (PODetail.Paid = FALSE));

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.