sum and show parts of sum (1 Viewer)

aml5171

Registered User.
Local time
Yesterday, 19:06
Joined
Feb 17, 2011
Messages
41
I was wondering if it is possible to do a sum query but show the parts of the sum.

For example, my database tracks insurance policies on oil rigs, and a single rig can have multiple policies on it. Currently I am summing all the open policies on a particular rig by typing in a rig name and then receive that total number. What I would like to appear is each policy on that rig, then the total.

Anybody know if or how that would be done? Thanks in advance for the help.

I can attach the database if necessary.
 
Where are you trying to show this? Report, form or query? Can you post the SQL of the query you are using now that is not giving you the desired results?
 
For now I just would like to have this show in a query. I have not started creating forms or reports into my database yet. Do they provide any significant advantage to the user besides an aesthetic one?

It is not that the query I am currently using is not providing the "desired" results, I would just like to capture more.

This is my SQL:

SELECT qryMain.txtVesselName, Sum(qryMain.Exposure) AS SumOfExposure
FROM qryMain
GROUP BY qryMain.txtVesselName
HAVING (((qryMain.txtVesselName) Like [Please enter vessel name] & "*"));

In the above query what is happening is, the user types in the name of a particular "vessel", which represents a single oil rig that we have multiple insurance policies on. The query sums each policy for that vessel and gives me the total. What we would like to be able to see in the end result is the individual policies for that vessel and then the sum of those policies' exposures.

My apologies if this makes little sense or is hard to understand if more information is needed please let me know and I will gladly provide and explain it. Also if a zip of my database would be helpful I can do that as well.

Any suggestions or thoughts you or anyone has is greatly appreciated. Thank you in advance for your interest and help.
 
Well, right off the bat that is what forms are for... entering data. That should not be done via query or table level. Then on the form you can show totals based on values entered. I would not even try to do that in a query...
 
I'm sorry if I explained it unclearly, but no values are being entered via the query. The way I have the database set up is with a master excel sheet that contains all my companies data that is regularly updated, which I then import from excel as "sheet1" into the database and run append queries based on that structure.

The only thing being entered into the query is criteria such as vessel name but that value "vessel name" is already in the database.
 
Oh okay... What are the names and field names of the tables you are appending to?
 
I do not even know where to begin. I have 9 append queries. attached is my database if you would like to take a look at it. I figured showing the parts of the sum was an easy expression or adjustment to my query.
 

Attachments

Okay, what version of Access are you using? I don't want to modify in the wrong version as you may not be able to open it when I am done.
 
it is the older version of access, i think its 2003-07 i guess. sorry i am no longer at my work computer so can't check to remember the years
 
Okay, going to go to my Access 2003 machine to make changes, I should be safe...
 
In this thread you have been calling them rigs but I see no field with that name... What field name am I grouping on?
 
sorry i am calling them vesselname in the database
 
In Access 2007 & 2010 you have the option of adding a totals row after you run a query, but I do not think that this was available in Access 2003 (but I may be wrong). I no longer have Access 2003, so I cannot verify it.

The better approach would be to display the data from the query in a form or report and add a control for the total you want. For the form, you will need a main form based on the vessel table (probably) and a subform for the exposure details. You would then add the control to the main form that sums the appropriate field of the records in the subform.
 

Users who are viewing this thread

Back
Top Bottom