MTD

[gmenon100 via AccessMonster.com]

Dear Friends,

I am having a problem showing the MTD figure on every line item for products.

My table is as below tblTestcount

CodeNo (text), Manager(text), SalesRep(text), SrvDate (date), Customer (text),
Count (number)

Example:
CodeNo Manager Salrep Srv Dt Customer Count
1234 Don Smith 02/01/08 Goodwill 1
0527 Don Smith 02/01/08 Goodwill 3
0832 Cathy Jason 02/01/08 Sealy 1
1234 Don Smith 02/02/08 Goodwill 1
0527 Don Smith 02/02/08 Goodwill 1
1229 Arthur Keith 02/02/08 International 1
1234 Cathy Karl 02/02/08 Sealy 1

I want to create a report as under:

Manager
Salrep
Srv Dt
Customer
CodeNo Count(02/02/08)
CountMTD (02/01 + 02/02)
Don
Smith
02/02/08
Goodwill

1234
1 2
0527
1 4
subtotal by account 2
6

In short I want to show the count for the day and the corresponding MTD for
that particular codeNo.
MTD.

Can someone please help. Appreciate it

Gimen

 

[Gary Walter]

Dear Friends,

I am having a problem showing the MTD figure on every line item for
products.

My table is as below tblTestcount

CodeNo (text), Manager(text), SalesRep(text), SrvDate (date), Customer
(text),
Count (number)

Example:
CodeNo Manager Salrep Srv Dt Customer Count
1234 Don Smith 02/01/08 Goodwill 1
0527 Don Smith 02/01/08 Goodwill 3
0832 Cathy Jason 02/01/08 Sealy 1
1234 Don Smith 02/02/08 Goodwill 1
0527 Don Smith 02/02/08 Goodwill 1
1229 Arthur Keith 02/02/08 International 1
1234 Cathy Karl 02/02/08 Sealy 1

I want to create a report as under:

Manager
Salrep
Srv Dt
Customer
CodeNo Count(02/02/08)
CountMTD (02/01 + 02/02)
Don
Smith
02/02/08
Goodwill

1234
1 2
0527
1 4
subtotal by account 2
6

In short I want to show the count for the day and the corresponding MTD
for
that particular codeNo.
MTD.

Hi Gimen,

I assume tblTestCount is a "derived table" for your report?

If so, then why not add one more field "MTDCnt"
type Long which can be Null, or zero (better), initially.

Then join table to itself and update "Main" (M) instance
based on "Past" (P) instance.

UPDATE
tblTestCount AS M
INNER JOIN
tblTestCount AS P
ON
(M.Customer = P.Customer)
AND
(M.Salrep = P.Salrep)
AND
(M.Manager = P.Manager)
AND
(M.CodeNo = P.CodeNo)
SET M.MTDCnt = NZ([M].[MTDCnt],0)+[P].[Count]
WHERE
(((Month([M].[Srv Dt]))=Month([P].[Srv Dt]))
AND
((Year([M].[Srv Dt]))=Year([P].[Srv Dt]))
AND
((P.[Srv Dt])<=[M].[Srv Dt]));

I think by having the MTD already computed
in the table, your report will not have "grouping"
problems if tried to use aggregating subquery.

That may be one way...

Please repond back if I misunderstood
or was not clear about something.

good luck,

gary

 

[Gary Walter]

Dear Friends,

I am having a problem showing the MTD figure on every line item for
products.

My table is as below tblTestcount

CodeNo (text), Manager(text), SalesRep(text), SrvDate (date), Customer
(text),
Count (number)

Example:
CodeNo Manager Salrep Srv Dt Customer Count
1234 Don Smith 02/01/08 Goodwill 1
0527 Don Smith 02/01/08 Goodwill 3
0832 Cathy Jason 02/01/08 Sealy 1
1234 Don Smith 02/02/08 Goodwill 1
0527 Don Smith 02/02/08 Goodwill 1
1229 Arthur Keith 02/02/08 International 1
1234 Cathy Karl 02/02/08 Sealy 1

I want to create a report as under:

Manager
Salrep
Srv Dt
Customer
CodeNo
Count(02/02/08)
CountMTD (02/01 + 02/02)
Don
Smith
02/02/08
Goodwill

1234
1 2
0527
1 4
subtotal by account 2
6

In short I want to show the count for the day and the corresponding MTD
for
that particular codeNo.
MTD.

Hi Gimen,

I assume tblTestCount is a "derived table" for your report?

If so, then why not add one more field "MTDCnt"
type Long which can be Null, or zero (better), initially.

Then join table to itself and update "Main" (M) instance
based on "Past" (P) instance.

UPDATE
tblTestCount AS M
INNER JOIN
tblTestCount AS P
ON
(M.Customer = P.Customer)
AND
(M.Salrep = P.Salrep)
AND
(M.Manager = P.Manager)
AND
(M.CodeNo = P.CodeNo)
SET M.MTDCnt = NZ([M].[MTDCnt],0)+[P].[Count]
WHERE
(((Month([M].[Srv Dt]))=Month([P].[Srv Dt]))
AND
((Year([M].[Srv Dt]))=Year([P].[Srv Dt]))
AND
((P.[Srv Dt])<=[M].[Srv Dt]));

I think by having the MTD already computed
in the table, your report will not have "grouping"
problems if tried to use aggregating subquery.

In fact, you could possibly simplify report
even further by adding 2 additional number fields
(again Null, or zero (better), initially)

CntSubTot
MTDCntSubTot

then, *after run above query*, run following to
get subtotals over date and account

UPDATE
tblTestCount AS M
INNER JOIN
tblTestCount AS P
ON
(M.[Srv Dt] = P.[Srv Dt])
AND
(M.Manager = P.Manager)
AND
(M.Salrep = P.Salrep)
AND
(M.Customer = P.Customer)
SET
M.CntSubTot = NZ([M].[CntSubTot],0)+[P].[Count],
M.MTDCntSubTot = NZ([M].[MTDCntSubTot],0)+[P].[MTDCnt];

then use them only in your account report footer.

Note: you used different field names
so you might have to substitute

SalesRep for Salrep
SrvDate for [Srv Dt]

in queries...

good luck,

gary

 

[gmenon100 via AccessMonster.com]

Hi Gary,

Thank you very much for your advice. I will surely try your suggestion no
sooner i reach office.

Appreciate your help.

GM

[quoted text clipped - 76 lines]

in the table, your report will not have "grouping"
problems if tried to use aggregating subquery.

In fact, you could possibly simplify report
even further by adding 2 additional number fields
(again Null, or zero (better), initially)

CntSubTot
MTDCntSubTot

then, *after run above query*, run following to
get subtotals over date and account

UPDATE
tblTestCount AS M
INNER JOIN
tblTestCount AS P
ON
(M.[Srv Dt] = P.[Srv Dt])
AND
(M.Manager = P.Manager)
AND
(M.Salrep = P.Salrep)
AND
(M.Customer = P.Customer)
SET
M.CntSubTot = NZ([M].[CntSubTot],0)+[P].[Count],
M.MTDCntSubTot = NZ([M].[MTDCntSubTot],0)+[P].[MTDCnt];

then use them only in your account report footer.

Note: you used different field names
so you might have to substitute

SalesRep for Salrep
SrvDate for [Srv Dt]

in queries...

good luck,

gary