Quarter Chart Showing As 22 '06 1

[bowieknife]

Does anyone know how to get a chart to show "1st", "2nd", etc. for a quarterly chart? I utilized the chart wizard and am having trouble with this. Thanks in advance.

 

[dhookom]

Modify the Row Source of the chart control to display the exact values you want to display in your chart. If you can't figure this out, come back with your current Row Source.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bowieknife]

Thanks d.

Here is my current row source:

TRANSFORM Sum([CountOfShipment Id]) AS [SumOfCountOfShipment Id] SELECT (Format([Actual Shipment Date],"Q 'YY")) FROM [QryShippingGFP] GROUP BY (Year([Actual Shipment Date])*4 + DatePart("q", [Actual Shipment Date])-1),(Format([Actual Shipment Date],"Q 'YY")) PIVOT [Veh Type Desc];

I want this to show "1st Quarter 2006", etc.

Is there a way to do this for weekly charts also (only displaying start or end of week date instead of 19 '06, 20 '06)?

Current row source for the weekly chart is:

TRANSFORM Sum([CountOfShipment Id]) AS [SumOfCountOfShipment Id] SELECT (Format([Actual Shipment Date],"WW 'YY")) FROM [QryShippingGFP] GROUP BY (Year([Actual Shipment Date])*CLng(54) + DatePart("ww",[Actual Shipment Date],0)-1),(Format([Actual Shipment Date],"WW 'YY")) PIVOT [Veh Type Desc];

I appreciate your help.

 

[dhookom]

Try:

TRANSFORM Sum([CountOfShipment Id]) AS [TheValue] 
SELECT Choose(DatePart("q",[Actual Shipment Date]), "1st","2nd","3rd","4th") & " Quarter " & Year([Actual Shipment Date])
FROM [QryShippingGFP]   
GROUP BY Choose(DatePart("q",[Actual Shipment Date]),"1st","2nd","3rd","4th") & " Quarter " & Year([Actual Shipment Date])
PIVOT [Veh Type Desc];

To group by week, you can subtract the weekday from the date and add 1.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bowieknife]

Thanks dhookum. Have a star.

For the "week ending" chart report mentioned above, I changed the statement to accomodate the format, but now I get the chart displaying all dates along the x-axis, including the days in between the "week ending" dates (which are the only points displaying data. Can you help?

Here is my row source:

TRANSFORM Sum(QryShippingGFP.[CountOfShipment Id]) AS [SumOfCountOfShipment Id] SELECT Format([Actual Shipment Date]-(Weekday([Actual Shipment Date])-8),"Short Date") AS Expr1 FROM QryShippingGFP GROUP BY (Year([Actual Shipment Date])*CLng(54)+DatePart("ww",[Actual Shipment Date],0)-1), Format([Actual Shipment Date]-(Weekday([Actual Shipment Date])-8),"Short Date") PIVOT QryShippingGFP.[Veh Type Desc];

 

[dhookom]

Does this work:

TRANSFORM Sum(QryShippingGFP.[CountOfShipment Id]) AS [SumOfCountOfShipment Id] 
SELECT Format([Actual Shipment Date]-(Weekday([Actual Shipment Date])-8),"Short Date") AS WeekOf 
FROM QryShippingGFP 
GROUP BY Format([Actual Shipment Date]-(Weekday([Actual Shipment Date])-8),"Short Date") 
PIVOT QryShippingGFP.[Veh Type Desc];

It looks like you are calculating a prior week.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bowieknife]

No - I'm still getting the blank week days (dates) in between the week ending dates.

 

[dhookom]

Rip out the formatting in the query and try again. If that doesn't work, come back with your SQL view.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bowieknife]

Not sure if this is what you're asking for:

TRANSFORM Sum(QryShippingGFP.[CountOfShipment Id]) AS [SumOfCountOfShipment Id]
SELECT Format([Actual Shipment Date]-(Weekday([Actual Shipment Date])-8),"Short Date") AS Expr1
FROM QryShippingGFP
GROUP BY (Year([Actual Shipment Date])*CLng(54)+DatePart("ww",[Actual Shipment Date],0)-1), Format([Actual Shipment Date]-(Weekday([Actual Shipment Date])-8),"Short Date")
PIVOT QryShippingGFP.[Veh Type Desc];

 

[dhookom]

That's where I want you to rip out the Format().
Try:

TRANSFORM Sum(QryShippingGFP.[CountOfShipment Id]) AS [SumOfCountOfShipment Id]
SELECT [Actual Shipment Date]-(Weekday([Actual Shipment Date])-8) AS WeekOf
FROM QryShippingGFP
GROUP BY [Actual Shipment Date]-(Weekday([Actual Shipment Date])-8)
PIVOT QryShippingGFP.[Veh Type Desc];

If this doesn't work, does you Actual Shipment Date contain a time value or just the date?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bowieknife]

It does contain dates and times, for example:

Actual Shipment Date
5/23/2006 2:17:04 PM
5/27/2006 12:51:25 PM
5/11/2006 1:03:21 PM
5/12/2006 10:00:28 AM
5/14/2006 5:11:59 PM

Sorry for not mentioning that before. Thanks for your patience.

 

[dhookom]

Get rid of the time portion with

TRANSFORM Sum(QryShippingGFP.[CountOfShipment Id]) AS [SumOfCountOfShipment Id]
SELECT DateValue([Actual Shipment Date])-(Weekday([Actual Shipment Date])-8) AS WeekOf
FROM QryShippingGFP
GROUP BY DateValue([Actual Shipment Date])-(Weekday([Actual Shipment Date])-8)
PIVOT QryShippingGFP.[Veh Type Desc];

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bowieknife]

Thanks again dhookum. I'm still getting the date "spread" in between the key "week ending" dates using the DateValue function.

What the heck is happening with this chart?

I appreciate your help.

 

[dhookom]

Does the date "spread" show in the datasheet view of the chart's row source?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bowieknife]

No it doesn't. The datasheet view is great - grouped by weekening dates.

 

[bowieknife]

This is what it shows:

WeekOf Intermodal/Container Rail Truck
5/7/2006 24 7 160
5/14/2006 18 12 162
5/21/2006 37 12 202
5/28/2006 25 13 179
6/4/2006 32 3 170
6/11/2006 6 38

 

[dhookom]

I expect you need to change something in your chart properties. Your SQL is fine.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bowieknife]

I'm fumbling in the dark here. Which properties should I be looking at?

 

[dhookom]

I don't know. I would have to open the chart in design and play around with the various properties making changes until I found the appropriate one. I don't have your chart in front of me so you may have to fiddle with the chart and report back.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]