WTD, MTD, YTD

[gmenon100 via AccessMonster.com]

Dear friends,

I have a table with the following columns:
Code#, OrdDt, Quantity, amount, Salesrep, Customer,

I want to form a query to show the following:

Weekly:

Code Mon Tue Wed Thu Fri
Sat Sun TOTAL
qty amt qty amt qty amt qty amt qty amt qty
amt qty amt QTY AMT

7778 2 3000 4 322 7 9992 11 2828 3 847 83
3377 6 773 116 18616
5466 ...........

Same for the monthly report and yearly.

Can someone please advise how I can go with this?

Thank you

Gimen

 

[Gary Walter]

I have a table with the following columns:
Code#, OrdDt, Quantity, amount, Salesrep, Customer,

I want to form a query to show the following:

Weekly:

Code Mon Tue Wed Thu Fri
Sat Sun TOTAL
qty amt qty amt qty amt qty amt qty amt qty
amt qty amt QTY AMT

7778 2 3000 4 322 7 9992 11 2828 3 847 83
3377 6 773 116 18616
5466 ...........

Same for the monthly report and yearly.

Can someone please advise how I can go with this?

Hi Gimen,

I might use the xtab method that Duane
has referenced earlier

http://www.tek-tips.com/faqs.cfm?fid=4524.

1) Create a table "tblXtabColumns" with one text field "FldName"

Enter 2 records for "FldName"

Qty
Amt

2) Let's assume your table name were "tblOrders"

I have a table with the following columns:
Code#, OrdDt, Quantity, amount, Salesrep, Customer,

A "week" tends to mean different things, i.e.,
business rules and regional defaults can confuse
when a week starts and when the first week of
the year starts, etc.

To keep things simple, I'm going to assume
you have some mechanism to create a WHERE
clause for the "week."

It sounds like your "week" goes from Monday
to the next Sunday, so for the week of

3/3/2008(Monday) - 3/9/2008(Sunday)

I would use following WHERE clause
(in case OrdDt has time values):

WHERE
T.OrdDt >= #3/3/2008#
AND
T.OrdDt < #3/10/2008#

So for that "week," try this crosstab that
uses the technique described in the link:

(replace "tblOrders" with actual table name)

TRANSFORM
Max(Switch(X.FldName="Qty", T.Quantity,
X.FldName="Amt", T.amount))
SELECT
T.[Code#],
Sum(IIF(X.FldName="Amt",T.amount,0)) AS TotAmt,
Sum(IIF(X.FldName="Qty", T.Quantity,0)) AS TotQty
FROM tblXtabColumns AS X, tblOrders AS T
WHERE
T.OrdDt >= #3/3/2008#
AND
T.OrdDt < #3/10/2008#
GROUP BY
T.[Code#]
PIVOT Format(T.OrdDt, "ddd") & X.FldName
IN
("MonAmt",
"MonQty",
"TueAmt",
"TueQty",
"WedAmt",
"WedQty",
"ThuAmt",
"ThuQty",
"FriAmt",
"FriQty",
"SatAmt",
"SatQty",
"SunAmt",
"SunQty");

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

good luck,

gary

 

[gmenon100 via AccessMonster.com]

Hi Gary,


Firstly thank you for your repsonse.
I was able to get the query in the format I want. However, the total value
and total count is not distributed over the months it just gives the totals.
I am giving here below my SQL:

Please advice where I am wrong.

Thank you
Gimen


TRANSFORM Max(Switch(X.FldName="count",T.count,X.FldName="charges",T.charges))
AS Expr1
SELECT T.Code, T.Rep, T.Number, T.Code, T.[Test Description], Sum(IIf(X.
FldName="charges",T.charges,0)) AS TotAmt, Sum(IIf(X.FldName="count",T.count,
0)) AS TotQty
FROM tblXtabColumns AS X, DoctorUtil AS T
WHERE (((T.mth)>=#1/1/2008# And (T.mth)<#7/31/2008#))
GROUP BY T.Code, T.Rep, T.Number, T.Code, T.[Test Description]
PIVOT Format(T.mth,"mmm") & X.FldName In ("JanAmt","JanQty","FebAmt","FebQty",
"MarAmt","MarQty","AprAmt","AprQty","MayAmt","MayQty","JunAmt","JunQty",
"JulAmt","JulQty");

I have a table with the following columns:
Code#, OrdDt, Quantity, amount, Salesrep, Customer,

[quoted text clipped - 15 lines]

Can someone please advise how I can go with this?

Hi Gimen,

I might use the xtab method that Duane
has referenced earlier

http://www.tek-tips.com/faqs.cfm?fid=4524.

1) Create a table "tblXtabColumns" with one text field "FldName"

Enter 2 records for "FldName"

Qty
Amt

2) Let's assume your table name were "tblOrders"

I have a table with the following columns:
Code#, OrdDt, Quantity, amount, Salesrep, Customer,

A "week" tends to mean different things, i.e.,
business rules and regional defaults can confuse
when a week starts and when the first week of
the year starts, etc.

To keep things simple, I'm going to assume
you have some mechanism to create a WHERE
clause for the "week."

It sounds like your "week" goes from Monday
to the next Sunday, so for the week of

3/3/2008(Monday) - 3/9/2008(Sunday)

I would use following WHERE clause
(in case OrdDt has time values):

WHERE
T.OrdDt >= #3/3/2008#
AND
T.OrdDt < #3/10/2008#

So for that "week," try this crosstab that
uses the technique described in the link:

(replace "tblOrders" with actual table name)

TRANSFORM
Max(Switch(X.FldName="Qty", T.Quantity,
X.FldName="Amt", T.amount))
SELECT
T.[Code#],
Sum(IIF(X.FldName="Amt",T.amount,0)) AS TotAmt,
Sum(IIF(X.FldName="Qty", T.Quantity,0)) AS TotQty
FROM tblXtabColumns AS X, tblOrders AS T
WHERE
T.OrdDt >= #3/3/2008#
AND
T.OrdDt < #3/10/2008#
GROUP BY
T.[Code#]
PIVOT Format(T.OrdDt, "ddd") & X.FldName
IN
("MonAmt",
"MonQty",
"TueAmt",
"TueQty",
"WedAmt",
"WedQty",
"ThuAmt",
"ThuQty",
"FriAmt",
"FriQty",
"SatAmt",
"SatQty",
"SunAmt",
"SunQty");

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

good luck,

gary

 

[gmenon100 via AccessMonster.com]

Hi Gary
Sorry, My mistake, the field values I used was "Count" and "Charges" and I
used JanQty, and JanAmt in my query. I changed this to JanCount and JanCharge
and the Query worked.

Thanks a million Gary.

Gimen.

Hi Gary,

Firstly thank you for your repsonse.
I was able to get the query in the format I want. However, the total value
and total count is not distributed over the months it just gives the totals.
I am giving here below my SQL:

Please advice where I am wrong.

Thank you
Gimen

TRANSFORM Max(Switch(X.FldName="count",T.count,X.FldName="charges",T.charges))
AS Expr1
SELECT T.Code, T.Rep, T.Number, T.Code, T.[Test Description], Sum(IIf(X.
FldName="charges",T.charges,0)) AS TotAmt, Sum(IIf(X.FldName="count",T.count,
0)) AS TotQty
FROM tblXtabColumns AS X, DoctorUtil AS T
WHERE (((T.mth)>=#1/1/2008# And (T.mth)<#7/31/2008#))
GROUP BY T.Code, T.Rep, T.Number, T.Code, T.[Test Description]
PIVOT Format(T.mth,"mmm") & X.FldName In ("JanAmt","JanQty","FebAmt","FebQty",
"MarAmt","MarQty","AprAmt","AprQty","MayAmt","MayQty","JunAmt","JunQty",
"JulAmt","JulQty");
[quoted text clipped - 85 lines]

gary