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

On Jan 15, 2:29*pm, pat67 <pbus...@comcast.net> wrote:
> 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?

On Mon, 17 Jan 2011 07:06:09 -0800 (PST), pat67 <(E-Mail Removed)> wrote:


>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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com