By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,287 Members | 1,509 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,287 IT Pros & Developers. It's quick & easy.

Does Access have Limited Crosstab compared with Excel?

P: 26
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
Feb 20 '08 #1
Share this Question
Share on Google+
23 Replies


Expert Mod 2.5K+
P: 2,545
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
Feb 21 '08 #2

P: 26
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
Feb 21 '08 #3

P: 26
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
Feb 22 '08 #4

NeoPa
Expert Mod 15k+
P: 31,768
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).
Feb 22 '08 #5

P: 26
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
Feb 23 '08 #6

NeoPa
Expert Mod 15k+
P: 31,768
No problems.
Below the standard Submit & Preview buttons there is a box called "Additional Options". The Manage Attachments button is in there.
Feb 23 '08 #7

P: 26
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
Feb 23 '08 #8

P: 26
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.
Expand|Select|Wrap|Line Numbers
  1. strwall = "PARAMETERS [Forms].[wallchart2].[combo3] Byte;"
  2. strwall = strwall & " TRANSFORM Sum(tblResults.pos) AS SumOfpos "
  3. strwall = strwall & " SELECT [membername] &  "                                 . " & [seriesname] AS HELM "
  4. strwall = strwall & " FROM (tblRace INNER JOIN tblResults ON tblRace.raceauto=tblResults.raceID)"
  5. strwall = strwall & " INNER JOIN tblSeries ON tblRace.seriesID=tblSeries.seriesID "
  6. strwall = strwall & " WHERE (((tblRace.seriesID) = [seriesnum]))"
  7. strwall = strwall & " GROUP BY [membername] & '                               . ' & [seriesname] AS HELM, "
  8. strwall = strwall & " tblRace.seriesID , tblSeries.seriesname "
  9. strwall = strwall & " ORDER BY Format([date],'mm/dd') & ' ' & Format(tblresults.time,'Short Time')"
  10. strwall = strwall & " PIVOT Format([date],'mm/dd') & ' ' & Format(tblresults.time,'Short Time');"
Feb 23 '08 #9

NeoPa
Expert Mod 15k+
P: 31,768
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).
Feb 24 '08 #10

NeoPa
Expert Mod 15k+
P: 31,768
...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.
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 24 '08 #11

P: 26
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.
Feb 24 '08 #12

NeoPa
Expert Mod 15k+
P: 31,768
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.
Feb 24 '08 #13

P: 26
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
Expand|Select|Wrap|Line Numbers
  1. Dim qdf As DAO.QueryDef, strwall As String
  2. strwall = strwall & " TRANSFORM Sum(tblResults.pos) AS SumOfpos "
  3. strwall = strwall & " SELECT [membername] & Space(30) & '. ' & [seriesname] AS HELM "
  4. strwall = strwall & " FROM (tblRace INNER JOIN tblResults ON tblRace.raceauto=tblResults.raceID)"
  5. strwall = strwall & " INNER JOIN tblSeries ON tblRace.seriesID=tblSeries.seriesID "
  6. strwall = strwall & " WHERE (((tblRace.seriesID)= " & Me!Combo3 & "))"
  7. strwall = strwall & " GROUP BY [membername],[seriesname],[tblRace].[seriesID], [tblSeries].[seriesname] "
  8. strwall = strwall & " ORDER BY format([date],'mm/dd') & ' ' &  format([tblresults].[time],'hh:mm') "
  9. strwall = strwall & " PIVOT format([date],'mm/dd') & ' ' & format([tblresults].[time],'hh:mm');"
  10.  
  11. Set qdf = db.CreateQueryDef("qryWallQuery", strwall)
  12. qdf.Close
Feb 26 '08 #14

NeoPa
Expert Mod 15k+
P: 31,768
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.
Feb 26 '08 #15

Delerna
Expert 100+
P: 1,134
access 2007, yuk hate it. In fact office 2007 hate it.
I find myself constantly asking, now where have they put that .......
Hey microsoft, change for the sake of change is not good.
Feb 26 '08 #16

P: 26
I'm doing this application for my sailing club. The general committee insists on a 'pukka' licence so bought an Ofice 2007 3 user licence. Loading that onto the PC that will be located at the club, I found it necessary to double the memory just to get the performance back. I'm beginning to wonder if the best approach would be to have the licenced copy of office 2007 to show if required but stick to 2003 for the application!

I did wonder though if Access 2007 might have a few more goodies. Like a better CrossTab capability a la Excel for example ... doesn't seem to do anything better than 2003 but then I find the new user interface much less intuitive than the old and perhaps I haven't found the new goodies yet!

Thanks NeoPa for the 'Space 20' idea - didn't know you could do that.

Regards, Helm
Feb 27 '08 #17

NeoPa
Expert Mod 15k+
P: 31,768
...
Thanks NeoPa for the 'Space 20' idea - didn't know you could do that.
...
No problems. Glad it helped.
There's also a String() function for characters other than spaces. Not worth it for only a few, but when you get up to twenty then it saves space and is easier to understand.
Feb 27 '08 #18

P: 26
NeoPa ... can I check on site etiquete?

There's a number of things that have puzzled me as I've worked up this application. I'd like to ask about them but don't want to overstay my welcome. Should I generate a number of new posts, one for each or perhaps put a couple at a time?

For example, why is it that a modal form (such as a menu) and set for a certain size in the middle of the screen and hidden on menu selection will sometimes take over the whole screen when the called form is closed and the called form's closed event unhides the modal form. Sometimes it does and sometimes it doesn't.

Regards and thanks again for your help.

Helm.
Feb 29 '08 #19

NeoPa
Expert Mod 15k+
P: 31,768
As to site etiquette, it's generally better for each separate question to be in its own thread. The reasons include :
  • Managing one point at a time can be complicated enough. Knowing when complete etc.
  • Most experts only look for non-answered threads to jump into.
In short - it helps all involved.
Feb 29 '08 #20

NeoPa
Expert Mod 15k+
P: 31,768
...
For example, why is it that a modal form (such as a menu) and set for a certain size in the middle of the screen and hidden on menu selection will sometimes take over the whole screen when the called form is closed and the called form's closed event unhides the modal form. Sometimes it does and sometimes it doesn't.
...
The only issue I can think may cause this is when any object is maximised, its effect covers all objects. If the app is in maximised mode when the form is unhidden, then it will come back across the whole screen.
Feb 29 '08 #21

P: 26
The only issue I can think may cause this is when any object is maximised, its effect covers all objects. If the app is in maximised mode when the form is unhidden, then it will come back across the whole screen.
Yes, your'e right. Goes back a year or so. I'm trying not to use any built in toolbars. There's an opening modal form used as a menu. This calls another form. There's buttons on this form to fire reports that open in print preview with a custom toolbar to offer PRINT and CLOSE functions. When using the custom close button, the calling form returns but with a gap at the top of the screen where the custom toolbar had been. I only get the full screen back when exiting and reopening the calling form. I used docmd.maximise in each report and it seemed to 'solve' that problem - but it's come back to haunt me!

Here's the code I used to show and not show the custom toolbar in one of the reports ...If I remove the docmd.maximize on report deactivate I get the gap when returning to the calling form.

Hope this doesn't sound like gibberish.

Helm
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub Report_Activate()
  4. DoCmd.ShowToolbar "Print4", acToolbarYes
  5. End Sub
  6.  
  7. Private Sub Report_Deactivate()
  8. DoCmd.ShowToolbar "Print4", acToolbarNo
  9. 'DoCmd.Maximize  removed due large modal form problem
  10. End Sub
  11.  
  12. Private Sub Report_NoData(Cancel As Integer)
  13.  
  14. MsgBox "There are no entrants for this race!"
  15.  
  16. Cancel = True
  17. End Sub
Feb 29 '08 #22

P: 26
NeoPa

Thanks for the tip. I can now see that I need to Maximise on the activate event and minimize on the deactivate event. Problem solved. I'll play around a bit with this.Thanks again.

Regards, Helm
Feb 29 '08 #23

NeoPa
Expert Mod 15k+
P: 31,768
No worries.

I have a couple of classes that handle that in my dbs. I like my forms to be windows but reports to be maximised. The Form & Report classes handle this for me automatically.
Mar 1 '08 #24

Post your reply

Sign in to post your reply or Sign up for a free account.