Does Access have Limited Crosstab compared with Excel?

[helm]

Folks, could anyone advise ...

Is there a significant difference in crosstab capabilities in Access and Excel?

Using Office XP 2002 ... to produce a crosstab report I developed it in Excel from a query datasource in Access. One good thing about this was the ability to select both a date header and then a specific time header for the crosstab value. Try as I might, I cannot see a way to replicate this in Access either directly using a crosstab or using the crosstab as data for a report.

I wish I could see a way to show you the Excel output ... moving soon to Office 2007 which seems very slow in comparison with 2003 and that's part of the reason that I don't want ot open Excel for just one report.

The data I am seeking to model is:-

Select a set of records for that match one side of a 1:M set of tables. None of the records in the M table is unique. The M side holds data that includes a text field (member) which represents the row, a date field, a separate time field and an integer number value that will not exceed 99.

On the solution I would like to show something like this

Title : (from the 1 table in the 1:M query)

Date1 || Date 2 etc
Time1 | Time2 | Time3 | Time4 || Time1 | Time2 |Time 3 | etc

Fred | 1 | 4 | || 3 | 6 | 4 |
Bill | 3 | | 1 || 1 | 4 | |

I cannot use datepart since the date and time fields are sepaate fields. Note that there may be some null values for some results.

I hope that I have provided enought detail. I can supply a set of source data if this would help.

TIA for any help on this.

Helm

[Stewart Ross]

Hi Helm. Unfortunately, Access queries have fairly limited pivoting - only one field can be designated as the column header in a crosstab query and no sub-heads are possible. Excel's pivot facilities are much more comprehensive, allowing for sub-divisions of the pivoted data grouped by more than one field.

Using VBA it is possible to produce programmed output that would more closely match your requirements, but this would take time to develop. Perhaps other contributors could suggest another way?

-Stewart

[helm]

Hello Stewart and thanks for the response. Much as I thought. Access 2007 doesn't seem to have been updated in this area.

I can get some way with an Access report but am still stuck with 'fixed' column headers and the 'Jan to Dec' column header solution is not appropriate for 90% of the data (most have a limited lifespan of three months or so). I may be able to get close to 'all the data on the same line as the row value' by using fconcatchild to sub select all the records (think Dev Ashish produced that?).

Actually, I don't need the crosstab sum, avg etc, just be able to show the data 'like' the Excel output. Perhaps I've been leading myself astray!

I'll struggle on and hope that someone can suggest an 'Access' solution. BTW, do you happen to know how to insert code or a screen snapshot onto this forum - might help better show what I'm trying to do :-)

Thanks again

Helm

[helm]

Cracked it!

Use your access crosstab query as the record source for an access report. The report basically consists of unbound labels and controls which can be dynamically allocated to each record using the controls collection. Produce a recordset from the report recordsource.

That works. However, since my dates are in the format "dd/mm" the column order is based on the day rather than the month. Changed the formatting in the crostab to mm/dd solved that but gives a 'US' "mm/dd" column label. Used the MID function to split each record into a number of strings and recombined to get back to "dd/mm" format.

Also found that I could get other useful data by inserting other grouped fields to the crosstab and then adding them to (for example) the row heading for later use (again for example) as a report title in the report header (Instr & Mid).

So ... it can be done. Bit tedious but much faster than dumping into Excel ... and prettier too!

Helm

[NeoPa]

Thanks for updating the thread with your progress.
As far as attachments go, simply add a post; edit it within the time-limit; select attachment options and upload a conformant file (not too large etc).

[helm]

NeoPa,

Sorry but I can't see any way to send an attachment -all I can see is 'Insert Image' but that just brings up this ... [IMG], Insert Hyperlink ... E Mail Link. I must be missing something ..

Regards, Helm

[NeoPa]

No problems.
Below the standard Submit & Preview buttons there is a box called "Additional Options". The Manage Attachments button is in there.

[helm]

NeoPa

Ah, 'Additional Options' not showing on my brower - neither Firefox nor Explorer.

There's Report and Reply in the box and Post REply below the box. Nothing else showing until "Quick Browse" . Something I've done?

Regards and thanks for your help

Helm

[helm]

Folks,

My report works fine. Last problem is to set a qdef to create the query. What works fine in the query developer bombs out on compliation in VBA - as below. It gives a syntax error on the SELECT statement. I think there may be two problems - getting the single and double quotes right and perhaps also because I have used a lot of empty spaces and a . (period) to create HELM values that can be broken down in the report to produce labels. I'd really appreciate some help to finish this

with best regards, Helm

p.s. just noted in the Preview Post that having used ctl c (copy) and ctl p (paste) to create this message, the 30 odd 'white spaces' used between SELECT [membername] & " and the . have been truncated leaving just the . (period) ! Same applies to the GROUP BY clause!! Apologies if this hopelessly confuses the plot.

strwall = "PARAMETERS [Forms].[wallchart2].[combo3] Byte;"
strwall = strwall & " TRANSFORM Sum(tblResults.pos) AS SumOfpos "
strwall = strwall & " SELECT [membername] &  "                                 . " & [seriesname] AS HELM "
strwall = strwall & " FROM (tblRace INNER JOIN tblResults ON tblRace.raceauto=tblResults.raceID)"
strwall = strwall & " INNER JOIN tblSeries ON tblRace.seriesID=tblSeries.seriesID "
strwall = strwall & " WHERE (((tblRace.seriesID) = [seriesnum]))"
strwall = strwall & " GROUP BY [membername] & '                               . ' & [seriesname] AS HELM, "
strwall = strwall & " tblRace.seriesID , tblSeries.seriesname "
strwall = strwall & " ORDER BY Format([date],'mm/dd') & ' ' & Format(tblresults.time,'Short Time')"
strwall = strwall & " PIVOT Format([date],'mm/dd') & ' ' & Format(tblresults.time,'Short Time');"

[NeoPa]

NeoPa

Ah, 'Additional Options' not showing on my brower - neither Firefox nor Explorer.

There's Report and Reply in the box and Post REply below the box. Nothing else showing until "Quick Browse" . Something I've done?

Regards and thanks for your help

Helm

No - You're looking for it on the wrong page though ;)
After submitting your post you should hit the Edit/Delete button. In THERE you should find the Additional Options section (if you scroll down far enough).

[NeoPa]

...p.s. just noted in the Preview Post that having used ctl c (copy) and ctl p (paste) to create this message, the 30 odd 'white spaces' used between SELECT [membername] & " and the . have been truncated leaving just the . (period) ! Same applies to the GROUP BY clause!! Apologies if this hopelessly confuses the plot.

As you will see, I've added the [ CODE ] tags that are there for this very reason ;) When preparing a post, they are available by clicking on the button that looks like a # (Hover the mouse over the buttons to see what they do for you).

As far as your code is concerned, I would consider using Space(20) rather than a string of 20 spaces anyway. It's clearer then what you're attempting.

strwall = strwall & " SELECT [membername] & Space(20) & '. ' & [seriesname] AS HELM "

NB. Quotes to be passed through to SQL (as opposed to those used in VBA strings) should really be (') rather than ("). Your code would fail anyway as it would find the embedded double-quote (") annd treat it as the end of the VBA string rather than what was intended (Check out Quotes (') and Double-Quotes (") - Where and When to use them). I've changed it in the example.

[helm]

NeoPa,

Perfect ! Exactly what I wanted. Searched the net and up till 2 a.m trying to solve this! Thanks so much. And thanks too for the posting advice.

Is there a points scoring system on the site?

Regards, Helm.

[NeoPa]

Thanks for the thought, but no. We prefer to keep it simple. Point scoring systems seem to be so open to abuse & exploitation that simple post-count does for us.

[helm]

All,

Just to complete this post, this works well. Not entirely sure why the double quotes works in the WHERE statement but it does (because it's "outside" SQL) ? Also decided against PARAMETERS - better to take the seriesID value from the calling form and run the resulting query as the report record source. About to move from Access 2003 to 2007 so fingers crossed!

Regards, Helm

Dim qdf As DAO.QueryDef, strwall As String
strwall = strwall & " TRANSFORM Sum(tblResults.pos) AS SumOfpos "
strwall = strwall & " SELECT [membername] & Space(30) & '. ' & [seriesname] AS HELM "
strwall = strwall & " FROM (tblRace INNER JOIN tblResults ON tblRace.raceauto=tblResults.raceID)"
strwall = strwall & " INNER JOIN tblSeries ON tblRace.seriesID=tblSeries.seriesID "
strwall = strwall & " WHERE (((tblRace.seriesID)= " & Me!Combo3 & "))"
strwall = strwall & " GROUP BY [membername],[seriesname],[tblRace].[seriesID], [tblSeries].[seriesname] "
strwall = strwall & " ORDER BY format([date],'mm/dd') & ' ' &  format([tblresults].[time],'hh:mm') "
strwall = strwall & " PIVOT format([date],'mm/dd') & ' ' & format([tblresults].[time],'hh:mm');"

Set qdf = db.CreateQueryDef("qryWallQuery", strwall)
qdf.Close

[NeoPa]

Indeed, the double-quotes are the VBA string delimiters and don't find their way into the SQL string :)
As to moving to A2007 - my advice would be to avoid that like the plague. You may have reasons why you must. If so, good luck.