Help with this difficult report please- please ignore previous mai

[sudhir]

I would be very grateful for help to set up this difficult report

I have the following data in two fields a table

test result ( number- 2 decimals) date

5.6 01/01/2006
7.8 01/06/2006
10.9 01/10/2006
and so on..

the number of entries are variable and are not limited by any means.

I want to set a report to say

Go to the last test result and determine natural log of this number. call
it -A
Do the same for the test result immediately preceeding. Call it - B
Subtract B from A- call it - C

Get the difference between Last date and one immediately preceeding in
months ( number, upto two decimals )- Lets call it - D

calculate F ( it is actually the doubling time of what is being measured )=
D*0.693/C

Repeat this process until all values of date and test result are taken into
account.

is this possible?

any help will be greatly appreciated

thanks

sb





-
sb

 

[KARL DEWEY]

This seems to be an excelant job for a spreadsheet like Excel.

 

[Marshall Barton]

I would be very grateful for help to set up this difficult report

I have the following data in two fields a table

test result ( number- 2 decimals) date

5.6 01/01/2006
7.8 01/06/2006
10.9 01/10/2006
and so on..

the number of entries are variable and are not limited by any means.

I want to set a report to say

Go to the last test result and determine natural log of this number. call
it -A
Do the same for the test result immediately preceeding. Call it - B
Subtract B from A- call it - C

Get the difference between Last date and one immediately preceeding in
months ( number, upto two decimals )- Lets call it - D

calculate F ( it is actually the doubling time of what is being measured )=
D*0.693/C

Repeat this process until all values of date and test result are taken into
account.

You should be able to get the value from the previous record
by using a Domain Aggregate function.

SELECT TestResult, TestDate,
DMax("TestDate", "table",
"TestDate<" & Format(TestDate,"\#yyyy\/m\/d\#")
As PrevTestDate,
DLookup("TestResult", "table",
"TestDate=" & Format(PrevTestDate,"\#yyyy\/m\/d\#")
As PrevTestResult,
Log(TestResult) As A,
Log(PrevTestResult) As B,
A - B As C,
DateDiff("d", TestDate, PrevTestDate) / 30 As D,
D * 0.693 / C As F
FROM table

Try to run that kind of query and see how close you can get
(note: it will be slow for a largish table).

There is no good way to get a decimal months value from two
dates so I just divided the days difference by 30. You may
want to do some research to figure out just what you want
the difference in months to be,

Once you get the query producing satisfactoy reults, the
report should be trivial.

 

[sudhir]

Many many thanks, it does work. But i have noticed a problem!

The query is based on all values in the table. What I need is this program
to work only for a specified record. Eg- The table has 101 records with 404
test results. The formula u gave calculates values for all of them- which is
what it was asked to do!

I have a report based on a parameter query based on table, here i can see
the values for test results and date for an individual record. How can I get
this programme to work for a given record??

Almost like saying-

select the record with surname "Bloggs" from all the records in the table..
then your programme executes- to give me doubling times for Mr Bloggs..
Then I can choose MR Joe... and then your programme..continues..

Is this possible in a report??

Many thanks once again
( Am I sufficiently clear ?)
sb

 

[Marshall Barton]

The trick to what you want is to remove the parameter
criteria from the query so the report produces the
calculations for all tests.

You haven't mentioned a test ID kind of field, but I assume
there is a field that can be used to uniquely identify an
individual record (normally the table's primary key). You
will also have to modify the query to include this field.

Then, to filter the data to a specific record, you use an
unbound form with a text box (named txtTestID) for users to
enter the test id they want to see in the report.

A button on the form is then used to open the report and
apply the filer using code like:

If testID is a number type field:

DoCmd.OpenReport "name of report", acViewPreview, _
WhereCondition:= "testID=" + Me.txtTestID

Or, if testIDis a Text field:

DoCmd.OpenReport "name of report", acViewPreview, _
WhereCondition:= "testID=""" + Me.txtTestID + """"

 

[sudhir]

Many thanks once again
i will try this in a few days.

where do i learn these things? is this a part of visual basic or SQL ? can
u recommend a higher level book for these programming tricks??

i am very grateful for ur help

sb
--
sb

The trick to what you want is to remove the parameter
criteria from the query so the report produces the
calculations for all tests.

You haven't mentioned a test ID kind of field, but I assume
there is a field that can be used to uniquely identify an
individual record (normally the table's primary key). You
will also have to modify the query to include this field.

Then, to filter the data to a specific record, you use an
unbound form with a text box (named txtTestID) for users to
enter the test id they want to see in the report.

A button on the form is then used to open the report and
apply the filer using code like:

If testID is a number type field:

DoCmd.OpenReport "name of report", acViewPreview, _
WhereCondition:= "testID=" + Me.txtTestID

Or, if testIDis a Text field:

DoCmd.OpenReport "name of report", acViewPreview, _
WhereCondition:= "testID=""" + Me.txtTestID + """"
--
Marsh
MVP [MS Access]

Many many thanks, it does work. But i have noticed a problem!

The query is based on all values in the table. What I need is this program
to work only for a specified record. Eg- The table has 101 records with 404
test results. The formula u gave calculates values for all of them- which is
what it was asked to do!

I have a report based on a parameter query based on table, here i can see
the values for test results and date for an individual record. How can I get
this programme to work for a given record??

Almost like saying-

select the record with surname "Bloggs" from all the records in the table..
then your programme executes- to give me doubling times for Mr Bloggs..
Then I can choose MR Joe... and then your programme..continues..

 

[Marshall Barton]

Many thanks once again
i will try this in a few days.

where do i learn these things? is this a part of visual basic or SQL ? can
u recommend a higher level book for these programming tricks??

Sybex Press - "The Access xxxx Developer's Handbook"
where xxxx is your version of Access
is chock full of all kinds of techniques for a vast variety
of useful things.

 

[sudhir]

Many thanks Marshall

I deleted the parameter criteria from query. Created a blank form with a
text field for me to enter the IDno . Then placed a command button on this
form and in the properties window of this button, i chose "on click" event (
because i wanted to open the report based on the query upon clicking the
Enter button )and entered the text that u suggested.

on running this form i get messege : Compile error: Method or data member
not found.

even after on line search - i could not find how to "apply the filter using
the code " suitable for this task- i would be grateful if u could take me
throgh these steps in more detailed steps please??

many thanks for ur help

Sudhir

 

[Marshall Barton]

I deleted the parameter criteria from query. Created a blank form with a
text field for me to enter the IDno . Then placed a command button on this
form and in the properties window of this button, i chose "on click" event (
because i wanted to open the report based on the query upon clicking the
Enter button )and entered the text that u suggested.

on running this form i get messege : Compile error: Method or data member
not found.

even after on line search - i could not find how to "apply the filter using
the code " suitable for this task- i would be grateful if u could take me
throgh these steps in more detailed steps please??

I will need to see a Copy/Paste of the code you are trying
to use and which line has the error. Make sure you include
the name of the id field, the name of the text box, the name
of the report and the report's record source SQL statement.