Complex query/update approach?

[Haccess]

All,

I need some advice on how to approach a complex query and update problem. I need to identify all records in a contract line items table that are currently late to contract for reporting purposes.

The tables involved are: CLINS (the individual contract line items and associated descriptions, total qty, customer, etc.), SIDS (required delivery dates for subsets of each CLIN total), and LINE_ITEMS (actual deliveries for each CLIN).

Sample Data:
From the CLINS table (among other fields):
CLINS_ID 462
CLIN 0054AX
QTY 80

From the SIDS table (linked to CLINS via CLINS_ID)
SIDS_ID 82
CLINS_ID 462
QTY_DUE 20
DATE_DUE 03/31/07

SIDS_ID 83
CLINS_ID 462
QTY_DUE 20
DATE_DUE 06/30/07

SIDS_ID 84
CLINS_ID 462
QTY_DUE 20
DATE_DUE 09/30/07

SIDS_ID 85
CLINS_ID 462
QTY_DUE 20
DATE_DUE 12/31/07

From the LINE_ITEMS table (linked to CLINS via CLINS_ID):
LINE_ITEMS_ID 201
CLINS_ID 462
QTY_DELIVERED 7
DATE_DELIVERED 1/15/07

LINE_ITEMS_ID 268
CLINS_ID 462
QTY_DELIVERED 8
DATE_DELIVERED 1/29/07

LINE_ITEMS_ID 321
CLINS_ID 462
QTY_DELIVERED 2
DATE_DELIVERED 2/27/07

In this scenario, as of Now(), we've delivered 17 against a cumulative quantity due of 20 (which were all due in March, with another 20 coming due in June, etc.).

How would you approach tagging each CLIN in this situation as "late" to contract? I can add a field in the CLINs table (IS_LATE), but I'm struggling with how to automate setting the flag. I'd like to run the update macro upon db open and possibly after a few other events, so I suppose this should be in a stand-alone module, right?

I'm fairly new to Access and Access VBA, so go easy on me if this is something I should know already.

Thanks!

Tom

 

[Remou]

It would be good if line item was linked to SIDS via SIDS_ID rather than to CLINS.

CLINS->SIDS->LINEITEM

Is this possible?

 

[Haccess]

Remou,

Unfortunately, that's not possible due to the way the contracts are structured by our customer.

The contractual delivery schedule is contained within a standard appendix to the contract called the SIDS (shipping instruction data sheets). The SIDS is broken out by date due, and lists each CLIN coming due on that date and the quantity required. Most CLINs will be split up into quarterly deliveries that together satisfy the CLIN total quantity.

Actual deliveries must also be invoiced on a standard customer form. This form also breaks everything out by the CLIN being satified. Each entry on the customer invoice is called a Line Item, but is really just a direct reference back to the parent CLIN being satisfied.

Therefore, the CLINs table holds the master requirements. The SIDS table holds the required delivery schedule. the LINE_ITEMS table hold the actual deliveries data. All three are related via CLINS_ID following the parent/child structure of the contract.

Thanks,

Tom

 

[Remou]

Sorry, but I still do not see how the Line_Item cannot be marked with the SIDS it is being delivered against, in fact, if you cannot do this, how can you say, programmatically, when that SID has been completed? From the example you provide, it can be seen that the three Line_Items are dependant on one SIDS and four SIDS are dependant on one CLIN. The fact that Line_Items include the CLINS_ID may be useful, but not half as useful as showing the illustrated dependency, which is the SIDS_ID. You say that the line_iyem is a direct reference back to the CLIN beining satisified, but this is not quite true in your example, because the SIDS must be satisified first, when each SIDS is satisfied the CLIN is complete. When I said 'is this possible' I meant, is it possible for you to edit the table to include this field.

 

[Haccess]

Remou,

Technically speaking, I suppose you are correct. However, from the perspective of "satisfying" a single SIDS entry, that makes things a little trickier when you consider the following: Take the data I supplied as an example. If the very next shipment is for 10 pieces (toward the CLIN total quantity of 80), you're now at a total of 27, or 7 over the SIDS quantity required for the 1st Quarter. You have satisfied the Q1 SIDS record, but now you have to deal with an overshipment toward the Q2 quantity due in June.

I'm fairly new to Access and database design in general. However, I wonder if the extra calculation burden to deal with partial shipments and overshipments to each SIDS entry would offset having the direct relational link between the two tables. I chose to treat each secondary table (SIDS required deliveries and LINE_ITEMS actual deliveries) as children of the parent CLIN where the total quantity resides. If it was an incorrect choice, I'm afraid I may have to live with it at this stage as other users are now building dependent apps based upon my schema.

So given this constraint which would indeed be painful to modify at this point, I would still appreciate advice from the community on how to approach the problem at hand. What's the best way to either build an update query or code a module to calculate the total delivered to date, subtract the total required to date, and then update a field in the master CLINS table to set IS_LATE = True?

 

[Remou]

Try this:

SELECT L.CLINS_ID, 
S.Due, 
Sum(L.QTY_DELIVERED) AS Delivered
FROM LINE_ITEMS L 
INNER JOIN (SELECT SIDS.CLINS_ID, 
    SIDS.DATE_DUE, 
    Sum(SIDS.QTY_DUE) AS Due
    FROM SIDS
    GROUP BY SIDS.CLINS_ID, SIDS.DATE_DUE
    HAVING SIDS.DATE_DUE<Date()) AS S 
ON LINE_ITEMS.CLINS_ID = S.CLINS_ID
WHERE LINE_ITEMS.DATE_DELIVERED<=S.Date_Due
GROUP BY LINE_ITEMS.CLINS_ID, S.Due

 

[Haccess]

Thanks for the suggestion, Remou. I ended up receiving some local help from an experienced user who suggested not actually flagging the CLINs since this would be a redundant bit of data being stored.

He came up with the following function:

Public Function PositionToContract(lngCLINS_ID As Long, dtDate As Date) As Long
    
    Dim lngQtyDueToDate As Long
    Dim lngQtyDeliveredToDate As Long
    
    lngQtyDueToDate = Nz(DSum("[QTY_DUE]", "SIDS", "(([CLINS_ID] = " & lngCLINS_ID & ") AND ([DATE_DUE] <= #" & dtDate & "#))"), 0)
    lngQtyDeliveredToDate = Nz(DSum("[QTY_DELIVERED]", "LINE_ITEMS", "([CLINS_ID] = " & lngCLINS_ID & ")"), 0)
    PositionToContract = lngQtyDeliveredToDate - lngQtyDueToDate
    
End Function

I can now call the function from any report where I need to highlight late CLINs by evaluating the numeric result of the function. Late = anything less than zero.