crosstab problem

[pat67]

Hi, I am trying to create a crosstab query, since that is the only way
i can see it being done, that will show the value of orders due in a
month that need to be moved to another month. so the output would look
something like this.

Reschedule Month
Month Due Jan-2011 Feb-2011 Mar-2011 Apr-2011
May-2011 Jun-2011
Jan-2011 0 0
0 0 0 0
Feb-2011 1000 0 0
0 0 0
Mar-2011 500 100 0
0 0 0
Apr-2011 0 200 150
0 0 0
May-2011 100 0 200
1500 0 0
June-2011 0 120 150
200 600 0

what I have is a total table of all orders that I use queries to
delimit.For instance, if i am looking for what value i need to move to
a certain month but not out of a month.

SELECT qryExc_Message_Anticipate_1.[Month Due], Sum(IIf([Year
Due]<=2011,IIf([Resch Year]<=2011,[Value],0),0)) AS [Anticipate Within
2011] INTO tbl_2011_Anticipate_2
FROM qryExc_Message_Anticipate_1
WHERE (((qryExc_Message_Anticipate_1.[Exc Message])=10))
GROUP BY qryExc_Message_Anticipate_1.[Month Due]
HAVING (((Sum(IIf([Year Due]<=2011,IIf([Resch Year]<=2011,[Value],0),
0)))>0));


If anyone has any ideas I would appreciate it. If you need more info,
let me know.

Thanks

 

[pat67]

Hi, I am trying to create a crosstab query, since that is the only way
i can see it being done, that will show the value of orders due in a
month that need to be moved to another month. so the output would look
something like this.

                  Reschedule Month
Month Due        Jan-2011      Feb-2011    Mar-2011  Apr-2011
May-2011   Jun-2011
Jan-2011                 0              0
0             0                   0           0
Feb-2011                1000         0                0
0                   0           0
Mar-2011                500         100              0
0                   0           0
Apr-2011                 0             200              150
0                   0             0
May-2011                100         0                200
1500                 0             0
June-2011               0            120               150
200              600            0

what I have is a total table of all orders that I use queries to
delimit.For instance, if i am looking for what value i need to move to
a certain month but not out of a month.

SELECT qryExc_Message_Anticipate_1.[Month Due], Sum(IIf([Year
Due]<=2011,IIf([Resch Year]<=2011,[Value],0),0)) AS [Anticipate Within
2011] INTO tbl_2011_Anticipate_2
FROM qryExc_Message_Anticipate_1
WHERE (((qryExc_Message_Anticipate_1.[Exc Message])=10))
GROUP BY qryExc_Message_Anticipate_1.[Month Due]
HAVING (((Sum(IIf([Year Due]<=2011,IIf([Resch Year]<=2011,[Value],0),
0)))>0));

If anyone has any ideas I would appreciate it. If you need more info,
let me know.

Thanks

I actually was able to figure this out but now the issue is sorting.
Since the months have abbreviated names, Apr comes first. Is there a
way to sort Jan first?

 

[John W. Vinson]

I actually was able to figure this out but now the issue is sorting.
Since the months have abbreviated names, Apr comes first. Is there a
way to sort Jan first?

Include the month twice: once using Format([datefield], "mmm") to get the
alphabetic month name, once using Month([datefield]) to get an integer month
number. Display the first of these, sort by the other.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com