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:
[CODE=sql]SELECT tblDates.name, Max(tblDates.da te) AS [Latest Date]
FROM tblDates
GROUP BY tblDates.name
ORDER BY tblDates.name;[/CODE]
OUTPUT:
[CODE=text]
name Latest Date
A 8/18/2007
B 8/18/2007
C 12/30/2007
[/CODE]

[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.accoun t_no, Max(payments.pa yment_date) AS [Latest Date]
FROM payments
GROUP BY payments.accoun t_no
ORDER BY payments.accoun t_no;

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

Query_3 : dlt_3
SELECT dlt_2.account_n o, Max(dlt_2.payme nt_date) AS [Second last Date]
FROM dlt_2
GROUP BY dlt_2.account_n o
ORDER BY dlt_2.account_n o;

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.accoun t_no, Max(payments.pa yment_date) AS [Latest Date]
FROM payments
GROUP BY payments.accoun t_no
ORDER BY payments.accoun t_no;

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

Query_3 : dlt_3
SELECT dlt_2.account_n o, Max(dlt_2.payme nt_date) AS [Second last Date]
FROM dlt_2
GROUP BY dlt_2.account_n o
ORDER BY dlt_2.account_n o;

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]

How To Give Serial Numbers To Generated Records In Report

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 DataEnvironment 1
If .rsCommand1.Sta te = adStateOpen Then
.rsCommand1.Clo se
End If

' This passes in the value entered into the TextBox.

.command1 CDate(text1.Tex t)

' 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.Rec ordCount > 0 Then
Set rptashu.DataSou rce = DataEnvironment 1

' 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 dataenvironment 1" ' 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,nam e,amount,paymen t_date,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 DataEnvironment 1
If .rsCommand1.Sta te = adStateOpen Then
.rsCommand1.Clo se
End If

' This passes in the value entered into the TextBox.

.command1 CDate(text1.Tex t)

' 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.Rec ordCount > 0 Then
Set rptashu.DataSou rce = DataEnvironment 1

' 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 dataenvironment 1" ' 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,nam e,amount,paymen t_date,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 DataEnvironment 1
If .rsCommand1.Sta te = adStateOpen Then

.rsCommand1.Clo se
End If

' This passes in the value entered into the TextBox.

.command1 CDate(text1.Tex t)

' 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.Rec ordCount > 0 Then
Set rptashu.DataSou rce = DataEnvironment 1

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 DataEnvironment 1
If .rsCommand1.Sta te = adStateOpen Then

.rsCommand1.Clo se
End If

' This passes in the value entered into the TextBox.

.command1 CDate(text1.Tex t)

' 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.Rec ordCount > 0 Then
Set rptashu.DataSou rce = DataEnvironment 1

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.