Inline Query using Inner Join and Group By

[MisterChris]

Hellooo,

I have a bit of an issue regarding an inline query in Access 2010 - I am currently using INNER JOIN and GROUP BY to narrow down the RowSource of a combo box based on the value selected in the active combo box where the code is located "onClick".

The value being selected is a StoreID,
This ID is matched against the AssetRegister to find all group names,
The GroupID and GroupName are then retrieved from the AssetGroup table,
then finally the list is grouped on the Group ID and Group Name.

I have tested this query in SQL Server Management Studio and it works without any issues, however when I use it as an inline query to adjust the row source of the combo box it returns no values.

Here is the SQL query:

SELECT AssetGroup.ID, AssetGroup.GroupName
FROM AssetGroup
INNER JOIN
(SELECT AssetRegister.AssetGroup, AssetRegister.Store
FROM AssetRegister
WHERE AssetRegister.Store=7) AS ar ON (ar.AssetGroup = AssetGroup.ID)
INNER JOIN Store ON Store.ID = ar.Store
GROUP BY AssetGroup.ID, AssetGroup.GroupName ORDER BY AssetGroup.GroupName

And here is the inline query applied to the RowSource:

AssetGroup.RowSource = "SELECT AssetGroup.ID, AssetGroup.GroupName FROM AssetGroup INNER JOIN " _
                    & "(SELECT AssetRegister.AssetGroup, AssetRegister.Store FROM AssetRegister WHERE AssetRegister.Store=" & StoreID & ") AS ar ON (ar.AssetGroup = AssetGroup.ID) " _
                    & "INNER JOIN Store ON Store.ID = ar.Store GROUP BY AssetGroup.ID, AssetGroup.GroupName ORDER BY AssetGroup.GroupName"
AssetGroup.Requery

It is possible that this has to do with what is acceptable within Access VB6 script but I don't have the experience to locate the issue.

Please may somebody assist me with this query?
Thank you in advance.

 

[pr2-eugin]

Hello MisterChris, Welcome to AWF.

If you copy the RowSource of the AssetGroup as you have right now into a Normal SQL Query window, can you get the required result? If there are any errors with the join they will prompt you.. Trace the steps back..

Also, instead of using OnClick, try the AfterUpdate event of the ComboBox..

 

[MisterChris]

Hello pr2-eugin

Thank you for the quick response and the warm welcome.

I have tested the query as it is in a Normal SQL Query window and it returns the correct result - ID:158 and GroupName:XPUb

The original query attached to the combo box is as seen below:

SELECT Store.ID, Store.StoreNo, Store.StoreName FROM Store;

The Column Count is 3;
and the Column Widths are "0cm;2cm;6cm" (not terribly relevant but just in case )

I tried adding the code to the AfterUpdate event and alas it was unsuccessful.

 

[pr2-eugin]

Hmmm, that is quiet strange.. How about a bit of Debugging.. Try..

Dim sampSQL As String
sampSQL = "SELECT AssetGroup.ID, AssetGroup.GroupName FROM AssetGroup INNER JOIN " _
          & "(SELECT AssetRegister.AssetGroup, AssetRegister.Store FROM AssetRegister WHERE AssetRegister.Store=" & StoreID & ") AS ar ON (ar.AssetGroup = AssetGroup.ID) " _
          & "INNER JOIN Store ON Store.ID = ar.Store GROUP BY AssetGroup.ID, AssetGroup.GroupName ORDER BY AssetGroup.GroupName"
Debug.Print sampSQL
AssetGroup.RowSource = sampSQL
AssetGroup.Requery

In the immediate window, see if the Query generated is correct..

 

[MisterChris]

Okay I have fixed the issue, I just used the SQL Query builder in Access to generate an "Access compliant" query.

Thank you for your time and effort pr2-eugin, I sincerely appreciate it.

If you are curious about the Access generated query, it is as seen below:

sSQL = "SELECT AssetGroup.ID, AssetGroup.GroupName, Store.ID " _
                & "FROM (AssetRegister INNER JOIN Store ON AssetRegister.Store = Store.ID) INNER JOIN AssetGroup ON AssetRegister.AssetGroup = AssetGroup.ID " _
                & "WHERE (((AssetGroup.[ItemGroup]) = 26) And ((Store.ID) = " & StoreID & ")) " _
                & "GROUP BY AssetGroup.ID, AssetGroup.GroupName, Store.ID ORDER BY AssetGroup.GroupName"

Thanks again!

Have an awesome day further!