P: 16

Hi all. I'm new here and fairly new to building databases. Learned a lot in designing this one, but I am stuck on this one last expression. I'm trying to create a calculated control. The form is named "Complete_Worksheet". It displays the following fields:
ID
Standard
Outcome Measure
Numerator/Denominator
CalcValue
What I am trying to do is divide the value for the "CalcValue" field in a record by the value for the same field in the next record. The expression I've been working with is:
=DLookUp("[CalcValue]",,"Complete_Worksheet","[ID]="&"[ID]1"/"[ID]=")
The ID field is also the key for the source query.
When I view the form, the calculated field shows "#Error" reagardless of the values for the CalcValue field.
When I run the expression on the Visual Basic "Immediate" window, I get the error message "Compile Error: Expected: line number or label or statement or end of statement"
I appreciate any help you can give in getting this working.
 
Share this Question
Expert Mod 10K+
P: 14,534

Try this ... 

=DLookUp("[CalcValue]","Complete_Worksheet","[ID]=" & [ID]1) / [CalcValue]

I assume Complete_Worksheet is the table name.
Mary
 
P: 16

That is the form's name.
I tried your suggested script and received the same error message in the Immediate window.
Still doesn't work on the form either.
  Expert Mod 10K+
P: 14,534

That is the form's name.
I tried your suggested script and received the same error message in the Immediate window.
Still doesn't work on the form either.
That's because you need the table or query name for the DLookup. It seaches the table or query behind the form not the form itself.
Mary
 
P: 16

OK. Changed "Complete_Worksheet" to "[qryComplete_Worksheet]", but still get the same results.
  Expert Mod 10K+
P: 14,534

OK. Changed "Complete_Worksheet" to "[qryComplete_Worksheet]", but still get the same results.
Where are you putting this line of code?
Is CalcValue the name of the field in the table?
Mary
 
P: 16

Where are you putting this line of code?
Is CalcValue the name of the field in the table?
Mary
CalcValue is the field in the source query and the table that it draws from. I changed the expression so that it references fields in the source query. Am I missing something major here?
  Expert Mod 10K+
P: 14,534

CalcValue is the field in the source query and the table that it draws from. I changed the expression so that it references fields in the source query. Am I missing something major here?
Are ID and CalcValue in the same table. If so that is the table name you need.
If not is the query they are both in a saved query. In which case the query name is what is needed in the DLookup.
Think about it logically.
You are looking up the value of CalcValue in some query or table where the ID field in the same query or table is equal to the ID on the current form 1.
Mary
 
P: 16

Sorry, I mistyped earlier. CalcValue is calculated on the query from data on a table using the IIf expression. The form displays data from the query.
  Expert Mod 10K+
P: 14,534

Sorry, I mistyped earlier. CalcValue is calculated on the query from data on a table using the IIf expression. The form displays data from the query.
If you are not storing CalcValue in any field then this is a lot more complicated.
Could you explain what you are trying to do and why. Why does the calculated value need to be taken from the previous record and what is the calculation based on (i.e. what fields and what calculation on those fields).
Mary
 
P: 16

Certainly. The form is a display of statistical outcome measures used for accreditation purposes at a jail. These outcome measures consist of several different categories, listed as numerators or denominators in the final statistical representation. CalcValue is an automatically calculated value of totals and averages of various measures. The IIf expression was used to place these different functioning measures into the CalcValue field of a query. The calculated control I am attempting to create is to be the product of the numerator CalcValue value divided by the denominator CalcValue value of each measure.
  Expert Mod 10K+
P: 14,534

Certainly. The form is a display of statistical outcome measures used for accreditation purposes at a jail. These outcome measures consist of several different categories, listed as numerators or denominators in the final statistical representation. CalcValue is an automatically calculated value of totals and averages of various measures. The IIf expression was used to place these different functioning measures into the CalcValue field of a query. The calculated control I am attempting to create is to be the product of the numerator CalcValue value divided by the denominator CalcValue value of each measure.
It would help if you stored the denominator each time a record is created. Maybe set up a table to hold this one value. You could create an after update event on the CalcValue field to replace this record in the table each time. Alternatively if the Denominator is always ascending you could just add a record each time and get the max value. It would make it very easy to calculate against it as we wouldn't have to refer to the previous record.
Mary
 
P: 16

I think I follow what you are saying. Unfortunately, the numerator and denominator must be displayed on the form in the same column, meaning the CalcValue field on a running form.
 
P: 16

I think I follow what you are saying. Unfortunately, the numerator and denominator must be displayed on the form in the same column, meaning the CalcValue field on a running form.
After rereading your last post, I think I see a little confusion. Each numerator and denominator is different and changes daily based on updated statistics entered into a data table. There are 223 different statistical representations that I need to calculate numerators, denominators, and completed statistics for. Using queries and IIf expressions, I have been able to calculate numerators and denominators and place them into the CalcValue field to be displayed with their corresponding record. It is calculating the values for the numerator divided by the denominator that I am not able to figure out.
  Expert Mod 10K+
P: 14,534

After rereading your last post, I think I see a little confusion. Each numerator and denominator is different and changes daily based on updated statistics entered into a data table. There are 223 different statistical representations that I need to calculate numerators, denominators, and completed statistics for. Using queries and IIf expressions, I have been able to calculate numerators and denominators and place them into the CalcValue field to be displayed with their corresponding record. It is calculating the values for the numerator divided by the denominator that I am not able to figure out.
The confusion arose I think because you were trying to get a denominator from the previous record (previous ID) to calculate against the current value in CalcValue.
I understand that the issue is a complicated statistical one. However, for the purposes of this question can you restrict your explanation to things like field in current record / field in previous record and explain how and when the record changes, how the value changes in the current and/or previous calculated field, etc.
Mary
  Expert Mod 15k+
P: 31,494

MrHighSpeed.
Please understand that working remotely like this on other people's problems is a lot harder to get right than simply working on your own database.
We have two main problems :
1. We have to build up a picture of the whole problem in our heads without reference to any testing except at second hand via the requestor.
2. We have to rely on the accuracy and completeness of information passed to us via posts.
It doesn't take a great deal of imagination to realise, then, how crucial is the information passed to us.
 
P: 16

I understand and am very grateful for the insight and perspectives here. Maybe it will help if I explain a little background on the application here.
The function this database will serve was originally done by an Excel spreadsheet, which allowed me to enter the formula for calculation into each cell where needed. The problem with that is these statistics are compiled from about a dozen different department heads within the Department of Corrections. This would require me to spend a significant portion of every day entering and reentering data into the spreadsheet. Greater chance of typos and such as well. This database will allow the department heads to each enter their relevant stats while also populating a data table for the functions I need. The form I'm stuck on right now is set up to appear columnar in a running page format so that it looks similar to the spreadsheet format preferred by the American Correctional Association. They will be the ones auditing the data.
I have a table, "tblComplete_Worksheet" that contains these fields:
ID
Standard
Outcome Measure
Numerator/Denominator
I have another table, "tblOutcomes" that is the data table, populated by the department heads. It contains these fields:
ID
Outcomes ID (related to the ID field on the other table)
Value
Through a series of simple queries, I limit the Value data by date range, average or total the appropriate sets according to Outcomes ID (some Outcomes ID sets have to be averaged and some totalled), and combine them back into the same field, listed according to Outcomes ID. The query that finishes that job is called "qryDataSelect". It presents these fields:
ID (related to the above mentioned ID and Outcomes ID fields)
CalcValue
I tie it all together in a final query, called "qryComplete_Worksheet", which contains the fields:
ID (again, the same IDs as above)
Standard (from tblComplete_Worksheet)
Outcome Measure (from tblComplete_Worksheet)
Numerator/Denominator (from tblComplete_Worksheet)
CalcValue (from qryDataSelect)
I am presenting this on the form that I'm working on. Each of the above fields is showing up and functioning exactly as needed. There is one more field that has to be shown, the CalcValue from the Numerator record divided by the CalcValue from the Denominator record. This is where I am stuck.
I hope I made things clearer than the mud in my head. If I can provide any further information, please let me know.
  Expert Mod 15k+
P: 31,494

That looks good, but the SQL you currently have for the query would certainly help :).
  Expert Mod 15k+
P: 31,494

Is there a field (key) that links the records of the Numerator and Denominator together?
SQL is really atrocious at dealing with records in sequence generally, but it's very good at GROUPing.
 
P: 16

Are you referring to the SQL for the query I'm using to feed the form? Let me know and I'll post it ASAP.
Unfortunately, Numerator/Denominator is a single field. It's just that some records are numerators in the final calculation and some are denominators. The Numerator/Denominator field is basically a text description of where the value(s) used in the CalcValue field came from.
  Expert Mod 15k+
P: 31,494

Are you referring to the SQL for the query I'm using to feed the form? Let me know and I'll post it ASAP.
Yes.
Unfortunately, Numerator/Denominator is a single field. It's just that some records are numerators in the final calculation and some are denominators. The Numerator/Denominator field is basically a text description of where the value(s) used in the CalcValue field came from.
But if you have two separate records with the information on, is there something unique to those two records I can use to identify them?
 
P: 16

Yes.
But if you have two separate records with the information on, is there something unique to those two records I can use to identify them?
Here's the SQL for the "qryComplete_Worksheet":
SELECT Complete_Worksheet.ID, Complete_Worksheet.Standard, Complete_Worksheet.[Outcome ID], Complete_Worksheet.[Outcome Measure], Complete_Worksheet.[Numerator/Denominator], qryDataSelect.CalcValue
FROM qryDataSelect RIGHT JOIN Complete_Worksheet ON qryDataSelect.[Outcome ID] = Complete_Worksheet.[Outcome ID];
Hopefully that's helpful.
The Numerator/Denominator is identified by the value in the Outcome Measure field. Specifically, all denominator records have the text "divided by" in the Outcome Measures field. Does that help? Does it make sense?
  Expert Mod 15k+
P: 31,494

The Numerator/Denominator is identified by the value in the Outcome Measure field. Specifically, all denominator records have the text "divided by" in the Outcome Measures field. Does that help? Does it make sense?
It makes sense but I'm after something that connects a numerator record to the matching denominator record. Proximity is meaningless (almost completely) within an RDBMS database. SQL cannot use it.
 
P: 16

It makes sense but I'm after something that connects a numerator record to the matching denominator record. Proximity is meaningless (almost completely) within an RDBMS database. SQL cannot use it.
The only connection is that each denominator's ID number follows its numerator ID sequentially. I could create another field that might allow a more concrete relation. Any suggestions?
  Expert Mod 15k+
P: 31,494

The only connection is that each denominator's ID number follows its numerator ID sequentially. I could create another field that might allow a more concrete relation. Any suggestions?
Only 'Yes  do it'.
If you want to work with 'Next' and 'Previous' records in Access you have to run it through code. Not much fun when SQL can make life so much easier.
 
P: 16

Only 'Yes  do it'.
If you want to work with 'Next' and 'Previous' records in Access you have to run it through code. Not much fun when SQL can make life so much easier.
Any suggestions on what values should be used to differentiate one from the other? I'm just trying to formulate how that should look in my head.
  Expert Mod 15k+
P: 31,494

I can only say to think of something that would describe each of the two records but not any other.
I don't really know your data well enough (where it comes from etc) to give a detailed answer. Sorry.
 
P: 16

I can only say to think of something that would describe each of the two records but not any other.
I don't really know your data well enough (where it comes from etc) to give a detailed answer. Sorry.
Are you thinking something like a field that designates numerator records as "N" and denominator records as "D" or a unique identifier for each one?
  Expert Mod 15k+
P: 31,494

Are you thinking something like a field that designates numerator records as "N" and denominator records as "D" or a unique identifier for each one?
Ahh  Wrong end of the stick.
Think  ConnectID NumDen Type

A 346 Num

A 240 Den

B 762 Num

B 1,040 Den

etc...
where ConnectID can be numeric or string but acts to connect the two related records together.
Does that make it clearer?
 
P: 16

Ahh  Wrong end of the stick.
Think  ConnectID NumDen Type

A 346 Num

A 240 Den

B 762 Num

B 1,040 Den

etc...
where ConnectID can be numeric or string but acts to connect the two related records together.
Does that make it clearer?
I think I got what you're saying. So essentially, all things being equal, I should come up with half as many pairs of ConnectID entries as I have records?
I'll get to work on that. Got a few other ideas to try too. Thanks.
  Expert Mod 15k+
P: 31,494

You've got it MrHS.
That way we can GROUP on that field and have a Numerator and Denominator on the same result line. From there it is easy to do the arithmetic as all source items are available.
 
P: 16

Looks like I got it. I was overthinking the process by trying to use DLookUp. Instead, I created another query to isolate the Numerator records and add 1 to their ID numbers. I then Included the CalcValue field from that query in the qryComplete_Worksheet and created a calculated control on my form to divide the Numerator CalcValue by the Denominator CalcValue. Works real slick.
Thanks everyone for all your help!
    Question stats  viewed: 2368
 replies: 31
 date asked: Dec 11 '06
