Cross Tab Brainteaser

[Guest]

Hi -

I need help on the following that i've been working on forever.

I have 2 tables:

Table 1: Contains fields 'Supplier', 'Item', 'Quoted?', 'units', 'Currency'.
This table shows all items provided by various suppliers and whether they
have been quoted, type of units used, type of currency used.
Table 2: Contains fields 'No.', 'Item'. This table shows all possible items
to be provided by the suppliers and number 1 through 40.

I need to create a report that shows the following:
Supplier A Supplier B
Quoted? Units? Currency? Quoted? Units? Currency?
Item 1 Yes GBP No USD
Item 2 lb USD No
Item 3 No meters
..
..
..
..
Item 40

Thus, this is a cross tab query that builds a CHECKLIST type report. The
number of rows will always be 40. However, the number of suppliers varies but
the sub-headers (Quote, Unit, Currency) under each supplier remains the same.
In the case where there were no items quoted, they should still list the item
row but the cells will be blank.

A regular crosstab query will only produce a checklist with 1 header type
(for each supplier) instead of 3.

Please advise.

Thanks.

 

[Dale Fye]

Do you really want this as a report? If so, ignore the Cross Tab because
creating reports from crosstab queries can be a nightmare, although it can
be done.

Instead, try something like:

1. Create a query that will give you just the list of the suppliers. You
may already have a table that accomplishes this.

2. Create a query (qry_Supplier_Items) that includes your Suppliers query
(or table) and table2 from your original message. Do not join these tables.
The query would look something like the following. This Cartesian Join will
give you a result set that includes every supplier and every number (1-40).

SELECT Suppliers.Supplier, Items.No, Items.Item
FROM Supplier, Items

3. Create a new query that looks something like the following. By using
the left join in this query, you will still have a row for every row for
each supplier, but will only get the Quoted, Units, and Currency values for
those that have a matching record in your Table1.

SELECT Q1.Supplier, Q1.No, T1.Quoted, T1.Units, T1.Currency
FROM qry_Supplier_Items Q1
LEFT JOIN Table1 T1
ON Q1.Supplier = T1.Supplier AND Q1.Item = T1.Item
ORDER BY Q1.Supplier, Q1.No

4. Once you have this result set, create a multi-column report that wraps
around, ensuring that it displays all 40 rows in each column. Although this
method does have the down side that each column would include the Item #
descriptor.

HTH
Dale

 

[Guest]

Hi Dale -

Thanks for the prompt response. Question I have now is how do I create a
Multi-Column report that wraps? How exactly does this work? (I do not see it
in the report wizard)

Please let me know.
Thanks!

 

[Tom Lake]

Hi Dale -

Thanks for the prompt response. Question I have now is how do I create a
Multi-Column report that wraps? How exactly does this work? (I do not see
it
in the report wizard)

Please let me know.
Thanks!

It's in Page Setup for the report. (File, Page Setup)

Tom Lake

 

[Guest]

Hi Dale -

In your example below, I wanted to verify with you what Q1 and T1 is.

Please let me know when you get the chance.

Thanks!!

 

[Guest]

Hi Tom -

I am looking under page setup but do not see anything on multi-columns.
Something i'm missing? (Sorry i am new to Access)

Thanks!

 

[Tom Lake]

I am looking under page setup but do not see anything on multi-columns.

Something i'm missing? (Sorry i am new to Access)

When you open Page Setup in Design Mode, you should have three tabs,
Margins, Page, and Columns. When you click on the Columns tab, it lets you
select the number of columns you want, row spacing, column spacing, etc. If
I misunderstood what you're looking for, I apologize.

Tom Lake