Query by date - to establish cash flow forecast

[Epona]

I have a table that keeps track of various import orders and the costs associated at each step of the process. I need to create a query that will show me the month in which we can expect to spend the money going forward. Lead times vary from 20 to 120 days. The actual lead times per step of the process per item are stored in a different table.

Any bright ideas?

[recyan]

Can you show us the table structure with a few dummy data thrown in & the results that you want out of the query ?
If, that's not possible, we'll have to wait for someone who understands imports.

Thanks

[Epona]

ID	File Number	Supplier	Date OP	Date OP Sup	Date Port Arrival	LC complete	In transit	At Port	Delivered	LC Opening	Bank Set Margin	Insurance	Freight Cost	Customs	Multi Modal C	Inland Trspt	Container Demurrage
5	RM-HC-24/04	Inspire International	2011/11/09			Yes	No	No	No	0	3885000	9669.12	547058
6	RM-HC-39/04	Athi River Mining	2011/11/11			Yes	No	No	No	0	1646880	4936.12	595680
7	RM-HC-50/04	Pwani oil Products	2011/12/12			Yes	No	No	No	0	5211813	12019.08	268431
8	RM-HC-56/04	Sinba Link Pte Ltd	2012/01/25			Yes	No	No	No	0	674784	1825.36	194183
9	RM-HC-57/04	Unison Chemicals	2012/02/07			Yes	No	No	No	100000	2479400	5927.23	226546
10	RM-HC-60/04	Olivia Impex				No	No	No	No	0	6920110	16658.88	698018
11	RM-HC-61/04	Pwani oil Product				No	No	No	No	0	5947344	13744.12	321728
12	RM-HC-62/04	Multi Commodity International				No	No	No	No	0	316111	753.35	25870
13	RM-HC-65/04	Eastern Chemical				No	No	No	No	0	934038	2500.91	214490
14	RM-HC-66/04	Norbright				No	No	No	No	0	101929	285.36	27282
15	RM-HC-67/04	Aprirose House				No	No	No	No	0	803781	3000.27	573384
16	RM-HC-32/04	Sasol Gulf	2011/11/25		2011/12/30	Yes	Yes	Yes	No	30000	9990805	22737.81	448220
17	RM-HC-81/03	Inspire	2011/09/19		2012/01/17	Yes	Yes	Yes	No	0	2650925	6626.61	428468
18	RM-HC_22/04-1	Felda IFFCO	2011/11/11		2012/01/09	Yes	Yes	Yes	No	0	4671088	11889.9	396022

[Epona]

this is the lead time data

Mat Id	Mat Desc	Mat Cat	Source	UOM	Lead Time	Unit COst	Min Order Quantity	OP Time	Sup Time	Sail Time	Cl Time	Country of or
300001	Alkaline Silicate Glass	Raw	Import	kg	72	8	17500	10	40	10	12	Kenya
300005	Caustic Soda	Raw	Import	kg	94	16	16500	10	50	22	12	China
300018	Soda Ash Dense	Raw	Import	kg	94	10	17500	10	50	22	12	China
300010	DPFA(Distilled Palm Fatty Acid	Raw	Import	kg	77	30	16650	10	45	10	12	Kenya
300025	Fragrance Solar Soap-071205	Raw	Import	kg		281
300020	Monstral Blue Paste Colour	Raw	Import	kg	49	171	1200	10	25	5	9	India
300016	Perfume Det.301-575	Raw	Import	kg	60	341	1500	10	30	8	12	Eqypt

[Epona]

LC opening is paid when the LC is approved by the bank, Settlement and insurance + customs paid when goods hit the receiving port, balance is paid when goods have been cleared.

What I am trying to acheive is a report that shows

1. All funds that should have been spent by now, but arent due to customs or transport issues
2. Then all expected expenditure going forward month by month by type (expense)

I hope this makes sense

[recyan]

Thanks for posting the data. Now it'll be easier for many people to try & help you.

Just a few questions :
1) If I am not wrong, you have shown data from 2 tables. How are these tables related ?
2)

1. All funds that should have been spent by now

How do you calculate this, not from query point of view, but with reference to the table & data that you have provided ?

1. ...................................., but arent due to customs or transport issues

How do you calculate this, not from query point of view, but with reference to the table & data that you have provided ?
3)

2. Then all expected expenditure going forward month by month by type (expense)

How do you calculate this, not from query point of view, but with reference to the table & data that you have provided ?

3) Also if possible, can you post the possible query results, based on the Tables & Data that you have provided.

Thanks

[Epona]

Sorry, there is a third table as well, which essentially gives the file number and the item code and the total quantity to be purcased per item. there is a many to 1 relationship between the [file name] and the [item code] in the third table and a one to one link between the [item code] in the third table and the one loaded. User captures everything into a form.

Let me try and expalin the calcs
The starting date for everything is the LC date. and the LC costs are due at that date
The bank sett, ins and freight must be settled when the goods land wich would be the LC date + the lead times for supplier time and sail time

The next date would be the previuos calculated one plus clearing time, at that point we pay Customs and Multimodal

The final two, multimodal and container demurrage are calculated at a set 10 days from the previous total



All the above totals of costs need to be summarised as a total expected expenditure per file number per month, with the first field being the items that fall into a period prior to the current month

As soon as an item is marked delivered it must be excluded


Hope this helps

[recyan]

If I have understood correctly, below are the tables :
tblFileLCDetails

ID - PK
File Number - Unique
Date OP
Date OP Sup
Date Port Arrival
LC complete
In transit
At Port
Delivered
LC Opening
Bank Set Margin
Insurance
Freight Cost
Customs
Multi Modal C
Inland Trspt
Container Demurrage
__________________________________________________ ______
tblMaterialMaster

MatId - PK
UOM
Lead Time
Unit COst
Min Order Quantity
OP Time
Sup Time
Sail Time
Cl Time
__________________________________________________ ______
tblFileMaterialPurchaseDetails

ItemsPurchaseID
FileNumber - FK
MatId - FK
TotalQuantityToBePurchasedPerItem

__________________________________________________ ______

There is a one to many relation between FileNumber from tblFileLCDetails & FileNumber in tblFileMaterialPurchaseDetails.
There is a one to many relation between MatId from tblMaterialMaster & MatId in tblFileMaterialPurchaseDetails.
A FileNumber can be associated with minimum one or a maximum of many MatId in tblFileMaterialPurchaseDetails but will not have the same MatID within the same FileNumber.

Let us know if above is in line with what exists at your end.

Hope : You have the patience & appetite for lot of questions. I just can't help it

Thanks

[Epona]

Hi there,

you have it spot on. I look forward to a solution

[recyan]

I look forward to a solution

Whoa not so fast. Your bad luck, you have a slow coach trying to help you.
Am still trying to understand things.:-(

Check out if the below gives some guidelines in beginning to build up things :

SELECT 
	tblFileLCDetails.FileNumber, 
	tblFileLCDetails.DateOP, 
	tblFileLCDetails.DateOPSup, 
	tblFileLCDetails.DatePortArrival, 
	tblFileLCDetails.LCComplete, 
	tblFileLCDetails.InTransit, 
	tblFileLCDetails.AtPort, 
	tblFileLCDetails.Delivered, 
	tblFileLCDetails.LCOpening, 
	tblFileLCDetails.BankSetMargin, 
	tblFileLCDetails.Insurance, 
	tblFileLCDetails.FreightCost, 
	tblFileLCDetails.Customs, 
	tblFileLCDetails.MultiModalC, 
	tblFileLCDetails.InlandTrspt, 
	tblFileLCDetails.ContainerDemurrage, 
	tblFileMaterialPurchaseDetails.ItemsPurchaseID, 
	tblFileMaterialPurchaseDetails.FileNumber, 
	tblFileMaterialPurchaseDetails.MatId, 
	tblFileMaterialPurchaseDetails.TotalQuantityToBePurchased, 
	tblMaterialMaster.MatId, 
	tblMaterialMaster.UOM, 
	tblMaterialMaster.LeadTime, 
	tblMaterialMaster.UnitCOst, 
	tblMaterialMaster.MinOrderQuantity, 
	tblMaterialMaster.OPTime, 
	tblMaterialMaster.SupTime, 
	tblMaterialMaster.SailTime, 
	tblMaterialMaster.ClTime, 
	DateAdd("d",[SupTime]+[SailTime],[DateOP]) AS GoodsLandingDate, 
	DateAdd("d",[ClTime],[GoodsLandingDate]) AS CustomsAndMultiModalPaymentDate, 
	DateAdd("d",10,[CustomsAndMultiModalPaymentDate]) AS InlandTransportContainerDemurragePaymentDate
FROM 
	tblFileLCDetails 
	INNER JOIN 
	(
		tblFileMaterialPurchaseDetails 
		INNER JOIN 
		tblMaterialMaster 
		ON 
		tblFileMaterialPurchaseDetails.MatId = tblMaterialMaster.MatId
	) 
	ON 
	tblFileLCDetails.FileNumber = tblFileMaterialPurchaseDetails.FileNumber;

Thanks

[Epona]

Definitely looking promising, everything must run from todays date forward or backward - if tht makes sense

[recyan]

everything must run from todays date forward or backward - if tht makes sense

Literally Yes, but from the Data point of view, currently No.
We now have the query as a reference point, based on which, you can let us know, what further you want out of it or don't want of it.
For eg : We now have the 3 calculated Dates. Check if they are OK as per your requirement. Then let us know what further you want to do with them if they are OK.

Thanks

[recyan]

In addition, check out if below gives any further clues :

SELECT 
    tblFileLCDetails.FileNumber, 
    [TotalQuantityToBePurchased]*[UnitCOst] AS TotalQuantityToBePurchasedCost, 
    Round(([TotalQuantityToBePurchasedCost]/[TotalQuantityToBePurchasedCostPerFile])*100) AS ChargesAllocationPercent,
    DateAdd("d",[SupTime]+[SailTime],[DateOP]) AS GoodsLandingDate, 
    Round([BankSetMargin]*[ChargesAllocationPercent]/100) AS FinalBankSetMargin, 
    Round([Insurance]*[ChargesAllocationPercent]/100) AS FinalInsurance, 
    Round([FreightCost]*[ChargesAllocationPercent]/100) AS FinalFreight, 
    DateAdd("d",[ClTime],[GoodsLandingDate]) AS CustomsAndMultiModalPaymentDate, 
    Round([Customs]*[ChargesAllocationPercent]/100) AS FinalCustoms, 
    Round([MultiModalC]*[ChargesAllocationPercent]/100) AS FinalMultiModal, 
    DateAdd("d",10,[CustomsAndMultiModalPaymentDate]) AS InlandTransportContainerDemurragePaymentDate,
    Round([InlandTrspt]*[ChargesAllocationPercent]/100) AS FinalInlandTrspt, 
    Round([ContainerDemurrage]*[ChargesAllocationPercent]/100) AS FinalContainerDemurrage
FROM 
    (
        tblFileLCDetails 
        INNER JOIN 
        (
            tblFileMaterialPurchaseDetails 
            INNER JOIN 
            tblMaterialMaster 
            ON 
            tblFileMaterialPurchaseDetails.MatId = tblMaterialMaster.MatId
        ) 
        ON 
        tblFileLCDetails.FileNumber = tblFileMaterialPurchaseDetails.FileNumber
    ) 
    INNER JOIN 
    (
        SELECT 
            tblFileMaterialPurchaseDetails.FileNumber, 
            Sum([TotalQuantityToBePurchased]*[UnitCOst]) AS TotalQuantityToBePurchasedCostPerFile
        FROM 
            tblFileMaterialPurchaseDetails 
            INNER JOIN 
            tblMaterialMaster 
            ON 
            tblFileMaterialPurchaseDetails.MatId = tblMaterialMaster.MatId
        GROUP BY 
            tblFileMaterialPurchaseDetails.FileNumber
    )
    AS qryMaterialTotalCostPerFileA 
    ON 
    tblFileLCDetails.FileNumber = qryMaterialTotalCostPerFileA.FileNumber;

Thanks

[Epona]

thanks recyan,

I will look at them a little alter - stuck in a meeting