Curent and Previous Date report

[whiterower]

Hi, I have a problem getting the current date and previous date on my report my table looks like this:

tblmeter:
MeterID-autonumber primary key
MachineNo
Dateentered
Meter1
Meter2

Sample Records:
MeterID MachineNo Dateentered Meter1 Meter2
1 123 12/10/03 100 200
2 124 12/10/03 300 200
3 123 12/19/03 200 300
4 124 12/19/03 400 300
5 123 12/22/03 300 400
6 124 12/22/03 500 400
....
and so on theres about a thosand machines with reading per week and the dates were not fixed they read the machines whenever they want for testing.

I attached the format of the output report how it looks like.Pls need help i been searching this forum for couple of days now but couldn't find one that suit my needs..i tried other quries to no avail..thanks

 

[Jon K]

You can do it with a series of two queries.

Query1:-
SELECT [tblmeter].[MeterID], [tblmeter].[MachineNo],
[tblmeter].[Dateentered], [tblmeter].[Meter1], [tblmeter].[Meter2],
(Select Count(*) from tblmeter as s where s.MachineNo=tblmeter.MachineNo and s.Dateentered <=tblmeter.Dateentered ) AS Num1, Num1+1 AS Num2
FROM tblmeter
ORDER BY [MachineNo], [Dateentered];

Query2:-
SELECT [Query1].[MachineNo],
[Query1].[Dateentered] AS CurrentDate,
DLookUp("DateEntered","Query1","[MachineNo]=" & [MachineNo] & " and [Num2]=" & [Num1]) AS PreviousDate,
[Query1].[Meter1] AS CurrentMeter1,
DLookUp("Meter1","Query1","[MachineNo]=" & [MachineNo] & " and [Num2]=" & [Num1]) AS PreviousMeter1,
[Query1].[Meter2] AS CurrentMeter2,
DLookUp("Meter2","Query1","[MachineNo]=" & [MachineNo] & " and [Num2]=" & [Num1]) AS PreviousMeter2
FROM Query1;

You can open the attached database and run Query2.


Note these limitations:

Running the subquery in Query1 and the inefficient DLookUp() functions in Query2 will take time when the table is fairly large.

The previous dates and the previous readings returned by DLookUp() are actually "text" and are formatted only as date and numbers in the Field Properties in query design. To return actual dates and numbers, you will have to use additional data-type conversion functions such as CDate() and CInt() and also the IIF() function to test for null dates and null readings. These additional functions will further slow down the queries.


If performance is a problem when using the these queries, I think you should consider adding the previous date and previous meter reading fields to the table and directly update them there whenever a new record is added to the table.


The database was saved from Access 2000.

 

[whiterower]

Thanks Jon K for your reply,

I will querying from a large table..i tried your example and it works great.

How could I update the previous date and previous meter to the table whenever i have a new record?Thats a good idea..also there's another table that hold the electonic readings for each machine and i want to append the current electronic reading to my tblmeter the electronic reading to a field "elecmeter1" and electmeter2" whenever a corres[onding machine is added to my tblmeter.


Once again thank you.

 

[whiterower]

Thanks Jon K for your reply,

I will querying from a large table..i tried your example and it works great.

How could I update the previous date and previous meter to the table whenever i have a new record?Thats a good idea..also there's another table that hold the electonic readings for each machine and i want to append the current electronic reading to my tblmeter the electronic reading to a field "elecmeter1" and electmeter2" whenever a corres[onding machine is added to my tblmeter.


Once again thank you.

 

[Jon K]

>
How could I update the previous date and previous meter to the table whenever i have a new record?
<

You can do this using VBA on a data entry form.

I have added three fields in the table for the previous date and readings. As soon as a MachineNo is entered on the form, the previous date and readings for that MachineNo will be displayed. They will also be saved to the table. I have also included a new query to view the records.

The VBA code is in the After Update event of the MachineNo text box:

Private Sub MachineNo_AfterUpdate()
  Dim rs As DAO.Recordset
  Dim SQL As String
  
  SQL = "Select Top 1 [DateEntered] as PrevDate," & _
      " [Meter1] as PrevMeter1, [Meter2] as PrevMeter2" & _
      " From [tblMeter]" & _
      " Where [MachineNo]=" & Me.MachineNo & _
      " Order By [DateEntered] Desc"
 
  Set rs = CurrentDb.OpenRecordset(SQL)
  If Not rs.EOF Then
    Me.PreviousDate = rs!PrevDate
    Me.PreviousMeter1 = rs!PrevMeter1
    Me.PreviousMeter2 = rs!PrevMeter2
  End If
  Set rs = Nothing

End Sub

The code was written in Access 97, so DAO was used. If you write the code in Access 2000 or later, you must make a reference to DAO (when the code window is open, choose menu Tools, References... and select the Microsoft DAO Object Library 3.6).


As for the second question, you can also use VBA to append the readings to another table, though we seldom store the same data in two tables.

As an illustration, I have included a command button on the form. Whenever the button is clicked. the current readings are appended to the table "tblAnother". The code is in the On Click event of the command button:

Private Sub cmdAppend_Click()
  Dim SQL As String
  
  SQL = "Insert Into [tblAnother]" & _
      " ([MeterId], [MachineNo], [DateEntered]," & _
      " [Meter1], [Meter2])" & _
      " Values (" & Me.MeterID & "," & Me.MachineNo & _
      ",#" & Me.DateEntered & "#," & Me.Meter1 & _
      "," & Me.Meter2 & ")"
   
  DoCmd.SetWarnings False
  DoCmd.RunSQL SQL
  DoCmd.SetWarnings True
  MsgBox "Current readings appended to table tblAnother"
End Sub

Hope it helps.

 

[whiterower]

Thanks Jon K,

I'll gonna try it

Cheers

 

[whiterower]

Hi Jon K,

I tried your example and works great thank you so much I really appreciate your help.

 

[whiterower]

Jon I need help again..what if i edit the first record, the next record should update the previous readings automatically..On your sample database try to edit the 1st record,change meter1 to 250, the previous readings on record3 should change meter1 to 250 also..How will I do this..This is the only thing i Need and I will deploy the system on Monday...


Any help is greatly appreciated ...Thanks a lot

 

[Jon K]

I have added update code in the After Update event of the text boxes Meter1 and Meter2 in the database.

 

[whiterower]

Thanks Jon, i appreciate your help..

 

[whiterower]

Hi Jon,

Here I am again..you know what the user tested the program and found a problem that i need to resolve..they enter todays date and the corresponding meters works fine but sometimes they enter dates before..such as:

1st Entry:01/22/04 meter1:200 meter2:300
2nd Entry:01/20/04 meter1:200 meter2:300

If I use the date entry form to get the previous date and meter when I do the first entry it wont pick up the previous date,previousmeter1 and previousmete2 since theres no previous meters entry..how can I work around this one that if they enter the 2nd entry it will update automatically the previous date,previousmeter1 and previousmeter2 of the 1st entry?