By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,473 Members | 3,234 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,473 IT Pros & Developers. It's quick & easy.

Using DLookUp to divide a field in one record by the same field in the next

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.
Dec 11 '06 #1
Share this Question
Share on Google+
31 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. =DLookUp("[CalcValue]","Complete_Worksheet","[ID]=" &  [ID]-1) / [CalcValue]
  3.  
I assume Complete_Worksheet is the table name.

Mary
Dec 11 '06 #2

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.
Dec 11 '06 #3

MMcCarthy
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
Dec 11 '06 #4

P: 16
OK. Changed "Complete_Worksheet" to "[qryComplete_Worksheet]", but still get the same results.
Dec 11 '06 #5

MMcCarthy
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
Dec 11 '06 #6

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?
Dec 11 '06 #7

MMcCarthy
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
Dec 11 '06 #8

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.
Dec 11 '06 #9

MMcCarthy
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
Dec 11 '06 #10

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.
Dec 11 '06 #11

MMcCarthy
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
Dec 11 '06 #12

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.
Dec 11 '06 #13

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.
Dec 11 '06 #14

MMcCarthy
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
Dec 11 '06 #15

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 12 '06 #16

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.
Dec 12 '06 #17

NeoPa
Expert Mod 15k+
P: 31,186
That looks good, but the SQL you currently have for the query would certainly help :).
Dec 12 '06 #18

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 12 '06 #19

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.
Dec 12 '06 #20

NeoPa
Expert Mod 15k+
P: 31,186
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?
Dec 12 '06 #21

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?
Dec 12 '06 #22

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 12 '06 #23

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?
Dec 12 '06 #24

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 13 '06 #25

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.
Dec 13 '06 #26

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 14 '06 #27

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?
Dec 14 '06 #28

NeoPa
Expert Mod 15k+
P: 31,186
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
Expand|Select|Wrap|Line Numbers
  1. ConnectID   NumDen   Type
  2. A                   346        Num
  3. A                   240        Den
  4. B                   762        Num
  5. B                 1,040        Den
  6. etc...
where ConnectID can be numeric or string but acts to connect the two related records together.
Does that make it clearer?
Dec 14 '06 #29

P: 16
Ahh - Wrong end of the stick.

Think
Expand|Select|Wrap|Line Numbers
  1. ConnectID   NumDen   Type
  2. A                   346        Num
  3. A                   240        Den
  4. B                   762        Num
  5. B                 1,040        Den
  6. 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.
Dec 14 '06 #30

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 14 '06 #31

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!
Dec 14 '06 #32

Post your reply

Sign in to post your reply or Sign up for a free account.