Query to Sum price doesn`t work:(

[Smaua]

ok..i`m trying this and for some reason it`s not calculating the sum. it just shows the values found. i have 3 tables bookings_table, party_table and session_table. the session_code is a field in the session_code but exists also as a foreign key in the Bookings_table. any idea??

(SELECT Sum([Session_Cost]) FROM [Session_Table] WHERE [Bookings_Table].[Session_Code] = [Session_Table].[Session_Code] AND [Bookings_Table].[Paid?] LIKE '0')

thanks a lot!!

 

[Golom]

I'm suprised that it runs at all.

Try this

SELECT Sum([Session_Cost]) 

FROM   [Session_Table] S INNER JOIN [Bookings_Table] B
       ON S.[Session_Code] = B.[Session_Code]

WHERE  B.[Paid?] = '0'

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Smaua]

thanks for answering so quickly..however it still doesn`t work. says it`s too complicated to be evaluated any other ideas?

 

[dhookom]

Is [Paid?] a text field? If not, remove any quotes from around it so it correctly compares a number to a number.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Smaua]

yess!! it works now thanks a lot!

another question tho..when it shows the calculated price, it shows the same total on 3 lines for example if there were 3 records counted. any way to only make it show it on one?

 

[Golom]

To see the SUM for each Session_Code

SELECT   S.[Session_Code], Sum([Session_Cost]) 

FROM     [Session_Table] S INNER JOIN [Bookings_Table] B
         ON S.[Session_Code] = B.[Session_Code]

WHERE    B.[Paid?] = 0

GROUP BY S.[Session_Code]

To see one line for the result

SELECT   TOP 1 Sum([Session_Cost]) 

FROM     [Session_Table] S INNER JOIN [Bookings_Table] B
         ON S.[Session_Code] = B.[Session_Code]

WHERE    B.[Paid?] = 0

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Smaua]

thank you for you reply again Golom, however this just adds up all the rows. The total on each row is correct. It just repeats itself. What i wanted is to see only one row with this total, not add the rows together.

 

[Golom]

Can you provide an example of your data and expected result?

I don't understand your requirement

"... not add the rows together ..."

The SUM aggregate function does exactly that ... it adds the values from multiple rows together.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Smaua]

so..let`s say there are 4 bookings
booking 1 - cost
booking 2 - cost2
booking 3 - cost3
booking 4 - cost4

the sum function does (cost+cost2+cost3+cost4)=answer. when i run the query i get a table like:

answer
answer
answer
answer

the same answer..on 4 rows..i assume because it`s the number of records i have.instead of showing just answer in once cell. i just want one answer. so that if i count many records it doesn`t just show loads of rows with the same thing.

don`t know if this has anything to do with what i`m asking..but is there any way to store the result of a query in this case "answer" in a variable or something? so that i can just make a text box to display that result? it would be very useful for other stuff i`m working on at the moment as well.

 

[Smaua]

oh and when i used the TOP 1 Sum it just added all the "answer"'s on the rows together..

 

[Golom]

Most peculiar. This

SELECT   Sum([Session_Cost]) 

FROM     [Session_Table] S INNER JOIN [Bookings_Table] B
         ON S.[Session_Code] = B.[Session_Code]

WHERE    B.[Paid?] = 0

should produce only 1 row. Does it produce more than that?

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Smaua]

hmm yep it does..at least for me. i`ve uploaded a screenshot here:

http://www.photohosting.info/uploads/13a3ebdcea.jpg

 

[Smaua]

i`ve also tried other queries and they still show many rows, some even empty but still there. any way around this? because i will need to create reports using these values and they will just repeat

 

[dhookom]

You are displaying subdatasheets which a large percentage of "seasoned" Access developers avoid at all costs.

It isn't clear what SQL statement you used where.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Smaua]

i used this code..
SELECT Sum([Session_Cost])

FROM [Session_Table] S INNER JOIN [Bookings_Table] B
ON S.[Session_Code] = B.[Session_Code]

WHERE B.[Paid?] = 0

and..is seasoned not familiar with?
it`s a subquery because that`s what i`m using it for. if i try and run it normally it still gives many rows.

 

[dhookom]

Seasoned" = experienced. I would recommend setting all of your subdatasheet properties to none. Clearly, this is causing the issue in your form display.

Where did you use the "code" you posted?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]