Column total in query help? 1

[sparkyout]

I have qryOrderDetails with 9 fields. I would like to total the columns for two fields: Price and Amount.

How can I add the calculated field into my query?

Do I need to create a separate query to do this?

 

[lespaul]

SELECT Price + Amount As Total From tblName

you'll have to post your SQL for more specific help

Leslie

 

[sparkyout]

Here's the SQL:

User's will answer one criteria: Enter Sales Order no. This can yield one or more records. Right now, I just have calculated fields on the forms, however it would be easier to have everything in the query to simplify reporting. I appreciate any help!
-----------------------------------------------------------
Here's the SQL:

SELECT sotrnew.FSONO, sotrnew.SEQNO, somnew.FORDDATE, sotrnew.FCUSTNO, somnew.FCOMPANY, somnew.FPONO, sotrnew.FDUEDATE, sotrnew.FITEMNO, sotrnew.FORDQTY, sotrnew.FSHIPQTY, sotrnew.FPRICE, sotrnew.FAMOUNT
FROM sotrnew INNER JOIN somnew ON sotrnew.FSONO = somnew.FSONO
WHERE (((sotrnew.FSONO)=[Enter Sales Order Number only (Ex: 123456)]))
ORDER BY sotrnew.FSONO, sotrnew.SEQNO, somnew.FORDDATE, sotrnew.FCUSTNO;

 

[lespaul]

SELECT sotrnew.FSONO, sotrnew.SEQNO, somnew.FORDDATE, sotrnew.FCUSTNO, somnew.FCOMPANY, somnew.FPONO, sotrnew.FDUEDATE, sotrnew.FITEMNO, sotrnew.FORDQTY, sotrnew.FSHIPQTY, SUM(sotrnew.FPRICE), SUM(sotrnew.FAMOUNT)
FROM sotrnew INNER JOIN somnew ON sotrnew.FSONO = somnew.FSONO
WHERE (((sotrnew.FSONO)=[Enter Sales Order Number only (Ex: 123456)]))
GROUP BY sotrnew.FSONO, sotrnew.SEQNO, somnew.FORDDATE, sotrnew.FCUSTNO, somnew.FCOMPANY, somnew.FPONO, sotrnew.FDUEDATE, sotrnew.FITEMNO, sotrnew.FORDQTY, sotrnew.FSHIPQTY
ORDER BY sotrnew.FSONO, sotrnew.SEQNO, somnew.FORDDATE, sotrnew.FCUSTNO;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[sparkyout]

Leslie,

Unfortunately this only gave the row totals. I had an idea. I created another query (with same critera) that included FSONO, FOrdQty and FAmount. I grouped by FSONO and did the sum for each field. That gave me the total for FOrdQty and FAmount when the sales order# (FSONO) is typed in.

Now, when I combine that w/the other query the problem is that users have to answer the same criteria question twice. Following is the code. Any ideas on how to have just one criteria question? Thanks, Kim
------------------------------------

SELECT sotrnew.FSONO, sotrnew.SEQNO, somnew.FORDDATE, sotrnew.FCUSTNO, somnew.FCOMPANY, somnew.FPONO, sotrnew.FDUEDATE, sotrnew.FITEMNO, sotrnew.FSHIPQTY, sotrnew.FPRICE, qryFieldTotals.TotalOrderAmount, qryFieldTotals.TotalSetsOrdered
FROM qryFieldTotals, sotrnew INNER JOIN somnew ON sotrnew.FSONO = somnew.FSONO
WHERE (((sotrnew.FSONO)=[Enter Sales Order Number only (Ex: 123456)]))
ORDER BY sotrnew.FSONO, sotrnew.SEQNO, somnew.FORDDATE, sotrnew.FCUSTNO;

 

[lespaul]

I think you need to change your FROM clause to:

FROM sotrnew INNER JOIN qryFieldTotals on qryFieldTotals.FSONO = sotrnew.FSONO
INNER JOIN somnew ON sotrnew.FSONO = somnew.FSONO

and remove the criteria from either this query or qryFieldTotals.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[sparkyout]

Thanks! All I had to do was remove the second criteria and not edit the inner join. Kim

 

[sparkyout]

Leslie, You were such a big help. How are you with forms and subforms?

 

[lespaul]

not my thing!! I really don't work in Access at all anymore, so I just help with queries and table design. There is a Forms Forum though, Forum702, I'm sure you'll find someone just as helpful there!

Good luck,

Leslie