If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Crosstab Query -- Urgent!! Help needed.......
I have a crosstab query which sums up all manufacture's sales QTY. The query is as follows: ========================================= TRANSFORM Sum( sales_rpt_history.Total_Cases) AS SumOfTotal_Cases SELECT sales_rpt_history.MFR_Name, Sum( sales_rpt_history.Total_Cases) AS [Total Of Total_Cases] FROM sales_rpt_history, Max_date, Min_Date_12 WHERE ((( sales_rpt_history.Deliver_date)[Min_Date_12].[MinOfMinOfDeliver_date] And ( sales_rpt_history.Deliver_date)[Max_date].[maxdate]) GROUP BY BPAN007_sales_rpt_history.MFR_Name ORDER BY Format([deliver_date]," mmm") PIVOT Format([deliver_date]," mmm") In ("001","002","003","004","005","006","007","008"," 009","010","011","012"); ------------------------------------------------------------ I wish the column will sort in year and month order as well as in "001" format. So "001" will be the oldest record and so on.............. Can anyone please help me out? I've been working on this for 3 days now!! Any help would be greatly appreciated!!! |
#2
|
|||
|
|||
Crosstab Query -- Urgent!! Help needed.......
I don't see how you could be getting any results since your expression
Format([DateField]," mmm") will return somethin like " Jul". Your "In" statement has 001, 002,... Will you have multiple years represented so there is a possibility of more than 12 column headings generated? You might consider using a column heading expression like ColHead: "M" & Format(DateDiff("m", [Deliver_Date], Date()),"00") This will create columns with headings like "M01","M02",... -- Duane Hookom MS Access MVP -- "bpan007" wrote in message lkaboutsoftware.com... I have a crosstab query which sums up all manufacture's sales QTY. The query is as follows: ========================================= TRANSFORM Sum( sales_rpt_history.Total_Cases) AS SumOfTotal_Cases SELECT sales_rpt_history.MFR_Name, Sum( sales_rpt_history.Total_Cases) AS [Total Of Total_Cases] FROM sales_rpt_history, Max_date, Min_Date_12 WHERE ((( sales_rpt_history.Deliver_date)[Min_Date_12].[MinOfMinOfDeliver_date] And ( sales_rpt_history.Deliver_date)[Max_date].[maxdate]) GROUP BY BPAN007_sales_rpt_history.MFR_Name ORDER BY Format([deliver_date]," mmm") PIVOT Format([deliver_date]," mmm") In ("001","002","003","004","005","006","007","008"," 009","010","011","012"); ------------------------------------------------------------ I wish the column will sort in year and month order as well as in "001" format. So "001" will be the oldest record and so on.............. Can anyone please help me out? I've been working on this for 3 days now!! Any help would be greatly appreciated!!! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
crosstab query | chris morrison | Running & Setting Up Queries | 2 | July 12th, 2004 08:57 PM |
Crosstab query oddity | Duane Hookom | Running & Setting Up Queries | 2 | July 4th, 2004 07:41 PM |
Crosstab query with same VALUE in multiple columns | ChrisJ | Running & Setting Up Queries | 1 | June 20th, 2004 10:41 PM |
Showing all subrows in crosstab query | Ragnar Midtskogen | Running & Setting Up Queries | 3 | May 26th, 2004 08:16 PM |
Dates In a CrossTab query | Box 666 | New Users | 1 | May 21st, 2004 04:01 AM |