Contracts to be fulfilled

[Alex Motilal]

Dear friends,
I am returning to the Forum after a long gap.
I have a Database which deals with cotton ginning. The cotton seed generated is sold to seed buyers and cotton seed oil mills. The Database have the following tables:
tblGinning:
GinningID
DateOfGinning
CottonGinnedInQuintals
LintGeneratedInQuintals
SeedGeneratedInQuintals
tblBuyer
BuyerID (PK)
BuyerName and other related fields
tblContract
ContractID (PK)
BuyerID (Child Key)
ContractDT
ContractQty
RatePerQtl
tblReadyIntimation


(in this table the seed generated is intimated to the buyer on date to date basis, so that he can make payment and lift the seed)
IntimationID(PK)
ContractID (Child Key)
DateOfIntimation
SeedReadyInQuintals
What I need is, a query to show the quantity available to be intimated to the remaining contracts (Contract Wise) after deducting quantity already intimated. I tried and failed. I hope the experts can help.

[June7]

What does 'intimation' mean in this usage?

What did you try?

Try: GROUP BY (aggregate) queries on tblContract and tblReadyIntimation that sum the quantities, then join those queries on ContractID (join type 'Include all records from tblContract...'), calculate difference between the summed quantities.

[Alex Motilal]

Thank u June7.
Suppose a buyer has contracted for 1000 quintals(ie100Kgs per quintal) of cotton seed, and when the quantity is produced, he will be intimated that the contracted quantity is available for lifting. He has to lift the quantity within certain period from the date of intimation. The quantity will be intimated in the order of contracts, as and when the seed is ready. During cotton season about 20 to 50 contracts will be entered daily. From the tables, you can see that the tblGinning is not related to any of the other tables. Whatever seed produced on day to day basis, has to be distributed to the contracts and intimated to the buyer. since the volume is more, it is important to know the contracts intimated and the contracts to be intimated.
I will try the method you have suggested.
Alex

[June7]

So 'intimation' means 'sent a notice'?

Think I understand better - you need to determine how much product ginned is available after accounting for previous 'initimations' and then apply that balance to unfilled contracts in order of receipt?

Sounds tricky and I expect several queries will be needed.

Could be more specific if had data to test queries on. Want to provide db for analysis? Follow instructions at bottom of my post.

[Alex Motilal]

Yes Intimation means sending notice or informing and what you have understood is correct.

I do not know why the company uses the word 'Intimation'. Your earlier suggestion to create aggregate queries partially work. They give the result of the balance quantity of seed to be informed against a particular contract.

Since the tblGinning is not related with other tables, is it possible to create a function, so that the available seed produced can be distributed to the remaining contracts?

I will upload the DB for analysis, if the company permits to do so.

Thanks.
Alex

[Alex Motilal]

Dear June7

The following example may help you(all quantities in quintals)


Date Raw Cotton Purchased Seed Expected (65%) Seed Sold Contract #

11/1/2012 10000 6500 6500 001 to 005

11/2/2012 8000 5200 5200 006 to 008


From the above you can see that the seed is sold on the day of Raw Cotton purchase, since seed cannot be stored for a long period.
Now the actual ginning starts after proper heaps of cotton are formed, which may be after a week of the first purchase.

Now when the seed is ready the first contract # 001 will be informed that then the next contract and so on (each contract will be of different quantities)
The underlying query of the report should show the balance available seed to be distributed to the remaining contracts
Alex

[June7]

No, that doesn't help me much. Really need better understanding of data structure and be able to test queries on data and I don't want to try replicating.