473,325 Members | 2,671 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Does Access have Limited Crosstab compared with Excel?

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
23 2501
Stewart Ross
2,545 Expert Mod 2GB
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
helm
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
helm
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
32,556 Expert Mod 16PB
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
helm
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
32,556 Expert Mod 16PB
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
helm
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
helm
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
...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
helm
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
32,556 Expert Mod 16PB
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
helm
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
32,556 Expert Mod 16PB
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
1,134 Expert 1GB
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
helm
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
32,556 Expert Mod 16PB
...
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
helm
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
...
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
helm
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
helm
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
32,556 Expert Mod 16PB
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

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

Similar topics

0
by: Jack | last post by:
Windows 2K Pro Access/Excel 2003 Hi there, I have a large number of password protected Excel Workbooks. The files are protected by code that runs when the workbook opens. The code asks the...
18
by: Andre Laplume via AccessMonster.com | last post by:
I have inherited a bunch of dbs which are are shared among a small group in my dept. We typically use the dbs to write queries to extract data, usually dumping it into Excel. Most dbs originated...
4
by: Kaur | last post by:
Hi, I am trying to create a crosstab type of report without using crosstab query. The report captures several survey questions and for each survey questions there are multiple choices that users...
1
by: Brad | last post by:
Thanks for taking the time to read my question. I have a table of data that has Date, Data and Category. I need to show, in a report, each Categories Data by Date. The Date has to be it's own...
0
by: Tom | last post by:
Hi: I'm using a crosstab query to export data to an Excel spreadsheet for further processing. The source of the data for the crosstab is a temporary table. The temp table has 3 fields: *...
1
by: qarmoe | last post by:
Here is the db with sample data. http://download.yousendit.com/AAA18BC7520F196E I want to use qryCrosstab and table "ALL" to achieve the result that would look like this query...
5
by: garethfx | last post by:
Hi all Ive 2 crosstab queries that I have been asked to export the data from to excel. My sql isn't to cleaver so I,m stuck. I need to place the queries outputted data into specific cells in...
4
by: rdsandy | last post by:
Hi, I have some code below in VBA for Access 2003 which is on a button called "RentalCrosstabPercTtlQtyMonthLoc_Click". This is a crosstab query which brings up rental items down the side, who...
9
by: prakashwadhwani | last post by:
Hi !! I'm about to develop a new project for a client. Should I go about it in Access 2003 or 2007 ? Purchasing it either for me or for my client is not a major consideration here ... what I'd...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.