Microsoft Access 2007 Report with Conditional Columns

Question

I am looking to make a very simple report to condense and show data side by side. All of the examples of reports I find are only row by row.

The query I will use will only have three schema "Company, Model, Total"

The format I am trying to get to is

Company     Model     Total     Company     Model     Total
 A           123       2         B           123       4
 A           222       3         B           333       3
 A           444       7         B           444       7

The idea is to present the information in a way that multiple companies side by side can compare inventory of the same model and find discrepencies. Ideally the report would eventually group all Model's that span every company at the top, but thats a next generation problem.

I have attempted conditional formating on multiple "Company" boxes, but the conditionals do not seem to be applying properly or for some reason every "Company" box is adopting the same conditionals.

Answer 1

I think you want a crosstab query grouping by model (the rowHeader), company as the column header, and first(total) as the value.

The results should look like

model   A total B total
123     2       4
222     3   
333             3
444     7       7

then you can create another query based on the crosstab results to calculate the difference between company totals, if you want.

Answer 2

You have to do this in two steps:

1. Build a query that gives you:

 Company  Model   Total
 A         123    2
 A         222    3
 A         444    7
 B         123    4
 B         333    3
 B         444    7

Let's call q this query.

2. Build a second query

SELECT q1.Company, q1.Model, q1.Total, q1.Company, q2.Model, q2.Total
FROM q AS q1 INNER JOIN q AS q2 ON q1.Model = q2.Model
WHERE q1.company < q2.company;

This will give you:

 A    123   2   B   123   4  
 A    444   7   B   444   7

(There are no matching data for models 222 and 333)