[RESOLVED] Help with recordset result!

[honeybee]

I have 3 table

1. cat_Tbl = category table which consist of fields:
catNo = category number (primary key)
catDesc = category description

2. prod_Tbl = product table which consist of fields:
pIndex = index number of a product (primary key)
pText = product description

3 FM_Tbl = items ordered(in an order slip) table which consist of fields:
OSno = Order slip number
FMitemNo = item number
Pindex = index number of an ordered product
FMqty = quantity ordered

My apologies. I should have mentioned this earlier itself. The three tables you have mentioned should be related to each other. While the FM_Tbl and prod_Tbl are linked through the Pindex, there is no link between the prod_Tbl and the cat_Tbl (at least I don't see one in your post above). Ideally the prod_Tbl should have another column catNo which is a foreign key to the catNo (PK) column of your cat_Tbl table.

Now, I have an order slip record #12704. in that slip, i ordered several items, each has its unique item number. but when I run the code below, the result set exceeds the number of items i actually ordered. when I check the result, few items were duplicated. this is my code:

strSQL = "SELECT cat_Tbl.catDesc,FM_Tbl.FMitemNo,FM_Tbl.Pindex,FM_Tbl.FMqty,prod_Tbl.Ptext,prod_Tbl.Pum,prod_Tbl.Pprice 
FROM cat_Tbl,FM_Tbl,prod_Tbl WHERE 
(FM_Tbl.OSno = " & lngNum & ") AND FM_Tbl.Pindex=prod_Tbl.Pindex AND cat_Tbl.catNo=prod_Tbl.Pcat ORDER BY FM_Tbl.FMitemNo"
    
    ...

Is there a problem with the strSQL? What's wrong with my code?

What I want is to display just what I ordered; exact number of items, exact items. and just referencing to other tables its category and product description.

The query would then look something like this:

strSQL = "SELECT cat_Tbl.catDesc,FM_Tbl.FMitemNo,FM_Tbl.Pindex,FM_Tbl.FMqty,prod_Tbl.Ptext,prod_Tbl.Pum,prod_Tbl.Pprice 
FROM FM_Tbl inner join prod_Tbl On FM_Tbl.Pindex = prod_Tbl.pIndex INNER JOIN cat_Tbl on prod_Tbl.catNo = cat_Tbl.catNo 
WHERE (FM_Tbl.OSno = " & lngNum & ")"
    
    ...

.