How to use a Junction table

[gwboolean]

I use an Access database (obviously since I am here). However, I use Visual Basic for all of my forms, applications and code to work with the database. So I have two tables with many-to-many relationships using a junction table to link them:





So what I want to do is this. I have a form that is displaying a single record from tblItemMaster (a parts table). Additionally, I have a button on the form that when clicked should display a list of all records from tblSourceMaster (a vendor/supplier table) that is related to the displayed record from tblItemMaster. The junction table lnkItemVendor would be the bridge to achieve this.

I am guessing that it requires a query written into the Button_Click() event to achieve this. However, my query skills are not even close to the level needed to do this. So am I right that a SQL query is the tool and if so how does one setup a SQL query that uses the junction table to pull records from tblSourceMaster? Or could you point me to the information I could research to figure this out?

[June7]

Use Access query builder to help get SQL syntax correct.

Really just have to query the junction table.

SELECT intSiTechVendorID FROM lnkItemVendor WHERE intSiTechItemID = [intSiTechID];

But if you want to also see attributes from tblSourceMaster then include that table in the query as well by joining on the key fields.

However, how does writing a query in button click event accomplish what you want? The query probably needs to be the source for something - combobox, form, report.

[orange]

Hmmm? Not sure where to start. Typically with a many to many relationship between 2 tables, you add a junction table.
The junction table gets a compound primary key consisting of the Primary keys from each of your tables in the many to many.

For example, Students and Courses.

A Student can take Many Courses and a Course can be taken by Many Students

tblStudent(studentID, studentName, studentPhone...)
tblCourse(courseID,CourseName,CourseDescription...))

junction table tblStudentTakesCourse(studentID,CourseID) each record uniquely identifies student and Course taken (and may contain other info ..semester)

1.....................>.Many____Many<............. ............1
tblStudent----->tblStudentTakesCourse<-----tblCourse

[gwboolean]

June,

Thanks. It never occurred to me that the query would be written in the junction table. But then again while I can code the hell out of VB, I am a neophyte with SQL queries and am only able to work very simple ones.
I am going to give this a try immediately. Let you know how it works for me.

[gwboolean]

June,

The button event is because I am using a container, holding a datagridview, that is not visible until the button is clicked. I just figured that is where the call for the query should be as well.

[gwboolean]

June, I have been playing with your suggestion and do not see how it gets me where I want to go. It appears to me that your suggestion only results in a list in the JOIN table. I do not see how that gets me the list from tblSourceMaster. Here is another approach I am looking at.

SELECT DISTINCT tblItemMaster.intSiTechID
FROM lnkItemVendor
JOIN tblSourceMaster ON lnkItemVendor.intSiTechItemID = tblItemMaster.intSiTechID

This should work, but I keep getting a syntax error in the FROM clause. But it seems like this should be the way to...... if it worked.

[gwboolean]

OK, here is where I am at and what is almost, but not quite, working for me:

SELECT tblItemMaster.strTitle, tblSourceMaster.strVendorName
FROM ((lnkItemVendor
INNER JOIN tblItemMaster ON tblItemMaster.intSiTech = lnkItemVendor.intSiTechItemID)
INNER JOIN tblSourceMaster ON tblSourceMaster.intSiTech = lnkItemVendor.intSiTechVendorID)

I can get this to parse, but something is still not right. I do not see anywhere that gives an actual value to look for. i.e. there is no equivalent of a WHERE clause in this.

[June7]

Did you use Access query builder and do as I suggested? I said build query that links tblSourceMaster and lnkItemVendor and use a WHERE clause:

WHERE intSiTechItemID = [intSiTechID]

[intSiTechID] is reference to field on the form

In VBA can expand the reference with Me!intSiTechID and use concatenation to build SQL string.

In query object it would be like Forms!formname![intSiTechID].

That should give you a list of tblSourceMaster records that are associated with the specific [intSiTechID].

[gwboolean]

June,

Gottcha. I will give that a try. Also, here is where I am with the approach I have been using:

SELECT tblSourceMaster.strSiTechID, tblSourceMaster.strVendorName
FROM ((lnkItemVendor
INNER JOIN tblItemMaster ON tblItemMaster.intSiTech = lnkItemVendor.intSiTechItemID)
INNER JOIN tblSourceMaster ON tblSourceMaster.intSiTech = lnkItemVendor.intSiTechVendorID)
WHERE (lnkItemVendor.intSiTechItemID = ?)

However, I am getting this message and pulling my hair out. Damn good thing I am not bald. But that might soon change. I can almost smell what it wrong, but just can't quite figure it out.

[June7]

Put the ? within [] or better, use informative text: [enter vendor ID]. But why not referencing the field on form?

Did you use Access query builder? I did and JOIN clause structure is different.

Exactly where is that SQL statement? If it is constructed in VBA, post the actual procedure code.

[John_G]

There is an "ID" missing here: INNER JOIN tblSourceMaster ON tblSourceMaster.intSiTechID = lnkItemVendor.intSiTechVendorID)

I think tblSourceMaster.strSiTechID should be tblSourceMaster.intSiTechID, unless it is not showing in the diagram above.

In your diagram, are you sure lnkItemVendor.intSiTechVendorID should be linked to tblSourceMaster.intSiTechID? Wouldn't it make morse sense to link it to
tblSourceMaster.strVendorID?

[gwboolean]

June,

I am not using VBA, I am using VB from a Visual Studio IDE. I have used VBA in the past and know that there are some differences in how things get done. I am not completely clear on your questions and explanations, but I can tell you the structure of what I have done.

The SQL query resides in the Junction table in the dataset. The query is called from within a button event of a visual basic form and goes something like this (exactly like this):

LnkItemVendorTableAdapter.FillBySiTechItemID(Me._M asterBase3_0ItemMasterDataSet.lnkItemVendor, glbintIDNum)

I do know that the error resides at the point of the joins I have with the parameters after the = sign. I cannot understand why there is no value there.

Could you perhaps show me the structure of your complete query. I am unable to discern what it is you are doing from the partial information you have provided. Not finding fault, I just don't quite get it.

[gwboolean]

John,

What you state sounds very plausible. However, I am a little dyslexic and am unable to find where, in the query I used tblSourceMaster.strSiTechID. Below is the verbatim query. I do reference a string (tblSourceMasterstrVendirID) in the SELECT statement.

SELECT tblSourceMaster.intSiTechID, tblSourceMaster.strVendorName,tblSourceMaster.strV endorID
FROM ((lnkItemVendor
INNER JOIN tblItemMaster ON tblItemMaster.intSiTech = lnkItemVendor.intSiTechItemID)
INNER JOIN tblSourceMaster ON tblSourceMaster.intSiTech = lnkItemVendor.intSiTechVendorID)
WHERE (lnkItemVendor.intSiTechItemID = ?

So here is what the query is supposed to do. I have an ID value, say 100000000 that I get from a specific record from tblItemMaster.intSiTechID......Damn, thanks to you I just solved the problem and it is my dyslexia. If you look at the inner joins they are not correct. Both should show intSiTechID instead of intSiTech. I can neither type or read. Anyway, with that correction it works exactly as it is supposed to do. Thanks for the help John.

[June7]

Did you use Access query builder to construct SQL statement?

I don't understand what you mean by "SQL query resides in the Junction table in the dataset". How does a query reside in a table?

There is no value after the WHERE clause = sign because you aren't providing one.

The WHERE clause can have explicit value, such as: WHERE lnkItemVendor.intSiTechItemID = 10

or dynamic parameter in query object such as: WHERE lnkItemVendor.intSiTechItemID = [enter vendor ID]

That parameter will trigger a popup prompting user for input.

A dynamic parameter can reference a field or control on form to provide input: WHERE lnkItemVendor.intSiTechItemID = Forms!formname!fieldname

If you are building SQL string in code, the dynamic parameter reference will have to be concatenated to pass value of current record on form, such as:

strSQL = "SELECT * FROM tableORquery WHERE field = " & Me!fieldname

EDIT: See now you have solved issue. Congratulations!

[gwboolean]

I did use the query builder to test the query, but I did not use it to write the query.

If you look above you can see the three tables involved. That is from access. In the dataset that is in my Visual Basic the tables look like this:



At the bottom of each table you can see queries that I use. In this case the query I am using is the highlighted one in lnkItemVendor. This query is called from a button event in the code of my visual basic form to fill a datagridview:
'Find vendors/Suppliers
LnkItemVendorTableAdapter.FillBySiTechItemID(Me._M asterBase3_0ItemMasterDataSet.lnkItemVendor, glbintIDNum)

The original query was like this:

SELECT tblSourceMaster.intSiTechID, tblSourceMaster.strVendorName,tblSourceMaster.strV endorID
FROM ((lnkItemVendor
INNER JOIN tblItemMaster ON tblItemMaster.intSiTech = lnkItemVendor.intSiTechItemID)
INNER JOIN tblSourceMaster ON tblSourceMaster.intSiTech = lnkItemVendor.intSiTechVendorID)
WHERE (lnkItemVendor.intSiTechItemID = ?)

look at the field names in the ON parameter of the two joins. They are both intSiTech.

Here is the query that works and provides the required results:

SELECT tblSourceMaster.intSiTechID, tblSourceMaster.strVendorName,tblSourceMaster.strV endorID
FROM ((lnkItemVendor
INNER JOIN tblItemMaster ON tblItemMaster.intSiTechID = lnkItemVendor.intSiTechItemID)
INNER JOIN tblSourceMaster ON tblSourceMaster.intSiTechID = lnkItemVendor.intSiTechVendorID)
WHERE (lnkItemVendor.intSiTechItemID = ?)

In this case, the field names in both of the ON parameters of the joins is intSiTechID.

This is what works. As far as writing the query into the code on the form, I would love to do that instead of putting a query into a table in the dataset and calling it, but I have not yet learned how that is done. It appears that what you are suggesting is to do something like that, but I would need a little more information from you on how to achieve that.
I have tried and know that it takes more than just:

Using cmd As New OleDbCommand With {.Connection = cn}
cmd.CommandText = <SQL>Query Code</SQL>.Value