Sum of a query 1

[Yardyy]

Hi, How would i go about getting the sum of a field in a query in a report?

i have the following which is :

=SUM([query_name!field_Name])

but does not work !!

I have several queries which i need to sum up, just not sure how to do the sum part in the report.

any help most appreciated.

Many Thanks
Yurov Ardyy

 

[dhookom]

Your query must be included in the Record Source of the report. THen you could use a text box in a Group or Report Header or Footer section with a control source like:
=Sum(Field_Name)
A report can have only one record source. You can use subreports which each have their own record source.

If you can't figure this out, come back with more information about your requirements.

Duane
Hook'D on Access
MS Access MVP

 

[PWise]

how about

=Dsum("FieldName","QueryName")

 

[Yardyy]

Thanks pwise that worked a treat, just what i wanted.

sorry if my explanation was not 100%, but you hit the nail on the head.

dhookom, thanks to you also, i actually had that bit of code, just wasnt sure how much detail that i needed to put in the post..

Many Thanks
Yurov Ardyy

 

[dhookom]

I generally would question whether a number of DSum() expressions is the optimum solution. However, if it works well for you, use it.

Duane
Hook'D on Access
MS Access MVP

 

[Yardyy]

I have 13 queries in total that need summing to give me a total. The easiest way i though would have been to do 13 small queries that extract the data required and then sum then in one report.

This is the code that i use for the smaller queries:

SELECT vedepts.OLDCODE AS hPdeptname, Sum(vesales.RETAIL_NET) AS total_sales
FROM ((vedepts INNER JOIN vesales ON vedepts.DEPTID = vesales.DEPTID) INNER JOIN vetrans ON vesales.TRANSID = vetrans.TRANSID) INNER JOIN VEBRANCHES ON vetrans.BRANCHID = VEBRANCHES.BRANCHID
WHERE (((vetrans.DATETIME)>Date()-30) AND

((vedepts.OLDCODE) Like "013" Or (vedepts.OLDCODE)="015" Or (vedepts.OLDCODE)="016"))

GROUP BY vedepts.OLDCODE, VEBRANCHES.BRANCHID
HAVING (((VEBRANCHES.BRANCHID)=1));


the problem was that i could not sum the 013, 015, and the 016 directly in access as i dont know how to.
so i thought that i if i do it in small stages i may be able to complete it.

i have set of 12 other variants like 013, 015, and 016 above that need to be grouped then summed. But i cannot do it, because i am pretty new to access and SQL.



Many Thanks
Yurov Ardyy

 

[dhookom]

Any time I see a list of different unique values in the WHERE clause ie: 013, 015, and 016 I would expect this relationship to be described/stored in tables. Perhaps you should store GROUPS of OLDCODEs.

Duane
Hook'D on Access
MS Access MVP

 

[Yardyy]

I am not sure how I would do this. What benefit would that provide ?

Do you know of any good resources on the web for learning SQL ?

Many Thanks
Yurov Ardyy

 

[dhookom]

OLDCODE) Like "013" Or (vedepts.OLDCODE)="015" Or (vedepts.OLDCODE)="016"))

To me, this might a problem. What happens when you want to include code 018 or remove 013? If this will [red]never[/red] happen then it might not be an issue. Typically, I would have some value stored in a table that would suggest a relationship that grouped 013, 015, and 016 together. When the relationship changes, I would edit the data and not a query.

Check here Data belongs in your tables -- not in your code

Duane
Hook'D on Access
MS Access MVP

 

[Yardyy]

I see what you mean, looking back the queries that i am using are looking back 30 days for the specific data

>Date()-30

this restricts me in that i have to manually change the above 30 figure in all the queries if i need to change the days. How could i simplify this so that one one change is made in one place.

Many Thanks
Yurov Ardyy

 

[dhookom]

You could put the 30 in a table or build the SQL dyanamically with some code.

Duane
Hook'D on Access
MS Access MVP

 

[Yardyy]

i just tried to put the 30 in a table, but not sure how to refernce it from the query.. i tried .

>Date()-(

,[field])

that never worked.

also tried it without the brackets.

Many Thanks
Yurov Ardyy

 

[dhookom]

You can't reference a field from TABLEX unless TABLEX is in the FROM clause or from a domain aggregate function or subquery or function.

Duane
Hook'D on Access
MS Access MVP

 

[Yardyy]

wold it be possible to have a table with only two fields then, fromdate and todate.

what function would you use then to reference those in the query.

not sure if that makes sense.

Many Thanks
Yurov Ardyy

 

[dhookom]

To reference these in a query, you add the table to the query. If the table has just one record, it won't bother the results other than the query won't be updateable. This shouldn't be a problem with a report.

If the table with the two date fields has more than one record, the number of records in your report's records source will multiply.

Again, I would probably have a field in table vedepts that can store something that ties 013, 015, and 016 together.

Duane
Hook'D on Access
MS Access MVP

 

[Yardyy]

Thats great, i will work some examples out and get back to you asap. thanks for your help again. much appreciated.

Many Thanks
Yurov Ardyy