Query

[firoz.raj]

sir i am writing background query to retrieve last of qty
i am having production consumable product,maintenance
consumable product with product_id M1 TO m1000
and tools consumable product(t1 to t1000).

i want to retrieve item_code, productname, unit, max(ID) AND LAST
Qty i am getting all these things except last of qty in the query result
kindly help me
how should i get last of qty

SELECT DISTINCTROW PRODUCTION.Item_code, PRODUCTION.Productname, PRODUCTION.unit, Max(PRODUCTION.ID) AS [Max Of ID], Last(PRODUCTION.Qty) AS LastOfQty
FROM PRODUCTION
GROUP BY PRODUCTION.Item_code, PRODUCTION.Productname, PRODUCTION.unit
kindly find part detail attachmet :
it is very very urgent

 

[jal]

A Group By eliminates dups but, behind the scenes, the engine keeps a record of all the individual dups in each set of dups. The LAST function, when used with Group By, will probably return the Last dup for each dup set (as opposed to the last record in the whole table).

Which do you want to return?

 

[jal]

Also, I'm guessing that SELECT executes last. Since the GROUP BY has already eliminated the dups, I don't see why DistinctRow is needed. (I could be wrong).

 

[raskew]

This is merely this post http://www.access-programmers.co.uk/forums/showthread.php?t=161766 rewritten.

Please avoid posting duplicate threads.

Bob

P.S.: A subject of 'Query', posted in the Queries forum, doesn't give readers much to go on.

 

[firoz.raj]

NOW I MADE A FUNCTION IN VB6 AND BACKED MS ACCESS 2003 I WANT IF USER CLICK ON SAVE BUTTON PRODUCTNAME,ITEM_CODE, AND UNIT SHOULD BE STORE
IN MR TABLE BUT THIS FUNCTION IS NOT WORKING .
Kindly help
Sub InsertIntoMR()
If DataPath = "" Then
Exit Sub
Else
Dim rs As adodb.Recordset
Dim productname, item_code, unit, strSql, GetConnString As String
Set rs = New Recordset
rs.Open strSql, con, adOpenDynamic, adLockOptimistic
strSql = "INSERT INTO MR "
strSql = strSql & "(item_code,productname,unit)"
strSql = strSql & "VALUES ("
With Grd
strSql = strSql & "'" & .TextMatrix(.Row, 1) & "',"
strSql = strSql & "'" & .TextMatrix(.Row, 2) & "',"
strSql = strSql & "'" & .TextMatrix(.Row, 3) & "',"
End With
con.Open GetConnString
con.Execute GetConnString
Set con = Nothing
End If
End Sub

 

[jal]

Dim rs As adodb.Recordset
Dim productname, item_code, unit, strSql, GetConnString As String
Set rs = New Recordset
rs.Open strSql, con, adOpenDynamic, adLockOptimistic

Oddly, you seem to open a recordset and then you never make use of it. Also, you open the recordset on strSql before having built the sql.

strSql = "INSERT INTO MR "
strSql = strSql & "(item_code,productname,unit)"
strSql = strSql & "VALUES ("
With Grd
strSql = strSql & "'" & .TextMatrix(.Row, 1) & "',"
strSql = strSql & "'" & .TextMatrix(.Row, 2) & "',"
strSql = strSql & "'" & .TextMatrix(.Row, 3) & "',"
End With

This part doesn't look too bad, it might be correct. Keep in mind, though, that quote marks are needed only for strings (productName), but if "unit" and "item_code" are numbers they won't need the extra quote marks.

con.Open GetConnString

If you are doing this in Access you usually won't need a connection string because you can use CurrentProject.Connection.

con.Execute GetConnString

No. Don't execute a connection string. Execute your SQL.

con.Execute strSQL

or possibly


CurrentProject.Connection.Execute strSQL


Maybe I'll get you some more tips on this.

 

[jal]

   'Get an open connection:
Dim cn As New ADODB.Connection
Set cn = CurrentProject.Connection
'if you are not using CurentProject.Connection
'then use these two lines of code INSTEAD
cn.ConnectionString = GetConnString
cn.Open
'Anyway,  now open the recordset.
Dim rs As New ADODB.Recordset
'Pull ZERO records from the table
rs.Open "SELECT * FROM MR WHERE 1 = 0", cn, adOpenKeyset, adLockOptimistic
rs.AddNew 'Adds a new record to the empty recordset.
With Grd
      rs("item_code") = .TextMatrix(.Row, 1)
      rs("ProductName") = .TextMatrix(.Row, 2)
     rs("unit") = .TextMatrix(.Row, 3)
End With
CloseThisRecordSet rs
'If using a special connection then you'd better close it:
'but if using CurrentProject.Connection then don't close it
'and don't set it to nothing.
cn.Close
Set cn = Nothing
 
 
 
Private Sub CloseThisRecordSet(ByRef recSet As ADODB.Recordset)
    If (recSet.State And ADODB.adStateOpen) <> ADODB.adStateOpen Then GoTo ExitSub
    If recSet.LockType = adLockReadOnly Then GoTo CloseRecordSet
    If recSet.EOF Or recSet.BOF Then GoTo CloseRecordSet
    If (recSet.EditMode = adEditAdd) Or (recSet.EditMode = adEditDelete) Then recSet.Update
CloseRecordSet:       recSet.Close
ExitSub:       Set recSet = Nothing
 End Sub

 

[firoz.raj]

Sir i want to store grid data column wise these lines are working fine
but i want to store multiple record in A Ms access 2003 table
rs1.Fields("item_code") = Grd.TextMatrix(1, 1)
rs1.Fields("productname") = Grd.TextMatrix(1, 2)
rs1.Fields("unit") = Grd.TextMatrix(1, 3)
these line working properly but i want to store these data column wise in a table Instead of storing Grd.TextMatrix(1, 1) value it store all the column value multible record should be add in a Mr table.

Private Sub Command1_Click()
Dim item_code, product_name, unit As String
Dim strSql As String
Dim nm As String
' Debug.Assert False
' On Error GoTo err
Set rs1 = New Recordset
rs1.Open " SELECT * FROM MR ", con, adOpenDynamic, adLockOptimistic
rs1.AddNew
rs1.Fields("Dept_name") = Combo1.Text
rs1.Fields("job_no") = Text1.Text
If IsNumeric(Text2.Text) Then
rs1.Fields("cost_centre") = CLng(Text2.Text)
rs1.Fields("emp_name") = Text3.Text
rs1.Fields("del_point") = Text4.Text
rs1.Fields("del_time") = CStr(MaskEdBox1)
If IsDate(DTPicker2.Value) Then
rs1.Fields("del_date") = DTPicker2.Value
rs1.Fields("sug_vendor") = Text7.Text
If IsDate(DTPicker1.Value) Then
rs1.Fields("mr_date") = DTPicker1.Value
rs1.Fields("manager") = Text13.Text
rs1.Fields("qty") = FlexGridEditor.Value
rs1.Fields("item_code") = Grd.TextMatrix(1, 1)
rs1.Fields("productname") = Grd.TextMatrix(1, 2)
rs1.Fields("unit") = Grd.TextMatrix(1, 3)
' Call InsertIntoMR
' strSql = "insert into MR values('" & cItemCode & "','" & product_name & "','" & unit & "')"
' strSql = strSql & "'" & Val(Grd.TextMatrix(Grd.Row, 1)) & "',"
' Debug.Print strSql
' con.Execute (strSql)
' rs1.Fields("Unit").Value = unit
' rs1.Fields("item_code") = Trim(Grd.TextMatrix(Grd.Row, Grd.Col) & "")
' rs1.Fields("item_code").Value = Trim(Grd.TextMatrix(Grd.Row, cItemCode) & " ")
' rs1.Fields("productname") = Trim(Grd.TextMatrix(Grd.Row, Grd.Col) & "")
' rs1.Fields("unit") = Trim(Grd.TextMatrix(Grd.Row, cUnit) & "")
End If
End If
End If
rs1.Update
MsgBox Trim(Grd.TextMatrix(1, 1))
MsgBox "Commit", vbInformation, Me.Name
rs1.Close
Exit Sub
'err: MsgBox "Error in Command1_Click()" & vbCrLf & err.Number & vbCrLf & err.Description
End Sub

 

[jal]

Probably the same idea as what you already have coded, but you may need to loop through your flex grid (I've never used one) to do this a row at a time. Sorry I don't know how to loop through one.

 

[firoz.raj]

Probably the same idea as what you already have coded, but you may need to loop through your flex grid (I've never used one) to do this a row at a time. Sorry I don't know how to loop through one.

i want my requisition no should start from 214500 and when user again save record through frontend it should increase by 1

how should i set req_no fields property so that it should be start 214500
but it should not be autonumber. it should be number.

 

[jal]

By the way, as I am still a beginner, I've been struggling to find a fail-safe way to close a recordset. I have updated the code that I gave you earlier. My new version is this one:

Private Sub CloseThisRecordSet(ByRef recSet As ADODB.Recordset)
'Warning: This sub fails to update a ForwardOnly RS if at EOF.
On Error GoTo ExitSub 'The next line throws an error if the RS is already closed
If recSet.EOF And recSet.BOF Then GoTo CloseRecordSet 'emtpy recordset.
'This RS is not empty. It has records. And it is open.
If recSet.LockType = adLockReadOnly Then GoTo CloseRecordSet
If recSet.CursorType = adOpenForwardOnly Then GoTo Update
If recSet.EOF Then recSet.MovePrevious
If recSet.BOF Then recSet.MoveFirst
Update: recSet.Update
CloseRecordSet: recSet.Close
ExitSub: Set recSet = Nothing
End Sub

 

[jal]

i want my requisition no should start from 214500 and when user again save record through frontend it should increase by 1

how should i set req_no fields property so that it should be start 214500
but it should not be autonumber. it should be number.

Private Sub Command0_Click()
    Dim NextValue As Long
    NextValue = 214500
    On Error Resume Next 'turns off error messages
    NextValue = ReturnOneValue("SELECT Max(req_no) as Result FROM Table1") + 1
    On Error GoTo 0 'turns error messages back on
    Dim rs As New ADODB.Recordset
    'I'm not sure whether you are using CurrentProject.Connection
    rs.Open "select * from table1 WHERE 1=0", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    rs.AddNew
    rs("Req_No") = NextValue
    'put code here to populate the rest of the columns
    CloseThisRecordSet rs
End Sub
 
 
'Note: This code uses CurrentProject.Connection.
'This code works only if you use  the words 'As Result' in the query string.
'For example, "SELECT  LastName as Result FROM Customers WHERE CustId = 55"
 Public Function ReturnOneValue(ByVal query As String) As Variant
    Dim rs As New ADODB.Recordset
      rs.Open query, CurrentProject.Connection, adOpenStatic, adLockReadOnly
    If rs.RecordCount > 1 Then
     Call Err.Raise(500, , "Your custom Execute Scalar method has returned more than one value.")
    ElseIf rs.RecordCount = 0 Then
        ReturnOneValue = Null
    Else: ReturnOneValue = rs("Result")
    End If
    rs.Close
    Set rs = Nothing
  End Function

 

[firoz.raj]

can anybody tell me how should i genereate a Ms access query so that
i see records itemwise i have written a query but it is not working
i want to display records item wise suppose if code of item is p70
when user put p70 it should show matching productname with issues
quantity as per user req.
item_code, productname issueqty
p70 laptop 10
laptop 20
30
totol 60

kindly help me i have written a query but it is not working i have written
a code in front end
rs.Open "select productname,count(qty) as Qty from partdetail group by productname having item_code= '" & Text1.Text & "' ", con, adOpenDynamic, adLockOptimistic
can anybody tell me sothat i only retrieve records itemwise and call
on the front end

TRANSFORM Sum(PRODUCTION.Qty) AS SumOfQty
SELECT PRODUCTION.Productname, PRODUCTION.Item_code, Sum(PRODUCTION.Qty) AS TotalOfQty
FROM PRODUCTION
GROUP BY PRODUCTION.Productname, PRODUCTION.Item_code
PIVOT Format([Trans_date],"Short_ Date");

 

[jal]

Are you sure a crosstab query is needed? Maybe a simple group by could do the trick?

SELECT PRODUCTION.Productname, PRODUCTION.Item_code, Sum(PRODUCTION.Qty) AS TotalOfQty
FROM PRODUCTION
GROUP BY PRODUCTION.Productname, PRODUCTION.Item_code

Maybe you should upload a spreadsheet showing exactly what the output should look like. Maybe include a list of columns in the original table(s), and maybe even some sample values.

 

[firoz.raj]

SELECT PRODUCTION.Productname, PRODUCTION.Item_code, Sum(PRODUCTION.Qty) AS TotalOfQty
FROM PRODUCTION
GROUP BY PRODUCTION.Productname, PRODUCTION.Item_code

this will simply retrieve productname,item_code and total of qty
and what about the detail of total quantity suppose there is a
storekeeper he has stock 100 of laptop he sales in a weak like
on january 2 he sales 10 pcs on january 3 he sales 20 pcs , on
january 7 he sales 70 pcs according to this query it will return 90
but i want the detail of laptop
like name code 10
20
70 this will come on the query
after that total 100

 

[jal]

Sorry, this is all gettng over my head. Doubt I can help - you have several columns and queries which I don't really understand. I was hoping it would be simpler. Anyway I tried this code:

rs.Open "select productname,count(qty) as Qty from partdetail group by productname ", con, adOpenKeyset, adLockOptimistic

and Access complained "circular reference" so you might want to change Qty to

The_Qty

 

[firoz.raj]

Re: generating complicated report in vb6

Can i design complicated report like Material requisition ,purchase order in Ms access since i am using backend as a ms access 2003 and front end
vb6(sp6) . May i get some help.

 

[jal]

Re: generating complicated report in vb6

Can i design complicated report like Material requisition ,purchase order in Ms access since i am using backend as a ms access 2003 and front end
vb6(sp6) . May i get some help.

At first glance, at least, that kind of thing looks possible in Ms Access Reporting. Have your code create a query that produces the data needed for the report, or code that creates a table that contains this report data. Remember, you can create a query using code like this:

Dim qDef as queryDef
On error resume next
set qDef = CurrentDB.QueryDefs("qryReport")
On error goto 0
if qDef is nothing then set qdef = CurrentDb.CreateQueryDef("qryReport")
qDef.SQL = "SELECT...."


Or, you can create a table like this:

DoCmd.RunSQL "SELECT * INTO tblForReport FROM table1....


Anyway base your report on this table or query that contains your report data. Then go into design view and use view > FieldList to see the columns. Drag the column names onto the report (Drag them into the Detail section to create columns on the report). Put labels above the textboxes, to hopefully serve as column headers. Put labels anywhere you need captions.

- You can drop textboxes anywhere on the report and then set their ControlSource to an appropriate column name or formula. For example you can first drop a label that says "Today's Date" and then, next to it, a textbox whose ControlSource value is
=Format(Date(),"dd-mmm-yyyy")
Notice that you have to precede the formula with an Equals sign, whereas if it's only a column name, I don't think the equals sign is necessary.
- I would imagine you can also set the textbox values using code, something like

Reports!CustomerReport!txtBoxName = "John Smith"


Ok, so let's say you want to create an invoice for a customer who has purchases several items. The data is in your qryReport or tblReport - if it contains data only for one customer, fine, but if it contains data for many customers, you might need to use Grouping to keep each customer's records together on one page (do this at View > Sorting and Grouping). You may also need to right-click the Detail bar and choose

Force New Page > After Section

to help keep each customer's items on one page.

 

[firoz.raj]

where should i place these codes in vb6 codewindow or ms access query section don't mind sir i as i am new in reporting of ms access .Kindly find attachment also there you will get
sir MR table as well as mr report.

Dim qDef as queryDef
On error resume next
set qDef = CurrentDB.QueryDefs("qryReport")
On error goto 0
if qDef is nothing then set qdef = CurrentDb.CreateQueryDef("qryReport")
qDef.SQL = "SELECT...."
 
Or, you can create a table like this:
DoCmd.RunSQL "SELECT * INTO tblForReport FROM table1....

[/quote]

 

[jal]

I am fairly new to Ms Access myself. I've never done front-end/backend. I've only done standalone mdb files. If you want to use Ms Access reporting, you will have to continue designing it in Ms Access just like you're doing.

Then in Vb6, if you like, add a reference to Ms Access and then do something like this (I don't know the exact code)

Dim Access as New Access.Application

Access.DoCmd.OpenReport "MR", Access.AcPreview

I gotta run...