Inner Join syntax

[Cowboy]

Okay, so I want to join two tables based on a pk and fk...but only where a field in the first table is equal to one of my variables.

I know the INNER JOIN comes before the WHERE, but I'm confused as to where to put the ON and the WHERE.

Here's what I have:

[INDENT] strSQL = "SELECT pkManColorSizesID, fkSizeID, txtSize " & _ 
" FROM tblManufColorSizes INNER JOIN tblSizes" & _
" ON tblManufColorSizes.fkSizeID=tblSizes.pkSizeID " & _
" WHERE tblManufColorSizes.fkManColorsID = " & varColorID
[/INDENT]

Is this correct?


Thanks!!

 

[pbaldy]

Is this correct?

Paul's rule-of-thumb number 6: if it works, it's correct...if it doesn't work, it's not correct.

But offhand it does look correct.

 

[Cowboy]

Well yes...but I haven't run it yet. Basically it's only part of larger function to populate fields based on current inventory on hand.

So until I write ALL the code, I won't know if it works yet.

Then again, I guess I could test it with dummy data...

Thanks for the reply though!

 

[Cowboy]

Here's what I'm trying to do...

strSQL = "SELECT pkManColorSizesID, fkSizeID, txtSize " & _
                " FROM tblManufColorSizes INNER JOIN tblSizes" & _
                " ON tblManufColorSizes.fkSizeID=tblSizes.pkSizeID" & _
                " WHERE tblManufColorSizes.fkManColorsID = " & varColorID
    Set rs = db.OpenRecordset(strSQL)
    
    Dim colSizes As Collection
    With rs
        .MoveFirst
        While Not .EOF
            colSizes.Add !pkManColorSizesID, !txtSize
        .MoveNext
        Wend
    End With

Am I missing anything?

 

[pbaldy]

The recordset part looks okay (though you don't need the .MoveFirst, and it will error with an empty recordset). I'm not a big user of classes, but I think that part's okay. What are you going to use it for? I'd be more likely to set a combo row source to that SQL, or something along those lines.

 

[Cowboy]

I'm using it to keep track of the ID numbers for specific color sizes to search for inventory on hand.

Basically you search by a manufacturer's color number, and we keep 8 different sizes on hand, so this will populate a form with the current values in text box fields so the user can update those counts for an inventory stock take.

After the query in the last post, I still need to write the queries to find the add and subtract transactions for each of the color size IDs since each color size ID's last stock take - then some simple math and voila: inventory on hand.

Then the form's fields get those numbers populated in them, the user erases the numbers he/she needs to (depending on which ones changed) and inputs new numbers...clicks an Update button - and a new stock take transaction gets recorded!

In theory anyway.