How to expand query to include multiple weeks

[jas0n]

tblTransfer is used to hold data of where our assets are in the company and
which internal contract to charge for each weeks period of hire. As they are
moved to a different contract we enter them into tblTransfer so we have a
history of where they have been and use this query to find out where they
are in the week of the cut off date and charge the internal cost centres.

It works fine doing it weekly but we want to move this to be done once per
month, either on a 4 or 5 week charging month depending on the accounting
calendar.

So, this query needs to run against the tblTransfer for each week for 4 or 5
weeks prior to the cut off date - then colate the information.

Im at a loss where to start with this, can anyone help?


PARAMETERS [What is the Cutoff Date] DateTime;
SELECT [ItemNo], [ContractNo], [DateOfTransfer], [ItemUser]
FROM tblTransfer AS E
WHERE E.DateofTransfer = (SELECT MAX(T.DateOfTransfer)
FROM tblTransfer as T
WHERE T.ItemNo = E.ItemNo
AND T.DateOfTransfer <= NZ([What is the Cutoff Date],#1/1/2299#));

 

[Guest]

Try this where you create a table TblAccountingCalendar with fields
[MonthStart] and [MonthEnd] ---

PARAMETERS [What is the Cutoff Date] DateTime;
SELECT E.ItemNo, E.ContractNo, E.DateOfTransfer, E.ItemUser, E.DateOfTransfer
FROM tblTransfer AS E, TblAccountingCalendar
WHERE (((E.DateOfTransfer)=(SELECT MAX(T.DateOfTransfer)
FROM tblTransfer as T
WHERE T.ItemNo = E.ItemNo
AND T.DateOfTransfer <= NZ([What is the Cutoff Date],#1/1/2299#))) AND
((E.DateOfTransfer) Between [MonthStart] And [MonthEnd]));

 

[jas0n]

Try this where you create a table TblAccountingCalendar with fields
[MonthStart] and [MonthEnd] ---

PARAMETERS [What is the Cutoff Date] DateTime;
SELECT E.ItemNo, E.ContractNo, E.DateOfTransfer, E.ItemUser,
E.DateOfTransfer
FROM tblTransfer AS E, TblAccountingCalendar
WHERE (((E.DateOfTransfer)=(SELECT MAX(T.DateOfTransfer)
FROM tblTransfer as T
WHERE T.ItemNo = E.ItemNo
AND T.DateOfTransfer <= NZ([What is the Cutoff Date],#1/1/2299#))) AND
((E.DateOfTransfer) Between [MonthStart] And [MonthEnd]));

No, doesnt give what im expecting ....

If a 4 week month and if there was no additions/movement during a month id
expect each contract to total 4 * existing rate of all items.

The old system charges weekly, rather than daily, if an item is held on a
contract even for part of that week it is charged at the weekly value to
that contract, so if we then do this once per month for a 4 or 5 week month
the totals should be 4 or 5 times the overall weekly rate value. But as
items move between contracts and new items get added and taken away during
part months I cant just times the figures by 4 or 5.

Im not sure im giving enough details or expressing it properly.

 

[Guest]

I could only guess at your table structure and data. Post the structure and
sample data. Show an example of what you expect from the sample data.

Try this where you create a table TblAccountingCalendar with fields
[MonthStart] and [MonthEnd] ---

PARAMETERS [What is the Cutoff Date] DateTime;
SELECT E.ItemNo, E.ContractNo, E.DateOfTransfer, E.ItemUser,
E.DateOfTransfer
FROM tblTransfer AS E, TblAccountingCalendar
WHERE (((E.DateOfTransfer)=(SELECT MAX(T.DateOfTransfer)
FROM tblTransfer as T
WHERE T.ItemNo = E.ItemNo
AND T.DateOfTransfer <= NZ([What is the Cutoff Date],#1/1/2299#))) AND
((E.DateOfTransfer) Between [MonthStart] And [MonthEnd]));

No, doesnt give what im expecting ....

If a 4 week month and if there was no additions/movement during a month id
expect each contract to total 4 * existing rate of all items.

The old system charges weekly, rather than daily, if an item is held on a
contract even for part of that week it is charged at the weekly value to
that contract, so if we then do this once per month for a 4 or 5 week month
the totals should be 4 or 5 times the overall weekly rate value. But as
items move between contracts and new items get added and taken away during
part months I cant just times the figures by 4 or 5.

Im not sure im giving enough details or expressing it properly.

 

[jas0n]

I could only guess at your table structure and data. Post the structure
and
sample data. Show an example of what you expect from the sample data.

:

Table layouts

tblContracts
ContractNo, ContractName
tblDescriptionType
DescriptionType
tblItems
ItemNo, DescriptionType, DescriptionDetail, SerialNo, Rate
tblItemUser
ItemUser
tblTransfer
Autonumber, ItemNo, ContractNo, DateOfTransfer, ItemUser

tblTransfer is used to store all the movements of items - the query then
takes the date entered as cut off date and returns only the last transfer of
each item - this tells us where it is and then I use a report to calculate
the sum of rate for all the items returned for that week summarised by
contract.

We are sticking with a weekly charge but only want to run the summary report
once per month - an item could move from one contract to another each week
so we still need to calculate the weekly report and combine it into a 4 or 5
week month end summary.

Im not sure if the way we use tblTransfer to record the moves is the best
way to calculate this but its working as it is - just weekly though.

 

[Guest]

You did not post the data example I asked for. Are all transfers on Friday?
Monday? Whole weeks? Some part week say Monday then Thursday?