Showing correct data in a report's columns

[tikkat3]

I am not sure if this best belongs in the Report or Query forum. I have been asked to create a report which details line by line (each line a contract number) the products sold in a given timeframe against that contract.

Most of the report has been easy to set up, but I do not know how to do the key part of it.

I have a table, which holds all installed product details. The fields are [ID], [Product], [Quantity] and [Contract].

[Contract] is linked by relationship (many-to-one) to the [Contract] field in the main table.
[ID] is an autonumber and this table's PK.
[Product] is linked by relationship to the Product table, which holds the full product listing.
[Quantity] is a field to record the quantities of products sold on each job.

The report has 10 product columns to show the different products sold against each contract. What I cannot do is to sort in the report (or query) so that I can show the correct number of products sold against the correct contract.

The column headings are in the Page Header section of the report and the actual data in the Details section.

I would appreciate any guidance for this problem.

 

[georgedwilkinson]

Take a look at crosstab queries in help. You would use a crosstab query as your data source.

Since I'm not a report guru, I can't help you much more than that. Just noticed your request has been sitting around a while and didn't want it to go stale.

 

[tikkat3]

Thanks. I will have a look at crosstab and see if it does what I need!

 

[tikkat3]

The crosstab query works. However, it will not display the data as I need it. I think it is probably a simple solution - but what?!

The current results display as:

Contract ProductA ProductB ProductC
1111 1 (Prod A column)
1111 5 (Prod B column)
1111 2 (Prod C Column)
2222 7 (Prod A column)
2222 1 (Prod B column)
2222 3 (Prod C column)


What I am wanting it to do is below:

Contract ProductA ProductB ProductC
1111 1 5 2
2222 7 1 3

I cannot do this in my report. (sorry matrix layout of the examples would not sit where they should do!)