Results 1 to 9 of 9

Crosstab Query

  1. #1
    UT227 is offline Competent Performer
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    418

    Crosstab Query

    I'm trying to make a crosstab query on car mileage. I need the months to go across the top and which car would be the left row. I need the average mileage of each car on each month. I made a query which gets each car and sums the mileage of each car. However, I need to filter the query to which year I need to look at. I made a search form where I can look at only the year I need. The criteria in the query is: [Forms]![frmVehicleMileageSearch]![txtYear]. When I try to make a crosstab query out of that, I get an error.
    Click image for larger version. 

Name:	Error.jpg 
Views:	15 
Size:	25.6 KB 
ID:	38164



    I'm not very familiar with crosstab queries. I know that I probably made several mistakes. Could use help getting this done.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,603
    Note: a crosstab ALWAYS must have a parameter setting: query design, parameter button on toolbar.
    yours will be
    [Forms]![frmVehicleMileageSearch]![txtYear]

    select queries don't need the PARAMETER setting, but these do.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,029
    When you run this query, that form is open, and the form name and the control name is properly spelled? Even if you directly run a query without such a reference but that query uses a query as a datasource (as opposed to a table) the form would still have to be open and the references correct. That's known as a stacked query, meaning a query that "calls" another query to get data. Any one in the stack has to be able to resolve a reference.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  4. #4
    UT227 is offline Competent Performer
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    418
    I completely forgot about the parameters. That worked great. Thanks. Now, the column headings are months. How would I get them in order? Jan-Dec?

  5. #5
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,603
    you must use format: yymm
    so they will sort.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,722
    Or specify month column headings in PIVOT clause. More about crosstab http://allenbrowne.com/ser-67.html
    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. #7
    UT227 is offline Competent Performer
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    418
    I need to have all of the year's months appear on the query. I have a month table (tblMonthList). The primary key is [MonthListID]. I do not have a foreign key in the mileage table. I have a use date, which I got the month from by putting in the query, ChkMonth: Month(UseDate). I joined these tables with a left join so that all of the months on tblMonthList would appear . However, all of the months do not appear. I'm not sure if I need an actual foreign key in the mileage table that will join with the month table. Here's what I'm doing:

    Click image for larger version. 

Name:	Query1.jpg 
Views:	6 
Size:	185.7 KB 
ID:	38170

    Click image for larger version. 

Name:	Query2.jpg 
Views:	6 
Size:	182.5 KB 
ID:	38171

    Click image for larger version. 

Name:	Query4.jpg 
Views:	6 
Size:	98.9 KB 
ID:	38172

    Click image for larger version. 

Name:	Query3.jpg 
Views:	6 
Size:	102.2 KB 
ID:	38173

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,722
    Did you read post 6?
    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. #9
    UT227 is offline Competent Performer
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    418
    Yes I did. I didn't understand it at first, second or third time. I stepped back a bit and got it the next time. Thanks.

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

Similar Threads

  1. Replies: 17
    Last Post: 07-13-2017, 05:23 PM
  2. Replies: 8
    Last Post: 05-11-2017, 07:54 AM
  3. Replies: 2
    Last Post: 04-30-2014, 03:01 PM
  4. Replies: 2
    Last Post: 08-16-2013, 01:36 AM
  5. Replies: 1
    Last Post: 07-30-2010, 10:28 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
  •  
Tech Forums: Microsoft Office Forums