Attempting to calculate 2 fields

[Guest]

I am trying to divide one field by another, in a table or query (would be
happy for it to work in either!!). The field I need the expression in is
called Federal Ratio.
I am trying to divide Federal Share by Total Cost. This is what I come up
with in expression builder [FEDSHARE] / [TOTALCOST] «Expr» [FEDERAL RATIO]

It is one of many I have tried, but can't come up with the right formula.
Any ideas would be appreciated.

Linda

 

[Allen Browne]

Use a query to do your calculations.

In query design view, type this into a fresh column in the Field row:
Federal Ratio: [FEDSHARE] / [TOTALCOST]

Note that this will error if TotalCost is zero, so you might want to use:
Federal Ratio: IIf([TotalCost]=0, 0, [FEDSHARE] / [TOTALCOST])

 

[Guest]

Allen, I have almost a similar question the problem I am having is
calculating and acummulating fileds and show in a chart linear, shoul I do a
cross tab and a pivot chart (I did reseach on tho\is group) but I am a casual
user to access can you help me?Tia

Use a query to do your calculations.

In query design view, type this into a fresh column in the Field row:
Federal Ratio: [FEDSHARE] / [TOTALCOST]

Note that this will error if TotalCost is zero, so you might want to use:
Federal Ratio: IIf([TotalCost]=0, 0, [FEDSHARE] / [TOTALCOST])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to divide one field by another, in a table or query (would be
happy for it to work in either!!). The field I need the expression in is
called Federal Ratio.
I am trying to divide Federal Share by Total Cost. This is what I come up
with in expression builder [FEDSHARE] / [TOTALCOST] «Expr» [FEDERAL RATIO]

It is one of many I have tried, but can't come up with the right formula.
Any ideas would be appreciated.

Linda

 

[Allen Browne]

Calculated fields in a query are easy enough.

Accumulating progressive totals in a query typically involves using a
subquery or a DSum() expression. DSum() is perhaps easier, but slower. To
create a subquery, you need to type a SQL statement into the Field row of
your query. Microsoft's introduction is:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

You don't need a crosstab unless you want to convert the entries in a field
into column headings.

 

[Guest]

Thank you I will it and see if I can do it, Cheers

 

[Guest]

Allan, I was looking at Norhtwind and the Sales by category report is the
same kind of report that I want to do, bu I dont know how to achieve that, I
think my table is wrong:
WEEK ID
WEEK
RAND
SIGN
SF
RAND
FAIL

so, do u think that is a right table? Tia

 

[Allen Browne]

Savanah, that's too broad a question to answer in these groups.

You may need to do some reading on database design.

 

[Guest]

Thanks for the tip, sorry I just kind of lost.

Savanah, that's too broad a question to answer in these groups.

You may need to do some reading on database design.

 

[Guest]

Thanks, Allen, that worked!!!

Linda

Use a query to do your calculations.

In query design view, type this into a fresh column in the Field row:
Federal Ratio: [FEDSHARE] / [TOTALCOST]

Note that this will error if TotalCost is zero, so you might want to use:
Federal Ratio: IIf([TotalCost]=0, 0, [FEDSHARE] / [TOTALCOST])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to divide one field by another, in a table or query (would be
happy for it to work in either!!). The field I need the expression in is
called Federal Ratio.
I am trying to divide Federal Share by Total Cost. This is what I come up
with in expression builder [FEDSHARE] / [TOTALCOST] «Expr» [FEDERAL RATIO]

It is one of many I have tried, but can't come up with the right formula.
Any ideas would be appreciated.

Linda

 

[Guest]

Allen, I have a similar problem: I created a query to try and calculate the
difference between a START TIME and an END TIME, however I cannot seem to
find the right formula nor can I display it in decimal format. Is there any
way to go about doing either?

Thanks.
--
Joe

Thanks, Allen, that worked!!!

Linda

Use a query to do your calculations.

In query design view, type this into a fresh column in the Field row:
Federal Ratio: [FEDSHARE] / [TOTALCOST]

Note that this will error if TotalCost is zero, so you might want to use:
Federal Ratio: IIf([TotalCost]=0, 0, [FEDSHARE] / [TOTALCOST])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to divide one field by another, in a table or query (would be
happy for it to work in either!!). The field I need the expression in is
called Federal Ratio.
I am trying to divide Federal Share by Total Cost. This is what I come up
with in expression builder [FEDSHARE] / [TOTALCOST] «Expr» [FEDERAL RATIO]

It is one of many I have tried, but can't come up with the right formula.
Any ideas would be appreciated.

Linda

 

[Jamie Collins]

I created a query to try and calculate the
difference between a START TIME and an END TIME, however I cannot seem to
find the right formula nor can I display it indecimalformat.

Take a look at DATEDIFF. It returns the number of time granules
specified (e.g. hours) as an integer value; if you are expecting a
decimal, try using a smaller granularity (e.g. minutes) and dividing
by the number of smaller time granules in the larger (e.g. 60 minutes
in an hour):

SELECT DATEDIFF('N', #2007-01-01 14:00:00#, #2007-01-01 15:35:00#) /
60

returns a value of type DOUBLE.

Alternatively:

SELECT (DATEDIFF('N', #2007-01-01 14:00:00#, #2007-01-01 15:35:00#) +
0.1 - 0.1)/ 60

returns a value of type DECIMAL.

Jamie.

--

 

[Guest]

Hi Jamie--

So where exactly would the "SELECT (DATEDIFF('N', #2007-01-01 14:00:00#,
#2007-01-01 15:35:00#) + 0.1 - 0.1)/ 60 go and what type of query does it
need to be for it to work? If by chance I use the query with a Total column,
what function should I use? (FYI: I am using the following fields: START
DATE, START TIME and FINISH TIME)

 

[Jamie Collins]

So where exactly would the "SELECT (DATEDIFF('N', #2007-01-01 14:00:00#,
#2007-01-01 15:35:00#) + 0.1 - 0.1)/ 60 go

Please post your existing query. TIA.

Jamie.

--

 

[Guest]

Field: WORKORDER
Table: Work Request
Total: Group By

Field: TECHNICIAN
Table: Work Request
Total: Group By

Field: START DATE
Table: Work Request
Total: Group By

Field: START TIME
Table: Work Request
Total: Group By

Field: FINISH TIME
Table: Work Request
Total: Group By