Query/Subquery - Percentage

[zeusspandex]

Hello everyone

i have 3 fields:

1. Code (Mon01, Mon02 etc)
2. Recycling (Check box, yes or no)
3. Tonnage

There are over 10,000 records in this table. I am trying to calculate in a query the percent for each code (Total approx 70), from the sum tonnage for records that are recycling (Check box ticked)

I have tried using a subquery in the tonnage field selecting records where recycling = yes. i get an error message saying the subquery will return 1 if any results.

Any help please!!!!

[NeoPa]

Start by designing a query which is GROUPed by [code] and WHERE [Recycling].

Next build a query from that where each record has the count divided by a DCount() of all the [Recycling] records in the table.

Does that make sense?

[zeusspandex]

erm roughly.

How would you go about writing the Dcount/Count expression for the second part??

[NeoPa]

DCount("*", "[YourTable]", "[Recycling]")

NB. The third parameter is not simply a field reference. It is a WHERE clause with the "WHERE " removed. As [Recycling] (as far as I can tell anyway) is a boolean field, the value is a TRUE/FALSE value, which is exactly what is required for a WHERE entry (no manipulation needed).

IE. The expression [Field] = 'A' actually resolves to a boolean value. A boolean field already IS a boolean value.

Is this clear and helpful?

[zeusspandex]

hmm,

I do apologise for perhaps being a little slow. Im still confused. I undertand the DCount function now but i am still unsure how to make it all work.

Ive started by grouping by [code] and summing the total tonnage for each one.

Now i need to a field which sums how much of this tonnage (Kg) can be attributed to recycling.

From this i just need to divide the first summed tonnage by 100, then times this figure by the sum of tonnage which can be attributed to recycling.

I do apologise again but is there anyway you could perhaps spell it out more clearly and completely?

[zeusspandex]

Update -

I have constructed a query that groups by [code]
I have summed the [tonnage] attributed to this code
I have now another field that divides the attributed code by 100

now i need another field that sums the [tonnage] attrbuted to the code which has the [recycling] check box ticked.

can i do this with "SELECT [Tonnage] From [Tonnage] WHERE [Recycling]-True"?

[zeusspandex]

how about useing DSum

for example DSum ("[Tonnage]![Tonnage]", "[Tonnage]", "[Recycling] = True'")

(not worked by the way) but am i on the right lines?

[NeoPa]

I've caught this very late so I need to look at it again tomorrow.

If I miss it after 24hrs, please bump the thread.

[zeusspandex]

this is a reminder lol :)

[NeoPa]

Well bumped.

I have lunch in 1/2 an hour, so I'll look at it then in more depth.

[NeoPa]

Having read some of your later posts I now appreciate what your first post meant, more clearly.

Rather than the percentage [Tonnage] that each [ Code ] (Not a good name to use on this site as it treats it as meaning [ CODE ] tags) is, of the total [Tonnage] and completely ignoring items which are not [recycling], you mean the percentage, within each [ Code ], that the [Recycling] [Tonnage] is of the total [Tonnage] for that [ Code ].

This means that my earlier advice is inappropriate for this question.

I see, however, that you have made some progress on your own with the logic, so I will put down what I recommend and we can progress from there.

[NeoPa]

To avoid posting problems on this site I will refer to your [ Code ] field as [TCode].

To avoid ambiguity and confusion I will refer to your table as [tblTonnage]. The table name of [Tonnage] - which I get from your post #6 - is unfortunate, as this also appears to be the name of one of the fields.

The SQL should be (relatively) straightforward with the new understanding of the question.

SELECT [TCode],
       Sum(IIf([Recycling],[Tonnage],0)) AS RecycTonnage,
       Sum([Tonnage]) AS TotTonnage,
       (100*[RecycTonnage]/[TotTonnage]) AS RecycPC
 
FROM [tblTonnage]
 
GROUP BY [TCode]

Have a look at how it does it and see if you can A) Understand it; B) Confirm this should do the job you need.

[zeusspandex]

Yea thats works great thank you.
The fields you were having difficulty with do actually have different names but i simplified them for what i thought would be easier for understanding.

A it works and B i understand it.

The only problem i am now having is that when i try and open the query, access asked me to state the value of [Recyctonnage] and [TotTonnage]. If i click Ok it still works but iw ouldnt mind getting rid of this minor problem. Also i need to sort the result so that [RecycPC] is displayed highest first etc. I have tried to do this by sorting the field in the design view of the query but to no avail.

Thank you once again

[NeoPa]

Yea thats works great thank you.
The fields you were having difficulty with do actually have different names but i simplified them for what i thought would be easier for understanding.

A it works and B i understand it.

The only problem i am now having is that when i try and open the query, access asked me to state the value of [RecycTonnage] and [TotTonnage]. If i click Ok it still works but iw ouldnt mind getting rid of this minor problem. Also i need to sort the result so that [RecycPC] is displayed highest first etc. I have tried to do this by sorting the field in the design view of the query but to no avail.

Thank you once again

I suspect that this only arises after you have attempted to change it to do the sorting. Understandable enough.

The original SQL (that I posted) doesn't have this problem, but allowing Access to try to do the sort for you will cause this.

A revised version which does the sort too is more complex as the sorting (ORDER BY clause) is done before the SELECT results are available, so it is not possible to refer to them by their ALIASes.

Try this instead :

SELECT [TCode],
       Sum(IIf([Recycling],[Tonnage],0)) AS RecycTonnage,
       Sum([Tonnage]) AS TotTonnage,
       (100*[RecycTonnage]/[TotTonnage]) AS RecycPC
 
FROM [tblTonnage]
 
GROUP BY [TCode]
 
ORDER BY (100*Sum(IIf([Recycling],[Tonnage],0))/Sum([Tonnage])) DESC

[NeoPa]

A new question has been split off from this thread and can now be found at Cross-Tab Problem.

[zeusspandex]

Hey

any way of putting a [Date] criteria into this? using date fields on the form.

SELECT Tonnage.SteveCode, Sum(IIf([Recycling],[Tonnage],0)) AS RecycTonnage, Sum(Tonnage.Tonnage) AS TotTonnage, (100*[RecycTonnage]/[TotTonnage]) AS RecycPC
FROM Tonnage
GROUP BY Tonnage.SteveCode
ORDER BY (100*Sum(IIf([Recycling],[Tonnage],0))/Sum([Tonnage])) DESC;

i was thinking somthing along the lines of :

Sum(IIf([Date],(Sum(IIf([Recycling],[Tonnage],0)), (Between [Forms]![Recycling Rates]![From Date] And [Forms]![Recycling Rates]![To Date])

am i close?

by the way this is the SQL from this original thread not the split of thread.

[NeoPa]

I have re-added a post which was mistakenly switched to the other thread. I can see your latest post is relevant to this thread too, but it might take me a while to work out what you're on about. The post is not well expressed and I'm just hoping I can work out what you mean if I process through it all in detail :(

[NeoPa]

Luckily, although the form and the controls on it have never been mentioned before, I can work out from your suggested SQL, what it is you seem to be trying to do. It's much better to explain your situation in words, but at least it's here.

What you need is a WHERE clause. I have to assume, as you haven't included it in your question (one of the most fundamental parts of what should have been the question) that you have a field in [Tonnage] called [Date]. Like having a field called [Tonnage] in [Tonnage], having a field called [Date] is really not recommended. I should say these are highly recommended AGAINST. The problems these will cause you are perfectly avoidable. It seems a shame to suffer from them in the circumstances.

Anyway, try this :

SELECT [SteveCode],
       Sum(IIf([Recycling],[Tonnage],0)) AS RecycTonnage,
       Sum([Tonnage]) AS TotTonnage,
       (100*[RecycTonnage]/[TotTonnage]) AS RecycPC
 
FROM [Tonnage]
 
WHERE [Date] Between [Forms]![Recycling Rates]![From Date]
                 And [Forms]![Recycling Rates]![To Date]
 
GROUP BY [SteveCode]
 
ORDER BY (100*Sum(IIf([Recycling],[Tonnage],0))/Sum([Tonnage])) DESC

[zeusspandex]

What you provided works beautifully, and i thank you.

I apologise for the amateurish mistakes but this is my first attempt at using Access and am i am completely self taught. I certainly have learned alot though and take your suggestions on board for next time.

Thank you once again.

[NeoPa]

Two threads sorted in one session :) Great news!

I fixed the bug in the GROUP BY line by the way. I expect you simply discovered it and fixed it yourself in your code. The post (#18) has now been updated.