Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to combine crosstab queries?

Posted on 2011-04-25
5
Medium Priority
?
451 Views
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
0
Comment
Question by:Anthony6890
  • 3
  • 2
5 Comments
 
LVL 13

Expert Comment

by:Lucas
ID: 35462013
You can do one of 2 things:

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

OR

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.
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 35462090
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...
0
 
LVL 13

Accepted Solution

by:
Lucas earned 1500 total points
ID: 35462383
Union your queries.  Make sure they all have the same number of fields.

Select field_1 as field1, field_2 as field2 from 1stquery
UNION
Select field_1 as field1, field_2 as field2 from 2ndquery
UNION
Select field_1 as field1, field_2 as field2 from 3rdquery
UNION
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.

0
 
LVL 1

Author Comment

by:Anthony6890
ID: 35466801
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.  
0
 
LVL 1

Author Closing Comment

by:Anthony6890
ID: 35466811
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.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

578 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