473,698 Members | 2,023 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Does Access have Limited Crosstab compared with Excel?

26 New Member
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 2551
Stewart Ross
2,545 Recognized Expert Moderator Specialist
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 New Member
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 New Member
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,569 Recognized Expert Moderator MVP
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 New Member
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,569 Recognized Expert Moderator MVP
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 New Member
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 New Member
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,569 Recognized Expert Moderator MVP
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

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

Similar topics

0
2835
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 user to key in a password into an input box, the password is compared to a a partciular cell on a hidden (xlVeryHidden) sheet and if it matches the Workbooks opens. I have not used the usual Excel password protection as I need to provide for several...
18
7337
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 in MsAccess 97 or prior and have been converted to 2003. On occassion user 1 will open a db. When user 2 opens the db it will not let user 2 modify macros and what not. I can understand this and realize we could split the db; it is not worth ...
4
350
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 responded to. Basically I am trying to create a report that will look some thing like this displaying question text, multiple reponse text and the response rate: Text for Question 1 Agree Disagree Strongly Agree 70% 20%
1
5159
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 column across the top and Category down the left side. As data is entered, the number of unique dates increases. As a result the
0
1270
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: * MonthYear (row header in the crosstab) * CriteriaName (column header in the crosstab) * CriteriaValue (value in the crosstab) There are only 6 distinct values in CriteriaName, two of which are
1
1613
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 "qryFinalResult". Table has a field called filename and it stores the table name. possible ????
5
7780
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 each of 2 worksheets (nice one so far i guess - not). Ive tried throught the macro system and through tools ~office links with no joy. These crosstabs are created each month so that that data will change and therefore will need to effectivly...
4
2483
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 rents along the top, the rental month and total number of each item on that month as rows next to the item, something like this: Item TotalQty Month Renter1 Renter2 .... PC 234 Aug-2007 2.33 4.55 ...
9
4491
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 like to know is the stability, speed & ease of use of both the products. I believe Access 2007 has a new file format too and that it may be slower.
0
8674
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8603
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9157
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9027
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8861
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7725
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6518
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4369
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4619
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

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.