ms-accessms-access-query

Access query: compare records in a group then select one based on a criteria in another field

I've an Access query as shown below & I want to generate the yellow highlighted fields
Description:
For ItemID there are group(s) and a single duration, for each group there are days before and days after.
The target (3 conditions):
1- If a group elements has full duration i.e. (Full_Dur) flag is Yes for both, the required 2 fields (D_before_G & D_after_G) shall be same as D_before & D_after as in group "2".

2-If a group elements has one full duration & the other is less the duration i.e. one (Full_Dur) flag is Yes & the second is No, the required 2 fields (D_before_G & D_after_G) shall be same as D_before & D_after with flag "No" as in group "1" ItemID "2010".

3- If a group elements has less than full duration i.e. (Full_Dur) flag is No for both, the required 2 fields (D_before_G & D_after_G) shall be same as lowest D_before & the lowest D_after.

Notes:
Group elements are only 2 numbers & also not repeated within an "ItemID"
Fields D_before, D_after & Full_dur are calculated fields in a query.

ID ItemID Group Duration D_before D_after Full_dur D_before_G D_after_G
1 2010 1 65 65 0 Yes 15 19
1 2010 1 65 15 19 No 15 19
1 2010 2 65 65 0 Yes 65 0
1 2010 2 65 65 0 Yes 65 0
1 2010 3 65 0 0 No 0 0
1 2010 3 65 0 0 No 0 0
2 1040 1 100 100 0 Yes 100 0
2 1040 1 100 100 0 Yes 100 0

enter image description here

Solution

  • You can solve this with two queries.

    The first query is a GROUP BY-query finding the lowest value for Full_dur field. I.e. if there is a No-record it will return "No" otherwise it return "Yes" as Field YesOrNo.

    In the second query you make two Dlockup() for the values in D_before and D_after filtering on ID, ItemID, Group and the YesOrNo field from the first query.

    The main querys name in this example is qryData.

    The first query (qryDataFindNo) looks like this:

    query qryDataFindNo

    The second query looks like this:

    query qryDataResult

    DLookups:

    D_before_G: DLookUp("D_before","qryData","ID=" & [qryData].[ID] & " AND ItemID=" & [qryData].[ItemID] & " AND Group=" & [qrydata].[Group] & " AND Full_dur=""" & [YesOrNo] & """")

    D_after_G: DLookUp("D_after","qryData","ID=" & [qryData].[ID] & " AND ItemID=" & [qryData].[ItemID] & " AND Group=" & [qrydata].[Group] & " AND Full_dur=""" & [YesOrNo] & """")