Problem building ACCESS query for retrival of records.

[ashutoshvyas]

I want to write such criteria in the query of microsoft ACCESS, which can give records with secondlast date?
table & fields............desired output
no-name-date..............name-date
1-A-07/15/07..............A-07/15/07
2-A-08/18/07..............B-04/12/07
3-B-04/12/07
4-B-08/18/07
5-A-07/03/07

[ADezii]

I want to write such criteria in the query of microsoft ACCESS, which can give records with secondlast date?
table & fields............desired output
no-name-date..............name-date
1-A-07/15/07..............A-07/15/07
2-A-08/18/07..............B-04/12/07
3-B-04/12/07
4-B-08/18/07
5-A-07/03/07

I don't think that what you are requesting can be done with traditional SQL, but I think it can be done in code. There are other Moderators/Experts more proficient in SQL than I, so I'm just going to wait and see what they think. In the mean time, the following SQL will display the Last Date for each name. I know that it is not the solution, but it is a starting point:

SELECT tblDates.name, Max(tblDates.date) AS [Latest Date]
FROM tblDates
GROUP BY tblDates.name
ORDER BY tblDates.name;

OUTPUT:

name    Latest Date
A        8/18/2007
B        8/18/2007
C        12/30/2007
 

[ashutoshvyas]

Thanks for redirecting me towards the way, probably i can go near the solution

[ADezii]

Thanks for redirecting me towards the way, probably i can go near the solution

You're quite welcome. I'll continue to monitor this Post, and if no viable solution is found, I'll attempt one through code.

[ashutoshvyas]

You're quite welcome. I'll continue to monitor this Post, and if no viable solution is found, I'll attempt one through code.

Dear sir,
Can anything done like following ?
1) Last date records are filtered by the first query method you suggested.
2) with second query, all the records from first query are deducted.
3) The third query will display last records by same method from second query, which are records with secondlast date for first query.

You might generalise this equation.

[ashutoshvyas]

Thank you very much for your motivation, Sir. Your small help has prepared me to solve complicated problems.

I had successfully builded a set of three queries which can : Filter out each individual records with second last date for date field. The SQL code for that three queries is pasted :

Query_1 : dlt
SELECT payments.account_no, Max(payments.payment_date) AS [Latest Date]
FROM payments
GROUP BY payments.account_no
ORDER BY payments.account_no;

Query_2 : dlt_2
SELECT payments.account_no, payments.payment_date, dlt.[Latest Date]
FROM (Customers INNER JOIN dlt ON Customers.account_no = dlt.account_no) INNER JOIN payments ON Customers.account_no = payments.account_no
WHERE ((Not [payment_date]=[Latest Date]));

Query_3 : dlt_3
SELECT dlt_2.account_no, Max(dlt_2.payment_date) AS [Second last Date]
FROM dlt_2
GROUP BY dlt_2.account_no
ORDER BY dlt_2.account_no;

This solution can be helpful to others also. If i am doing any mistake, suggestions are welcome.

[ADezii]

Thank you very much for your motivation, Sir. Your small help has prepared me to solve complicated problems.

I had successfully builded a set of three queries which can : Filter out each individual records with second last date for date field. The SQL code for that three queries is pasted :

Query_1 : dlt
SELECT payments.account_no, Max(payments.payment_date) AS [Latest Date]
FROM payments
GROUP BY payments.account_no
ORDER BY payments.account_no;

Query_2 : dlt_2
SELECT payments.account_no, payments.payment_date, dlt.[Latest Date]
FROM (Customers INNER JOIN dlt ON Customers.account_no = dlt.account_no) INNER JOIN payments ON Customers.account_no = payments.account_no
WHERE ((Not [payment_date]=[Latest Date]));

Query_3 : dlt_3
SELECT dlt_2.account_no, Max(dlt_2.payment_date) AS [Second last Date]
FROM dlt_2
GROUP BY dlt_2.account_no
ORDER BY dlt_2.account_no;

This solution can be helpful to others also. If i am doing any mistake, suggestions are welcome.

A fine case of deductive reasoning - glad you were able to solve your own problem. Sometimes it just takes a little motivation on our part, and persistence on your part (LOL). I am also happy that you decided to share the solution with us, thanks.

[ashutoshvyas]

A fine case of deductive reasoning - glad you were able to solve your own problem. Sometimes it just takes a little motivation on our part, and persistence on your part (LOL). I am also happy that you decided to share the solution with us, thanks.

Hello everybody
I had got one more problem.

I want to give serial numbers to each records which are generated in the REPORT by the mean of query. So what to do for generating automic serial no's which are given to each records, each time the report is generated.

[ADezii]

Hello everybody
I had got one more problem.

I want to give serial numbers to each records which are generated in the REPORT by the mean of query. So what to do for generating automic serial no's which are given to each records, each time the report is generated.

Are these Serial Numbers to be in any specific format or are they to just be numbered sequentially as in 1..2..3..4..5..6..7..8..9..10..........?

[ashutoshvyas]

Are these Serial Numbers to be in any specific format or are they to just be numbered sequentially as in 1..2..3..4..5..6..7..8..9..10..........?

No specific formet. Instead of serial no, I can call it reacord count.

In General when this report is executed, each time the records will be counted and they will be numbered sequentially as 1,2,3,4,5........ till the last record.

e.g. Report for the records where city = London

Sr.No Name city
1 A London
2 B London
3 X London

[ashutoshvyas]

dear all
i am pasting the code with errors, an effort to take this problem towards solution. If anyone can turn it into functionality, it will be a great help for me.

' I KNOW THIS CODE IS WITH ERRORS BUT I WANT TO MAKE IT ERROR FREE IF POSSIBLE
' SO THAT I CAN GENERATE A REPORT WHICH ACCEPTS DATE AS PARAMETER
' AND FILTER THAT RECORDS BY DATE AND STORES RESULTS IN TEMPORARY TABLE
' SO THAT RECORD COUNT CAN BE DONE AND THEN REPORT CAN BE GENERATED.

Private Sub Command1_Click()
'The If block closes the recordset if it was previously open before
'running the parameterized query.

With DataEnvironment1
If .rsCommand1.State = adStateOpen Then
.rsCommand1.Close
End If

' This passes in the value entered into the TextBox.

.command1 CDate(text1.Text)

' This If block checks to determine if any records are returned
' by the parameter. Then it shows a report if records are returned.
' Or displays a Message Box if no records are returned.

If .rsCommand1.RecordCount > 0 Then
Set rptashu.DataSource = DataEnvironment1

' code inserted to prepare a new table with record count

Dim i As Long
'Delete current data in A_Temp_Table
sql = "DELETE * FROM temptable"
Con.Excute sql

'select the data needed
sql = "SELECT * FROM dataenvironment1" ' selecting all the fields
Set rs = Con.Excute(sql)
i = 1
While Not rs.EOF
'insert row for temporary table with serial column
sql = "INSERT INTO temptable (Serial,account_no,card_no,name,amount,payment_dat e,Second last Date,Sum Of amount) VALUES(" & i & "," & rs.Fields(1) & "," & rs.Fields(2) & "," & rs.Fields(3) & "," & rs.Fields(4) & "," & rs.Fields(5) & "," & rs.Fields(6) & "," & rs.Fields(7)
Con.Excute sql
rs.MoveNext
Wend
'now, showing Report with data load from temp table
' report shown by parameter paseed as date, and taking all the records from temp table filtered by date and with record count

rptashu.Show
Else
MsgBox "No Titles found"
End If
End With
End Sub

[ADezii]

dear all
i am pasting the code with errors, an effort to take this problem towards solution. If anyone can turn it into functionality, it will be a great help for me.

' I KNOW THIS CODE IS WITH ERRORS BUT I WANT TO MAKE IT ERROR FREE IF POSSIBLE
' SO THAT I CAN GENERATE A REPORT WHICH ACCEPTS DATE AS PARAMETER
' AND FILTER THAT RECORDS BY DATE AND STORES RESULTS IN TEMPORARY TABLE
' SO THAT RECORD COUNT CAN BE DONE AND THEN REPORT CAN BE GENERATED.

Private Sub Command1_Click()
'The If block closes the recordset if it was previously open before
'running the parameterized query.

With DataEnvironment1
If .rsCommand1.State = adStateOpen Then
.rsCommand1.Close
End If

' This passes in the value entered into the TextBox.

.command1 CDate(text1.Text)

' This If block checks to determine if any records are returned
' by the parameter. Then it shows a report if records are returned.
' Or displays a Message Box if no records are returned.

If .rsCommand1.RecordCount > 0 Then
Set rptashu.DataSource = DataEnvironment1

' code inserted to prepare a new table with record count

Dim i As Long
'Delete current data in A_Temp_Table
sql = "DELETE * FROM temptable"
Con.Excute sql

'select the data needed
sql = "SELECT * FROM dataenvironment1" ' selecting all the fields
Set rs = Con.Excute(sql)
i = 1
While Not rs.EOF
'insert row for temporary table with serial column
sql = "INSERT INTO temptable (Serial,account_no,card_no,name,amount,payment_dat e,Second last Date,Sum Of amount) VALUES(" & i & "," & rs.Fields(1) & "," & rs.Fields(2) & "," & rs.Fields(3) & "," & rs.Fields(4) & "," & rs.Fields(5) & "," & rs.Fields(6) & "," & rs.Fields(7)
Con.Excute sql
rs.MoveNext
Wend
'now, showing Report with data load from temp table
' report shown by parameter paseed as date, and taking all the records from temp table filtered by date and with record count

rptashu.Show
Else
MsgBox "No Titles found"
End If
End With
End Sub

1. As far as generating sequential numbers in a Report:
   1. Create an Unbound Text Box within the Detail Section of your Report.
   2. Set the Control Source of this Text Box to =1.
   3. Set the Running Sum to Over All.
   4. The above actions will display a sequential numbered list for each Record in the Report starting at 1.
2. I'm having a little trouble interpreting your code. It seems as though you are attempting to generate a Report from External Data in an Access Database. This seems to be done via a DataEnvironment within Visual Basic. Is this correct? If this is not the case, please clarify.

[ashutoshvyas]

1. As far as generating sequential numbers in a Report:
   1. Create an Unbound Text Box within the Detail Section of your Report.
   2. Set the Control Source of this Text Box to =1.
   3. Set the Running Sum to Over All.
   4. The above actions will display a sequential numbered list for each Record in the Report starting at 1.
2. I'm having a little trouble interpreting your code. It seems as though you are attempting to generate a Report from External Data in an Access Database. This seems to be done via a DataEnvironment within Visual Basic. Is this correct? If this is not the case, please clarify.

Dear sir,
you r correct. I forgot to mention, so please pardon.
I want to create report using visual basic by data environment.
I am using access query as data provider, by which report is generated in visual basic. I had been successful to generate a report, but I face the problem to give recoard count. so i want to count records and assign sequential record numbers in the report.

The SQL code to link query is as under :
select * from final_qry where payment_date = ?

And code by which report is generated successfully by passing parameter is as under, but without sequential record numbers.

Private Sub Command1_Click()
'The If block closes the recordset if it was previously open before
'running the parameterized query.
With DataEnvironment1
If .rsCommand1.State = adStateOpen Then

.rsCommand1.Close
End If

' This passes in the value entered into the TextBox.

.command1 CDate(text1.Text)

' This If block checks to determine if any records are returned
' by the parameter. Then it shows a report if records are returned.
' Or displays a Message Box if no records are returned.
If .rsCommand1.RecordCount > 0 Then
Set rptashu.DataSource = DataEnvironment1

rptashu.Show
Else
MsgBox "No Titles found"
End If
End With
End Sub

I want to learn the method, something by which record numbers can be included in this report.

[ADezii]

Dear sir,
you r correct. I forgot to mention, so please pardon.
I want to create report using visual basic by data environment.
I am using access query as data provider, by which report is generated in visual basic. I had been successful to generate a report, but I face the problem to give recoard count. so i want to count records and assign sequential record numbers in the report.

The SQL code to link query is as under :
select * from final_qry where payment_date = ?

And code by which report is generated successfully by passing parameter is as under, but without sequential record numbers.

Private Sub Command1_Click()
'The If block closes the recordset if it was previously open before
'running the parameterized query.
With DataEnvironment1
If .rsCommand1.State = adStateOpen Then

.rsCommand1.Close
End If

' This passes in the value entered into the TextBox.

.command1 CDate(text1.Text)

' This If block checks to determine if any records are returned
' by the parameter. Then it shows a report if records are returned.
' Or displays a Message Box if no records are returned.
If .rsCommand1.RecordCount > 0 Then
Set rptashu.DataSource = DataEnvironment1

rptashu.Show
Else
MsgBox "No Titles found"
End If
End With
End Sub

I want to learn the method, something by which record numbers can be included in this report.

It seems to me that the problem is how to generate sequential numbers in the underlying SQL, namely: select * from final_qry where payment_date = ?. It would then be a simple matter to display these numbers in the Report. The answer to this dilemma, at least to me, is not that obvious but I'll continue to have a look at it. As previously stated, generating sequential numbers within an Internal Access Report is quite simple, this is not the case here.

[ashutoshvyas]

It seems to me that the problem is how to generate sequential numbers in the underlying SQL, namely: select * from final_qry where payment_date = ?. It would then be a simple matter to display these numbers in the Report. The answer to this dilemma, at least to me, is not that obvious but I'll continue to have a look at it. As previously stated, generating sequential numbers within an Internal Access Report is quite simple, this is not the case here.

Very correct sir. I had thinked a lot, that if sequential numbers r generated in the underlying SQL, the thing will be very easy. But i found the problem stated below :

One 'form' in visual basic is created by me, which will collect the date as parameter and then this query will be used to give records for that date. The problem is, we did not get the number of records filtered until this query is executed and so in advance we can not set one field for sequential numbers.

So as per your deep experience, is it possible thing to generate sequential numbers in the query itself ? If it seems possible to you, please guide me on the way.

[ADezii]

Very correct sir. I had thinked a lot, that if sequential numbers r generated in the underlying SQL, the thing will be very easy. But i found the problem stated below :

One 'form' in visual basic is created by me, which will collect the date as parameter and then this query will be used to give records for that date. The problem is, we did not get the number of records filtered until this query is executed and so in advance we can not set one field for sequential numbers.

So as per your deep experience, is it possible thing to generate sequential numbers in the query itself ? If it seems possible to you, please guide me on the way.

I'm still working on it, please be patient.

[ADezii]

I'm still working on it, please be patient.

It just dawned on me that I had experienced a similar request from a Member a long time ago, and here is how I resolved his problem. The circumstances are quite different, and the solution is a little extreme, but there seemed to be no other viable options and this logic does, in fact, work. I don't think that something like this can be implemented in your specific case, but I'll show you anyway. If you have any other questions, feel free to ask:

NeoPa: (me writing)
Here is a little Off-The-Wall technique that I always use whenever I want Query results to be numbered sequentially starting from 1 regardless of any Sort Order on any Field or Fields, number of Fields, etc. It is a little radical but there seems to be no other way to achieve the same result. Please look at it and let me know what you think. This code must be run prior to executing the Query and it also involves adding a [Counter] Field to the underlying Table then executing the Query with this ([Counter]) as the very 1st Column. It creates a Recordset based on the very Query that you want to sequentially number and writes the incremental values to the [Counter] Field starting at position 1. I'm sure that it can be improved - I just know that it works.

Dim MyDB As DAO.Database, MyRS As DAO.Recordset, intRecNo As Integer
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("qryEmployee", dbOpenDynaset)
 
Do While Not MyRS.EOF
  intRecNo = intRecNo + 1
    MyRS.Edit
      MyRS![Counter] = intRecNo
    MyRS.Update
    MyRS.MoveNext
Loop
 
MyRS.Close

No matter what Criteria you specify for qryEmployee, and no matter how many Records are returned, they will always be numbered 1 to Recordset.RecordCount.

[ashutoshvyas]

It just dawned on me that I had experienced a similar request from a Member a long time ago, and here is how I resolved his problem. The circumstances are quite different, and the solution is a little extreme, but there seemed to be no other viable options and this logic does, in fact, work. I don't think that something like this can be implemented in your specific case, but I'll show you anyway. If you have any other questions, feel free to ask:

Dim MyDB As DAO.Database, MyRS As DAO.Recordset, intRecNo As Integer
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("qryEmployee", dbOpenDynaset)
 
Do While Not MyRS.EOF
  intRecNo = intRecNo + 1
    MyRS.Edit
      MyRS![Counter] = intRecNo
    MyRS.Update
    MyRS.MoveNext
Loop
 
MyRS.Close

No matter what Criteria you specify for qryEmployee, and no matter how many Records are returned, they will always be numbered 1 to Recordset.RecordCount.

Thank you again.
I had noticed that there is the word expert under your name which was moderator few days ago, so congrats for becoming expert.

Truly u r genius & expert. I like your way to support, that is the right way. Instead of giving straight forward answers u r giving answers in the form of hints or clever language. I like your art.

with the help of your last post i had reached to the solution and now successful generation of report is done with sequential numbers. I am providing the code for reference for others.

1) In command1 i have to select lock type = 4- batch optimistic.
2) code segment (VB)

If .rsCommand1.RecordCount > 0 Then
.rsCommand1.MoveFirst
Dim intRecNo As Integer
Do While Not .rsCommand1.EOF
intRecNo = intRecNo + 1
.rsCommand1.Update
.rsCommand1![Counter] = intRecNo
.rsCommand1.Update
.rsCommand1.MoveNext
Loop

Set rptashu.DataSource = DataEnvironment1
rptashu.Show
Else
MsgBox "No Titles found"
End If
End With
End Sub

Thank you again.

[ADezii]

Thank you again.
I had noticed that there is the word expert under your name which was moderator few days ago, so congrats for becoming expert.

Truly u r genius & expert. I like your way to support, that is the right way. Instead of giving straight forward answers u r giving answers in the form of hints or clever language. I like your art.

with the help of your last post i had reached to the solution and now successful generation of report is done with sequential numbers. I am providing the code for reference for others.

1) In command1 i have to select lock type = 4- batch optimistic.
2) code segment (VB)

If .rsCommand1.RecordCount > 0 Then
.rsCommand1.MoveFirst
Dim intRecNo As Integer
Do While Not .rsCommand1.EOF
intRecNo = intRecNo + 1
.rsCommand1.Update
.rsCommand1![Counter] = intRecNo
.rsCommand1.Update
.rsCommand1.MoveNext
Loop

Set rptashu.DataSource = DataEnvironment1
rptashu.Show
Else
MsgBox "No Titles found"
End If
End With
End Sub

Thank you again.

I'm happy that you arrived at the solution and did, in fact, share it with all of us. I also thank you for your kind compliments, they are greatly appreciated. I'm just so glad that an old solution resurfaced and was able to assist another member. Take care and keep in touch.