Formulas in Quiries

[arinkydink]

I need some real help on this. I am trying to add up numbers that get updated, but other data in the table that corresponds to the numbers stay the same, but I need to see the old data and the new data. In diffrent rows. I am trying to create a query that adds up this data, that is diffrent in each row. Then I am trying to create a form that will show the sum of this data.

My fields are:

[SystemID]
[CompanyID]
[Year]
[Power Released] is what is going to change time after time. I will be creating a new line using the other 3 fields then include the new number. The formula in a query that I want to create will recognize that the 3 other fields are equal and just give me sum of the power released numbers.

My version of Access is 2002 I am using Office XP.

I sure could use the help.

Patrick

 

[Guest_imported]

Hey arinkydink,

I don't have solution to your question, but I have the same and similar problems. How does one punch in complex formulas (like ones in Excell) into Access? For example, if i need to sum up all of the months and the total is 3% of the sum divided by 4? Can Access take those formulas. If so, is that doen in a query or within tables and how?

Thanks

 

[jebry]

Hi Patrick!

Try this:

Select Sum([Power Released]), [SystemID], [CompanyID], [Year] From YourTable Group By [SystemID], [CompanyID],[Year]

hth
Jeff Bridgham
bridgham@purdue.edu

 

[arinkydink]

Jerby,

Thank you for your response.

When you say select Sum([Power Released]), [SystemID], [CompanyID], [Year] by table group [SystemID], [CompanyID],[Year]

My question is how exactly am I proceeding in this selection. Is this selection done in a query or in the tables design view?

If it is done in the query design view, then I must create a formula:

Something like this below?

PowerReleased (column)
PowerReleasedTran (table)
and leave the check mark unchecked.

Formula goes under the unmark check box:

=NZSum([Power Released]), [SystemID], [CompanyID], [Year]
AS PowerReleasedTotal (This is the new title)

?

I sure could use the help.

Patrick

 

[arinkydink]

BrankoG:

Jerby has solved my problem and two give you some information it is done in a query. If you right click your column in a query that you want to sum. Click on totals icon and you get a list of options, the first one is group by and next is sum etc.

I can't answer your question directly except for what I mention above.


Below I will post Jerby's and mine coversation it might help you out:

Hi Jerby,

Thank you very much, I tested it out a couple of times and everything works.

Thanks again.

Sincerely,

arinkydink




> Hi arinkydink>
>

Well, it isn't as simple as it was before. I had not realized that your
> query contained more fields than the four we have been working with. There
> is no way as far as I know to highlight non-touching columns in a query
> without highlighting all of the columns inbetween. When I was talking about
> selecting certain fields in my original reply, I meant to select them for
> inclusion in the query(thinking that we were building a query instead of
> working with an existing query). As for your next question, I would
> recommend that you do not store the sum at all since this can be a dynamic
> value and is easily recreated from the data when it is needed. So I would
> say leave the field out of the table altogether.
>
> Now, on with solving the problem:
>
> Create a new query, following the steps I have already listed, remembering
> that you do not need to select the columns, just include the fields
> indicated in the query. After the query is created, you will need to
> include
> the results in your current query. To do this, right click on the gray area
> above the design grid(where the tables are located, but not on a table) and
> choose Show Tables from the popup menu. Click on the queries tab and find
> the query you just created and select it and click the add button. Use the
> three fields you grouped by to join the query to the table and add the
> summed field to the query design grid. This should attach the proper total
> to each record.
>
> hth
>
> Jerby>
> P.S. If you need to limit the records you are looking at in the query you
> are creating and need to use fields other than the four we have been
> discussing, you can add those fields to the query as well and choose where
> in the total row for those fields. Now you can use them to limit the
> records
> without them showing up in the query itself.
>
>

>
>
> Hi Jerby,
>
> Thank for your quick response.
>
> First problem I am having with your direction, is I am having a problem
> highlighting all four rows. I am holding done the shift key and selecting
> the
> columns but since there is more then just these for columns in my query
> more
>
> then the four columns are being highlighted.
>
> What should I do?
>
> For the rest of your direction I see to be fine, but I don't now until I
> can
>
> appropriately select the columns.
>
> My next question is:
>
> I have created a column in the table were these for fields are located,
> this
>
> column is titled total power release. When I run my query I want to leave
> the
> power release field the way it is and to be able to see the details, but I
> want
> the sum details to show up in a new column called total power release (This
> is
> in the query).
>
> Can have this out come occur, if I follow your previous directions, after I
>
> figurer out how to highlight only the four fields that are required?
>
> My next question is:
>
> Can I have this sum appear in the table were these four fields are located?
>
> How do I do that?
>
>
> I sure could use the help.
>
> arinkydink
>
>
>
>

>
> > Hi arinkydink

> >
> > In the query design view select the four fields in question. On the
> toolbar
> > click the button to use aggregate functions(it looks like a summation
> sign
> > and is next to the drop down box with the word all in it). This will add
> a
> > line called Total to your design with Group By in each box. In the Total
> > box
> > under Power Released change Group By to Sum and run the query to see if
> > this
> > is what you are looking for.
> >
> > Jerby
> >
> >
> >
> >
> > Jerby,
> >
> > Thank you for your response.
> >
> > When you say select Sum([Power Released]), [SystemID], [CompanyID],
> [Year]
> > by
> > table group [SystemID], [CompanyID],[Year]
> >
> > My question is how exactly am I proceeding in this selection. Is this
> > selection
> > done in a query or in the tables design view?
> >
> > If it is done in the query design view, then I must create a formula:
> >
> > Something like this below?
> >
> > PowerReleased (column)
> > PowerReleasedTran (table)
> > and leave the check mark unchecked.
> >
> > Formula goes under the unmark check box:
> >
> > =NZSum([Power Released]), [SystemID], [CompanyID], [Year]
> > AS PowerReleasedTotal (This is the new title)
> >
> > ?
> >
> > I sure could use the help.
> >
> > arinkydink