Summing Tables in a report 1

[Aubs010]

I have a table with two columns that I need to get the data from.

| Element | Priority |  Cost  |
|---------|----------|--------|
|   A     |    0     |   0.00 |
|   B     |    6     |   0.00 |
|   C     |    1     | 100.00 |
|   D     |    1     |  30.00 |
|   E     |    1     |  70.00 |
|   F     |    2     |  20.00 |
|   G     |    4     |  40.00 |
|   H     |    3     |  30.00 |
|   I     |    3     |  30.00 |
|   J     |    4     |  20.00 |
|   K     |    1     |  70.00 |
|   L     |    0     |   0.00 |
|   M     |    4     |  20.00 |
|   N     |    4     |  45.00 |
|   O     |    6     |  00.00 |
|   P     |    1     | 200.00 |

Priority 0 is a blank (if they don't select anything)
Priority 6 is a header and will always be zero

Now, I want the report to display the sums of each of the prioritys. So for the example above, I want it to display the following results:

1 - 470.00
2 - 20.00
3 - 60.00
4 - 125.00
5 - 0.00

I can't for the life of me do it!!!

I would have thought it would be easy!!

INFO: the database will only hold one instance of each of the above. (unline a database!!) there will not be any repeats for different sites.

Any help would be greatly appreciated


Aubs

 

[Steve101]

Construct a query with the following SQL:

SELECT Priority, Sum(Cost)
FROM tblYourTable
WHERE Priority BETWEEN 1 AND 5
GROUP BY Priority

This should do the trick. Note you'll obviously have to change the tablename to whatever your table is called.

Hope this helps,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Aubs010]

Sorry that this post will make the page need a horizontal scroll...

As a continuation, I have made a Crosstab query in this manner:

FIELD:    || ID          | Ref         | Compliance  | 
Priority       | Priority | Budget Cost | Compliance | 
Priority                          |
----------||-------------|-------------|-------------|------
----------|----------|-------------|------------|-----------
------------------------|
TABLE:    || Elements    | Elements    | Elements    | 
Elements       | Elements | Elements    | Elements   | 
Elements                          |
----------||-------------|-------------|-------------|------
----------|----------|-------------|------------|-----------
------------------------|
TOTAL:    || Group By    | Group By    | Group By    | 
Group By       | Group By | Sum         | Where      | 
Where                             |
----------||-------------|-------------|-------------|------
----------|----------|-------------|------------|-----------
------------------------|
CROSSTAB: || Row Heading | Row Heading | Row Heading | 
Column Heading |          | Value       |            
|                                   |
----------||-------------|-------------|-------------|------
----------|----------|-------------|------------|-----------
------------------------|
SORT:     ||             |             |             
|                |          |             |            
|                                   |
----------||-------------|-------------|-------------|------
----------|----------|-------------|------------|-----------
------------------------|
CRITERIA: ||             |             |             
|                |          |             | False      
| "1" Or "2" Or "3" Or "4" Or "5"   |
OR:       ||             |             |             
|                |          |             |            
|                                   |

Ok, this seems to work ok apart from the fact that it brings up 50 pages (one for each of the "Where priority is either 1/2/3/4/5).

BUT, if there are no priority 5's, then it brings up an error, because there's no 5!

All I want the query to bring up is (from the sample data I provided in the first post):

|ID| REF |  Compliance | 1       | 2      | 3      | 4      | 5      |
|==|=====|=============|=========|========|========|========|========|
|1 |  c  |  NO         |1 100.00 |        |        |        |        |
|--|-----|-------------|---------|--------|--------|--------|--------|
|1 |  d  |  NO         |1  30.00 |        |        |        |        |
|--|-----|-------------|---------|--------|--------|--------|--------|
|1 |  e  |  NO         |1  70.00 |        |        |        |        |
|--|-----|-------------|---------|--------|--------|--------|--------|
|1 |  f  |  NO         |2        | 020.00 |        |        |        |
|--|-----|-------------|---------|--------|--------|--------|--------|
|1 |  g  |  NO         |4        |        |        | 040.00 |        |
|--|-----|-------------|---------|--------|--------|--------|--------|
|1 |  h  |  NO         |3        |        | 030.00 |        |        |
|--|-----|-------------|---------|--------|--------|--------|--------|
|1 |  i  |  NO         |3        |        | 030.00 |        |        |
|--|-----|-------------|---------|--------|--------|--------|--------|
|1 |  j  |  NO         |4        |        |        | 020.00 |        |

This way, I can refer to 1/2/3/4/5 and even if they are zero, do an if statement:

IIF([1] is null,"",Sum([1]))


Please help me if you can, I would appreciate it very much.


Aubs

 

[Aubs010]

Well I never!!!

That does work correctly Steve, thanks!

The next question, how would I get that data out into a report in the format that I want?

I want it like:

Priority 1 : £470.00
Priority 2 : £ 20.00
Priority 3 : £ 60.00
Priority 4 : £125.00
Priority 5 : £ 0.00

Much appreciated Steve


Aubs

 

[Steve101]

If the Cost field is defined as a Currency type field, then the field should format correctly; otherwise use the format property associated with the column in the query grid (point at the Cost column, and choose the View, Properties menu options to display the properties).

To show the Priority prefix, use the following modified SQL:

SELECT "Priority" & T.Priority AS Priority,
Sum(T.Cost) AS Cost
FROM tblYourTable AS T
WHERE T.Priority Between 1 And 5
GROUP BY "Priority" & T.Priority;

Hope this does it,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Aubs010]

That again does it perfectly, however, how can I get it to show that Priority 5 is zero if there are no items with a priority 5?


Aubs

 

[Steve101]

A number of ways to do this:

(a) Simplest: "Plant" a dummy row of data with a zero row. This is a Q&D approach though. Its fine though it could skew statistics (eg. averages) for low counts.

(b) More complex. Create another "master table", with one entry for 1,2,3,4,5,6 in each, and use an appropriate join to get what you want.

You probably need a little help with the above, which perhaps someone else can provide

Its getting late down here, and for me, its time to crash for the night,

Good luck,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Aubs010]

Okies, Sweet dreams!!

Thanks for all your help Steve, it has been very much appreciated


Aubs

 

[Aubs010]

Thanks for your help I decided to use maketable and append queries to create a temporary table and then take the data from there.

Regards,


Aubs

 

[CosmoKramer]

Aubs010,

Check out my response in the following thread dealing with crosstab queries:

thread703-519641

Let me know if this helps