Change results of query

[Gill1978]

Hi,

I have a query (below) that displays all the dates of a given month and a count of messages inserted into a table on each day of that month ie.
Date Count
--------------------
1/07/2007 0
2/07/2007 0
3/07/2007 5
4/07/2007 10
5/07/2007 0

However is there a way I can flip it round so the headings are the dates and then the count is the detail eg

1/07/2007 2/07/2007 3/07/2007 4/07/2007 5/07/2007
-------------------------------------------------------
0 0 5 10 0

I need to display the query in the above form in an Access report?

Any help would be great ...
Here's the query

Declare @Start as datetime
Declare @End as datetime 

Set @Start = '01/07/2007'
Set @End = '31/07/2007'

	declare @Start_loop datetime
	create table #DateTable (Dates datetime)
	set @Start_loop = @Start
	while @Start_loop <= @End
	begin
	    insert into #DateTable values(@Start_loop)
	    set @Start_loop = @Start_loop + 1
	end
	
	select dates, count(process_time) from #DateTable d
	left join pre..all_mess_inc
	on dates = Process_date
-- 	where Process_date between @Start and @End
	Group by dates

Thanks

Julie

 

[ca8msm]

From a quick search on the forums...thread183-1159740


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[Gill1978]

Not sure how that helps ... I'm trying to flip round the result set of the above query ...????

 

[HarleyQuinn]

Might it not be simpler to do it in the access report? Use your SQL query to return the data and the report to display it the way you want?

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Gill1978]

Don't know how to do that in Access ... do you know how to do this?

 

[Gill1978]

The reason why i need to flip this query round also is that I need to add 3 other lines to it ...
1. messages received before midday
2. messages received after midday etc

Otherwise I'll have to add these as further columns to my query above ... and I dont know how to do this ....

any ideas??

 

[HarleyQuinn]

I'll be honest, I've not touched an Access report in a long time, but I'm of the opinion that it's do-able. I could probably mudle something together but it'd take some time!

Perhaps posting a similar thread* about how to translate your results into the required format within reports in this forum703, would be helpful as it might offer you two solutions (providing that a solution is posted in this forum) allowing you to choose the most useable solution in your situation.

* Caveat, I nkow cross posting isn't good, but in this situation it's pretty much two seperate problems

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

And yeah, sorry that sounds a bit like cop-out answer.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Gill1978]

Well I've managed to coble together a query (using quite a few temp tables) to get the right data ... just need to figure out how to flip the display of the data ?!!?

Added it to the Access forum as you suggested ... as yet nothing ??!

Thanks for the help though HarleyQuinn