Sum Thread (1 Viewer)

[kingsgambit]

I have database where users can produce a quote.
Each new quote gets a quote number,
when users add a item to the quote, they also add a DHR (daily hire rate)
The table records the item and adds the quote and the DHR

I want to run a query so it shows the quote number and adds all the DHR's for that quote.
Can this be done in a query as an expression

 

[Junkee Brewster]

Hi there,

I'm assuming your tables and relationships are set up correctly/similarly....

If you're looking to display this data to a customer, use a form or report.
Add a calculated field to the form or report. (note * and # denote child/master fields, this setup would have 3 backend tables, customers, linked to quote numbers (thus custs can have multiple quotes) and then quotes linked to the DHR, so each quote can have mulitple DHR). Example FORM -

Main:
*[CustID]
Customer Name

Sub
*[CustID]
#[QuoteID], Quote Number

Sub of the Sub:
#[QuoteID] DHR
[QuoteID] DHR

In the footer of the "sub of the sub" form add a textbox, and type the following

=Sum([DHR])

(If printing a report, you would add the same calculated field in the totals footer)

Having the form set up (similar) to that will let you scroll through all the quotes made by that specific customer. This method will NOT write the sum of DHR to table.

 

[Junkee Brewster]

My apologies - this is the second time this has happened to me today - I've gone to post, gotten an error message, had to log back in and noticed that my post has gone to the wrong thread. Very sorry!!!!! Please ignore this reply for this thread

 

[Junkee Brewster]

OK, my computer has gremlins. Yes, this message is in the right place, to the right user, but before it was displaying a different message (something to do with Decoding Access) under this title of "Sum Thread". Twilight Zone! Soz

 

[kingsgambit]

I wanted to be able to put it into a query if I enter a single contract number, it will sum the DHR.

SELECT [daily revenue].[Contract ID], Sum([daily revenue].[DHR]) AS SumOfDHR
FROM [daily revenue]
GROUP BY [daily revenue].[Contract ID]
HAVING ((([daily revenue].[Contract ID])=[enter]));

I wanted to be able run a query so it grouped all contract numbers and show the sum of the DHR for each grouped