How to display or query this?

TABLE 1 ITEMNO, ITEMNAME, DESCRIPT, SUPLYR,
NOTE: (ITEMNO IS A FOREIGN KEY)
TABLE 2 ITEMNO, ITEMID, QTY
NOTE:(ITEMNO AND ITEMID IS A FOREIGN FROM TABLE 1 IN ITEMNO)

SO MY SAVE / INSERT QUERY KINDA LIKE THIS

Code:

---

Insert Into STRUCTURE(ITEMNO, ITEMID, QTY) values(" & TxtItemNu.Text & "," & TxtFlowerID.Text & "," & CboQuantity.Text & ")

---

AND MY FLEXGRID DISPLAY IS LIKE THIS

Code:

---

Select ITEMNO, ITEMID, QTY from STRUCTURE

---

BUT I LIKE TO DISPLAY LIKE THIS
Select ITEMNO, ITEMNAME, QTY WHERE ORDER BY ITEMNO
(THE ITEMNAME IS COMING FROM TABLE1)

Code:

---

Function PopulateFlexGrid()
   
    Dim rs As New ADODB.Recordset
    Dim sqlstr As String
        sqlstr = "Select STR_ITEMNO, ITEM_NAME, STR_QTY From STRUCTURE,ITEM WHERE (Structure.STR_FLOWER = ITEM.ITEM_NAME )"
        'Select STR_ITEMNO,STR_FLOWER,STR_QTY from STRUCTURE  "
        rs.Open sqlstr, Conn, adOpenForwardOnly, adLockReadOnly
           
            If Not rs.EOF Then
                MSHItemStructure.FixedCols = 0
                Set MSHItemStructure.Recordset = rs
                With MSHItemStructure
                    FormatString = "ITEM NUMBER|FLOWER ID|QUANTITY"
               
                    .ColWidth(0) = 1450
                    .ColWidth(1) = 2200
                    .ColWidth(2) = 1300
               
                   
                      .AllowUserResizing = flexResizeColumns
                      .SelectionMode = flexSelectionByRow
                     
            End With
                MSHItemStructure.Enabled = True
            Else
               
                MSHItemStructure.FixedCols = 0
                With MSHItemStructure
                    FormatString = "ITEM NUMBER|FLOWER ID|QUANTITY"
               
                    .ColWidth(0) = 1000
                    .ColWidth(1) = 1000
                    .ColWidth(2) = 1000
               
               
'              MSHItemStructure.TextMatrix(1, 0) = ""
'              MSHItemStructure.TextMatrix(1, 1) = ""
'              MSHItemStructure.TextMatrix(1, 2) = ""
               
                End With
                MSHItemStructure.Enabled = False
            End If
           
End Function

---

IT DONT DISPLAY ANY THING IN FLEXGRID,

SORRY, BUT I NEED HELP WITH THIS I REALLY NEED THIS CODE...
THANKS...

I think you just want:-

Code:

---

Select S.ItemNo, I.ItemName, S.ItemID, S.QTY
From Structure S
Inner Join Table1 I
  ON S.ItemNo = I.ItemNo

---

check my response to your other post [now merged into this thread] for the correct query. You shoould really be using your foreign key (ItemNo) to join the tables rather than a description field.

Quote:

---

Originally Posted by funkydexter

i think you just want:-

Code:

---

select s.itemno, i.itemname, s.itemid, s.qty
from structure s
inner join table1 i
  on s.itemno = i.itemno

---

---

yeah almost but,,,,, it show the item name of column1 it should be the item name of column 2 sir...

Thanks anyway.. But i need more little help

I'm sorry but I don't know what that means. It's showing the name of the item, taken from the item table. Can you be clearer in what you're trying to achieve?

From looking at your posts I'm not sure I'm understanding your data structure properly. I assume the Item No in your structure table is a foreign key to the Item No in your Item table. Is that right? If not can you explain clearly how your two tables are related.

Also, you've now created 3 separate threads related to this query (actually one's about inserting rather than deleting but it's all part of the same discussion I think). Please could you not do that. It makes it hard pull together all the details of what you're asking and therefore hard to give you an answer. Generally we'd ask that you only create a single thread for a question and only create a second thread if you've got a question that's substantially different. Thanks.

Duplicate threads merged - please post each question (or variation of it) only once.

There are many reasons (including what FunkyDexter mentioned) why a single thread is better for you and the rest of us.