How to combine crosstab queries?

Posted on 2011-04-25
Last Modified: 2012-05-11
I need to combine 4 cross tab queries in access into one.

My tables have a common row heading:  MBFTPT but the heading changes to represent four different stores.  Does anyone know how I can do this?  I basically want it to look like this:

MBFTPT    S&S      PMK      SR
F                  60        120       72
P               42            10       62
Question by:Anthony6890
    LVL 13

    Expert Comment

    You can do one of 2 things:

    1. Dump ALL your data into one table, then do your crosstab from there


    2. Go into your query design, and bring up the properties.  There is a property called "column headings".  Type in the headings that you want, and it should display them even if the values are null.
    LVL 1

    Author Comment

    Hey thanks for getting back to me.

    I don't know if I really want to dump all my data into a table and do a crosstab from there.

    When you say to go to my query design, which query are you referring to?  Right now I have the 4 separate queries, nothing has been combined yet...
    LVL 13

    Accepted Solution

    Union your queries.  Make sure they all have the same number of fields.

    Select field_1 as field1, field_2 as field2 from 1stquery
    Select field_1 as field1, field_2 as field2 from 2ndquery
    Select field_1 as field1, field_2 as field2 from 3rdquery
    Select field_1 as field1, field_2 as field2 from 4thquery

    depending on how much data you got, you might run into performance issues.

    Then create a cross tab on the union query.

    LVL 1

    Author Comment

    What I ended up doing was using a union query to combine all the cross tab queries.  I then input a line of code to sum across the row and create a totals column.

    Thanks for your help.  
    LVL 1

    Author Closing Comment

    The solution only offered one part of the problem.  I needed to add a lot more to get the answer to do what I needed it to do.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now