Web Development·12,146 discussions

Adding in Access97

Locked

I have an Access Table that I can query by beginning and ending date using SQL statements but I want to add a column of the results and perform a calculation on it eg. get the total of one column and find what percentage that total is of the total of another column (or something like that, I’ll have to ask the engineers what exactly they want) but they want to perform calcs on the totals of those columns.

Topic

Adding in Access97

In reply to Adding in Access97

Step one, to get your total in one column, sum it. It will sum it by the date, so create a second query based on the first one, but don’t use the date range, just the nember and sum the number – you now have a total for the column.

To perform more math on it:
Use the builder to select the field or qry you want and the imple math symbols to do the math for you. A lot can be done with parenthesis in one qry.

Hope this helps.

Adding in Access97

In reply to Adding in Access97

The question was auto-closed by TechRepublic

Adding in Access97

In reply to Adding in Access97

If you have a form that provides viewing of this table, you could use the DSum function to calculate the totals you need.

For example:
Dim lngTotal1 as Long
Dim lngTotal2 as Long
Dim dblPercent as Double

lngTotal1 = DSum(“[Col1]”,”[TableName]”,”[Date] = #” & me.txtSelectedDate & “#”)

lngTotal2 = DSum(“[Col2]”,”[TableName]”,”[Date] = #” & me.txtSelectedDate & “#”)

dblPercent = 100 * lngTotal1/lngTotal2

The third part of the DSum calculation is the where clause. In this exampleI’ve assumed that the column called [Date] must equal the date in the current form (me) object called txtSelectedDate.

Adding in Access97

In reply to Adding in Access97

The question was auto-closed by TechRepublic

Adding in Access97

In reply to Adding in Access97

This question was auto closed due to inactivity

[Author]

Replies