Results 1 to 10 of 10
  1. #1
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194

    Using A Dynamic Crosstab Query On A Form


    I have a crosstab query that compares up to 15 recipes depending on how many recipes the user wants to review. I want to know if there is a way to show the crosstab results on a form or report. There are 15 text boxes on frm_BulkComparison_Select for the user to input recipes into, another text box for the user to select the bill type, and a final text box for them to identify the branch plant associated with the recipe. If the user inputs only 5 recipes, the crosstab only shows 5 columns.

    I'd like this to display on a report or form so that I can add additional information from other tables such as the tool used for each recipe, the price of each recipe, and the coating ingredients associated to each recipe.

    I'm familiar with code, so if there is a VBA solution please let me know. I found one at http://www.helenfeddema.com/Files/accarch221.zip, but I need a little help breaking it down because it doesn't look like it will adapt very well to my request. I may be wrong though.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I use the code from post 3 (I have the book), plus there appear to be other solutions offered:

    http://www.utteraccess.com/forum/ind...wtopic=1976652
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This looks like a method that eliminates a lot of vba code. I confess I've never tried it, but wish I had come across it in years gone by. Apparently, all you might need is the IN clause. The example at the link is for a 12 month report where the report column labels are obviously going to need changing. However, seems to me I read before that if you design the report with the max number of fields required and include them all in the IN clause, you will always get them, data or no. If you try it and it works, please be sure to let me know.

    http://www.fmsinc.com/microsoftacces...ort/index.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    Hi Paul, I tried to use the code from post 3 of the link you shared, but the post assumes I know how to display a crosstab on a report already. I do not.

    Hy Micron, I looked over the information from fmsinc.com and tried to apply it but got stuck on the IN clause in the crosstab. I do not know how to change the recipe input to a number format. (example: user inputs '095AR' as the recipe. How do I transform this variable into the (1,2,3, etc.) format? Or better yet, how do I change the IN values to equal form fields? Like instead of IN (1, etc.) I have IN (Forms!frm_BulkComparison_Select!Item1, etc.)

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure but I don't see why you'd have to change your input to numbers. The section Use Numbers Rather than Specific Month Names is probably what you need to concentrate on and apply to your situation. They were using 12 months, thus it was easy to relate to 1 to 12, plus it's easy to make "labels" holding the year-month data as shown. However, I think the point is that the IN clause fixes the number of fields in the cross tab, and if there's no data, the field is still present in the report. As I think I mentioned, I never got to try this; I only wish I knew about it when I needed it. According to the web page, it is based on tables/queries from the Northwind db, so you could try playing with it. It would be awfully lengthy for sure, but it appears from your last post that IN (Forms!frm_BulkComparison_Select.Item1, Forms!frm_BulkComparison_Select.Item2, Forms!frm_BulkComparison_Select.Item3,... might be all you can do in such a query. Alternatively, you could create the sql in code and use much shorter variables for form references.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by lccrews View Post
    Hi Paul, I tried to use the code from post 3 of the link you shared, but the post assumes I know how to display a crosstab on a report already. I do not.
    The code assumes you've built a report with generic numbered textboxes and labels for the maximum number of fields to be displayed. It then shows/hides/labels them appropriately.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    THanks for that Paul. I have the generic fields set on rpt_Comparison. When looking at the code from Post #3 I see that rst = New ADODB.Recordset and I assume that this is where I place the SQL for my query? I am having difficulties getting my crosstab query to function here. Can you look at my SQL and tell me if anything is wrong? It keeps giving me "error: Syntax Error"

    Code:
    Set rst = "SELECT tbl_Formulas.BillType, tbl_Formulas.RawMaterial, tbl_Formulas.Input " _FROM tbl_Formulas " _
    WHERE (tbl_Formulas.BillType)=[forms]![frm_BulkComparison_Select]![bill1] Or (tbl_Formulas.BillType)=[Forms]![frm_BulkComparison_Select]![bill2] Or " _
    (tbl_Formulas.BillType)=[Forms]![frm_BulkComparison_Select]![bill3] AND " _
    (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 1] Or (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 2] Or " _
    (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 3] Or (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 4] Or " _
    (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 5] Or (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 6] Or " _
    (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 7] Or (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 8] Or " _
    (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 9] Or (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 10] Or " _
    (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 11] Or (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 12] Or " _
    (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 13] Or (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 14] Or " _
    (tbl_Formulas.Item)=[Forms]![frm_BulkComparison_Select]![Item 15] AND (tbl_Formulas.BP)=[Forms]![frm_BulkComparison_Select]![BP] " _
    GROUP BY tbl_Formulas.BillType, tbl_Formulas.RawMaterial " _
    PIVOT tbl_Formulas.Item;"
    I copied the code straight from the crosstab query in SQL view. I left out the PARAMETERS and TRANSFORM portions. Should I add those back? I tried adding "& " " to the start of each row as I've done this with all my other SQLs but then I get "error: Type Mismatch"

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm not on a computer, but no. The code assumes your query is the report's record source. Then it gets it with this line:

    Source:=Me.RecordSource
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    I'm sorry Paul, but I am not getting the desired output when using that code. I simply can't get it to work. I'm just going to have my form button output an excel document with the crosstab information and use multiple ADODB.recordsets to input further information below the crosstab information. Thank you so much for trying to help, Paul. Maybe soon I'll be able to comprehend that code.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If you want to attach a db with the failing code, I can tweak it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-22-2017, 03:39 AM
  2. Replies: 1
    Last Post: 01-26-2017, 08:29 AM
  3. Dynamic CrossTab Report from Query
    By ZJGMoparman in forum Programming
    Replies: 1
    Last Post: 08-20-2015, 10:54 AM
  4. Replies: 2
    Last Post: 10-09-2014, 11:37 AM
  5. Replies: 1
    Last Post: 07-30-2010, 10:28 AM

Tags for this Thread

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