CODEWITHSUNDEEP
sqlms-accessms-access-2010multivalue
Kriss
Kriss

Reputation: 11

MS Access multivalued field query

MS Access Database 2010, following tables are created:

BazaNalaza columns:

ResponsibleUnits:

ResponsibleUnits contains following data:

ID  + Name      + CompanyLine
++++++++++++++++++++++++++++++
1   + ItOps     + Technology
2   + IdDev     + Technology
3   + CRM       + Marketing
4   + Legal     + ExCo
5   + ItDWH     + Technology

Following SQL query is working fine:

SELECT * FROM BazaNalaza
WHERE BazaNalaza.ResponsibleUnit.Value IN (1,2,5)

However, following code is NOT working:

SELECT * FROM BazaNalaza
WHERE BazaNalaza.ResponsibleUnit.Value IN (SELECT ID FROM ResponsibleUnits WHERE CompanyLine = "Technology")

Second query works only for the lines where values within bazaNalaza.ResponsibleUnit begins with 1, 2 or 5, while fields which contains e.g. (3,5) or (4,5) or (3,4,5) are not in the resultset.

Any ideas what's wrong?

Upvotes: 1

Views: 2470

Answers (2)

jae555
jae555

Reputation: 140

You really should not have multivalued attributes. It's violating first normal form.

This format won't work and you'll probably need another table to separate that column from BazaNalaza table.

try this

select disinct * from BazaNalaza inner join ResponsibleUnits on ResponsibleUnits.ID = BazaNalaza.ResponsibleUnit.Value

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123799

Queries with multi-value lookup fields can be a bit strange because the context of a given table can sometimes be at the "parent" level and sometimes be at the "child" level. However, this seems to work for your case:

SELECT b.* FROM BazaNalaza b
WHERE 
    EXISTS (
        SELECT ID, ResponsibleUnit.Value FROM BazaNalaza
        WHERE 
            ID=b.ID 
            AND
            ResponsibleUnit.Value IN (
                SELECT ID FROM ResponsibleUnits 
                WHERE CompanyLine = "Technology"
            )
    )

Upvotes: 1

Related Questions