Report Resembling Spreadsheet

[shepkrm]

Hello,

I am trying to create an Access report that basically mimics a spreadsheet. It would show the name of an item, the estimated budget and then a charge for each division.

Currently, I have several queries that determine each division's charge. Since not all divisions will have a charge for each item, I cannot create a relationship between the queries. Therefore, I cannot create a query that groups these nor can I create a crosstab query.

To get around this, I created a join query to get all the information in one place. However, the reports generated from this group by division.

Ideally, I would like the following format:

Item Estimate Division1 Charge Division2 Charge...

Does anyone have any ideas? I searched the FAQ's and the SQL and Access Reports sections to no avail.

Thanks in advance for your help!

Becky

 

[rac2]

I think there are two essential issues here.

One is the problem of rows into columns. Frequently the requirement is to display data in columns but SQL gives the data in rows. Access offers the crosstab as a solution. There are strictly SQL solutions and these can be found scattered through the SQL forums. There are limits to these.

Second is the problem of incomplete data, a table has rows for things that have a value, but some things do not have values so they are not in the table. There is a master table that has all of the things. The solution here is more straightforward. The solution is to use an outer join. This provides rows for everything in the master table even when there is no corresponding row in the detail table. An outer join is usually a LEFT JOIN.

Simple inner join that yields an incomplete result if the thing has no details.

SELECT a.description, b.quantity
FROM Goods a
JOIN Orders b ON b.goods_id = a.goods_id

An outer join yields a row for everything, whether it was ordered or not.

SELECT a.description, b.quantity
FROM Goods a
LEFT JOIN Orders b ON b.goods_id = a.goods_id

This is not entirely satisfactory however, because the missing details are represented by NULL. Therefore, you recode the NULL to something nice like "None", or 0.

SELECT a.description, 
            IIf(ISNULL(b.quantity), 0, b.quantity)
FROM Goods a
LEFT JOIN Orders b ON b.goods_id = a.goods_id

I am not proficient in Access so the syntax in this last query may not be correct, but the idea is correct and there is an equivalent function that can be used to determine whether you have a NULL value or not.

Also I have never used the crosstab feature. Nonetheless, I hope these ideas will help you find the solution.