Complex query and update approach -- need advice

[Tom]

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, then tag them as such 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, True/
False), but I'm struggling with how to automate setting the flag. I'd
like to run the update macro upon db open and 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

 

[Michel Walsh]

If you have one table, with + and - quantities (for due and for shipped),
you can then make a running sum, per contract, per partID. You could also
make a sum, per contract, per partID, for any data prior today. This last
solution is:



SELECT contract, partID, SUM(qty)
FROM positiveAndNegative
WHERE dateStamp <= Today()
GROUP BY contractID, partID



Clearly, if the SUM if positive, that means there are some delay, while if
it is negative, too much material has been send, under that contract. It is
ok if the sum =0, since the required quantity has been shipped, entirely.


What I call a contract is probably what you call SIDS_ID, and what I call a
partID is probably what you call CLINS_ID, but contract and partID are
more... illustrative and meaningful, I think.


Vanderghast, Access MVP