Formula inputs from multiple records

[dkern1]

I am interested in making a formula that takes input variables from the same field in three consecutive records then puts the results in a different table, drops to the next record and repeats. Each three records are related by a field that has the same value. The order of value 1 is random but value 2 always correctly corresponds to A, B and C respectively.

The formula is A/(B+C)
I can't get this post to format correctly

(field 1) (field 2) (field 3)
Name value 1 value 2

Each column below should be fields 1,2 and 3 respectively
1 A 15
1 B 20
1 C 10
2 B 17
2 A 12
2 C 10
3 C 25
3 A 5
3 B 14

In this example I need the resulting table to read:

(field 1 is name) (field 2 is calculated value)

1 .5
2 .44
3 .128

I am new to access and don’t know if this is possible in query or must be written in SQL.

I will be pleased to explain what I am actually doing but wanted to avoid too much extra information in my initial comment. There are actually several million records from hospital cost reports.


Thanks

David

 

[KathyL]

“I am new to access and don’t know if this is possible in query or must be written in SQL.”

I’ve developed in Access a long time, and I believe you can pretty much do anything you want without ever touching SQL. However, one should understand that Access queries generate into SQL code; this is done by Access.

I think you might benefit from using a subform on a form, and having totals displayed on the primary form, which reference a summarized total from the subform.

 

[WIS]

I am interested in making a formula that takes input variables from the same field in three consecutive records then puts the results in a different table, drops to the next record and repeats. Each three records are related by a field that has the same value. The order of value 1 is random but value 2 always correctly corresponds to A, B and C respectively.

The formula is A/(B+C)
I can't get this post to format correctly

(field 1) (field 2) (field 3)
Name value 1 value 2

Each column below should be fields 1,2 and 3 respectively
1 A 15
1 B 20
1 C 10
2 B 17
2 A 12
2 C 10
3 C 25
3 A 5
3 B 14

In this example I need the resulting table to read:

(field 1 is name) (field 2 is calculated value)

1 .5
2 .44
3 .128

I am new to access and don’t know if this is possible in query or must be written in SQL.

I will be pleased to explain what I am actually doing but wanted to avoid too much extra information in my initial comment. There are actually several million records from hospital cost reports.
David

I think I get the gist of what your'e trying to do.

1. Create a qry to get records ordered as ABC, ABC etc, - pull flds into QBE grid and set fld 1 Ascending, fld2 Ascending.

2. You'll probably have to use VBA. Create a recordset from the qry in pt 1 and write a routine to do the calc and store in new tbl.