Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 44
  1. #16
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    June
    I've said this many times before.
    Creating a crosstab report with dynamic headers does NOT require the use of parameters.
    Nor does it need to be tricky to do.
    I have many examples of such reports.
    For example https://www.access-programmers.co.uk...d.php?t=294548
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  2. #17
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Sorry, so PARAMETERS not needed if column headings are specified. Got it.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #18
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    That's true.
    But nor are they required using dynamic headings using the approach in my example.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #19
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Also don't see any dynamic filtering for any of those queries or reports. Would PARAMETERS be needed if there were?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #20
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    There is only one crosstab report in that example.
    It is the Attributes List by File Type report shown in the screenshot
    Select a variety of different file types in turn e.g. Wav, ACCDB, xlsx, txt, gif etc.
    The crosstab headings are updated dynamically using code which can be reused with only minor modifications in different applications.

    Dynamic refers to the fact that the column headers are not fixed, either on the report or the property sheet.
    I don't understand what you mean by dynamic filtering.
    However I do have other examples of dynamic crosstab reports where filtering is applied on the calling form e.g using comboboxes or option group entries.
    Now of course those filter values could be done using parameters.
    That's true for any report using a filter to select data.

    But I'll repeat my previous comment. Dynamic crosstab reports do not require the use of parameters.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #21
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I've never used CROSSTAB in production db, thankfully, but have experimented with. I just did another test.

    Without specifying column headers nor PARAMETERS, I get error:

    TRANSFORM Sum(Rates.Rate) AS SumOfRate
    SELECT Rates.RateLevel, Sum(Rates.Rate) AS [Total Of Rate]
    FROM Rates
    WHERE (((Rates.RateID) Like [enter Group] & "*"))
    GROUP BY Rates.RateLevel
    PIVOT Rates.Position;

    These do not error:

    TRANSFORM Sum(Rates.Rate) AS SumOfRate
    SELECT Rates.RateLevel, Sum(Rates.Rate) AS [Total Of Rate]
    FROM Rates
    WHERE (((Rates.RateID) Like [enter Group] & "*"))
    GROUP BY Rates.RateLevel
    PIVOT Rates.Position In ("Base","Plate");

    PARAMETERS [enter Group] Text ( 255 );
    TRANSFORM Sum(Rates.Rate) AS SumOfRate
    SELECT Rates.RateLevel, Sum(Rates.Rate) AS [Total Of Rate]
    FROM Rates
    WHERE (((Rates.RateID) Like [enter Group] & "*"))
    GROUP BY Rates.RateLevel
    PIVOT Rates.Position;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #22
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Without having the table to look at, its hard to comment
    However isn't the part I've highlighted in red a parameter?

    TRANSFORM Sum(Rates.Rate) AS SumOfRate

    SELECT Rates.RateLevel, Sum(Rates.Rate) AS [Total Of Rate]
    FROM Rates
    WHERE (((Rates.RateID) Like [enter Group] & "*"))
    GROUP BY Rates.RateLevel
    PIVOT Rates.Position;

    If you use it in the where clause then you will need to handle that elsewhere.
    I would select the RateID value(s) on the calling form

    For info, I have large numbers of dynamic crosstab reports in production databases.
    Its like any other aspect of Access.
    Once you use it a few times, it becomes easy.
    There are other areas I hardly use and so these seem difficult to me
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #23
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Yes, but the query lacks the PARAMETERS clause used in the third example.

    I should have been more clear about what I meant by PARAMETERS - I mean the PARAMETERS declaration clause if CROSSTAB has dynamic filter inputs.

    Yes, I would normally reference a form control for dynamic inputs. This was just a quick example so I did not have to modify a form.

    This issue is the same if referencing a control for input and the PARAMETERS clause is not used nor column headers specified. I've tried it before but just to be sure, tried it again and yes, it errors.

    And correction, I have 1 db where I implemented CROSSTAB. Data is not filtered going into the CROSSTAB. No PARAMETERS clause and no column headers specified. CROSSTAB is report RecordSource and filter is applied when opening the report.

    Only brought up PARAMETERS issue because I thought OP was applying filter to query the CROSSTAB is based on. But seems is instead saving records to a temp table and using that as source for CROSSTAB.

    So the real issue is that report is designed with textboxes bound to column headers that no longer exist because the dataset in the temp table has been changed. Correcting this with one of suggestions given should even allow elimination of the temp table intermediate step.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #24
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I can't make any suggestions about your example without seeing the underlying data/form/report

    You could have a look at another example - my Currency Exchange Rate Tracker http://www.mendipdatasystems.co.uk/cert/4594365453
    The free version is an ACCDE file
    Select the currencies you want to show and download some data for a few days.
    The data report will look something like this (also available on a subform)

    Click image for larger version. 

Name:	TrackerReport.PNG 
Views:	19 
Size:	46.1 KB 
ID:	36395

    The crosstab query SQL used for that report is:
    Code:
    TRANSFORM First(qryExchangeRates.Rate) AS FirstOfRateSELECT qryExchangeRates.Use, qryExchangeRates.Base, qryExchangeRates.CurrencyCode, qryExchangeRates.Currency
    FROM qryExchangeRates
    WHERE (((qryExchangeRates.Base)=GetBaseCode()))
    GROUP BY qryExchangeRates.Use, qryExchangeRates.Base, qryExchangeRates.CurrencyCode, qryExchangeRates.Currency
    ORDER BY qryExchangeRates.CurrencyCode, qryExchangeRates.Date
    PIVOT qryExchangeRates.Date;
    In this example, due to space limitations, only the last 20 records are selected in each case.

    The same query is also used as the basis for charting exchange rate data - also available on a subform & a report
    This time here is the subform to show how the currencies selected can be dynamically changed

    Click image for larger version. 

Name:	TrackerChart.PNG 
Views:	18 
Size:	57.8 KB 
ID:	36396

    All filtering is done on the forms - no PARAMETERS specified anywhere in the crosstab or any of the queries used to build that
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #25
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I did some edits to previous post, probably after you read it.

    I am not asking for any assistance, was just offering info on a possible issue that OP might have to deal with.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #26
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I had replied before seeing your last edited reply.
    I know you weren't asking for assistance though i'm willing to look at it if you would be happy to do so.

    The reason for showing the second example was precisely because the crosstab used for the subform & reports (both data & chart versions) are filtered from a total of almost 170 currencies.
    Yes of course parameters could be used for filtering data but I cannot think of a single occasion where I've used these for several years in any type of query/form/report.
    I avoid them completely as data selection using parameters is in my opinion more difficult for end users and cannot be validated.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #27
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I also do not like dynamic parameterized queries and avoid them, and I would NEVER use popup inputs. If I did have to use dynamic parameter, it would reference a form control for input.

    I have never thought of using a function call for dynamic filter input to a CROSSTAB (BTW, this is what I would call a dynamic parameterized query). This is certainly viable alternative to PARAMETERS clause or specifying column headers. However, for anyone not comfortable with VBA, the PARAMETERS clause and/or specifying column headers is available.

    Another correction, found another db where I employ CROSSTAB. I am sure that is all.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #28
    chavez_sea is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2011
    Posts
    105
    Thanks for answering.
    My initial question was whether you can run a report from a crosstab query.
    so at this pint I have a query that retrieves the data as per mycriteria for any given month, deletes a table in input the new data into the table. Another query makes the crosstab. I run both from a macro. If I get the report running I would include in the macro. When I change the criteria, run the macro, the reports work the first time, say for November. I change the criteria to December, run the macro, data in the table has changed, the error message for the report is : ms database engine does not recognize [11/4/18] as a valid field name or expression.


    microsoft does not recognize [11/418] as a valid field name.

  14. #29
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Recommend you look at the examples I gave in the previous posts
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #30
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Links to examples and tutorials in posts 5, 8, 16, 24.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-15-2017, 08:44 AM
  2. Cross-Tab Report?
    By WCStarks in forum Reports
    Replies: 12
    Last Post: 08-14-2017, 03:14 PM
  3. Replies: 3
    Last Post: 07-26-2016, 02:12 PM
  4. Pass Parameters from Cross Tab Query to Report
    By jokeboy1 in forum Reports
    Replies: 5
    Last Post: 01-02-2015, 01:26 PM
  5. Adding rows on cross-tab query report
    By KahluaFawn in forum Reports
    Replies: 2
    Last Post: 02-18-2009, 10:09 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums