run a query that is based on the results of another query in Access

user_15545582 0
Viewed 457

so I have a an Excel spreadsheet imported into Access as a table and I am wanting to search this table on different criteria via a search from. The search from needs to search for specific criteria using the AND function but also to perform a text search over multiple fields as an OR function (like a keyword type search)

I can run these 2 queries separately using 2 different buttons but I cant run them together as then I combine the AND and OR searches in one query the whole query runs as an OR so I don't the results I want.

In essence the result I want must have X and Y and F and "NADCAP"(NADCAP could be in up to 6 fields) I have created a union query which works but as an OR query rather than what I need....

Soooo. Is there a way for me to run the AND query first and use the results of this to then do the keyword search and find all records that match all criteria?

I think it could be done by maybe using a sub query or a derived table? or using the 1st query result as the FROM or SELECT fields?

I know the proper and best way to do this is to build a database with correct tables etc but I was hoping that there could be a work around via a query as I have zero experience in building databases really...just from what I've read on forums like this when I get stuck!!

my AND query

SELECT [CP data].[Company name], [CP data].[UK head office address], [CP data].[Other UK addessses], [CP data].[UK manufacturing operations], [CP data].[Company URL], [CP data].[Oil and Gas], [CP data].Renewables, [CP data].Aerospace, [CP data].[Medical & Pharmaceuticals], [CP data].Automotive, [CP data].Rail, [CP data].Chemical, [CP data].Accreditations1, [CP data].Accreditations2, [CP data].Accreditations3, [CP data].Accreditations4, [CP data].Accreditations5, [CP data].Accreditations6, [CP data].[Full Assembly], [CP data].Component, [CP data].OMR
FROM [CP data]
WHERE ((([CP data].[Full Assembly]) Like "*" & [Forms]![Search]![Full Assembly] & "*") AND (([CP data].Component) Like "*" & [Forms]![Search]![Component] & "*") AND (([CP data].OMR) Like "*" & [Forms]![Search]![OMR] & "*"));

My OR query

SELECT [CP data].[Company name], [CP data].[UK head office address], [CP data].[Other UK addessses], [CP data].[UK manufacturing operations], [CP data].[Company URL], [CP data].[Oil and Gas], [CP data].Renewables, [CP data].Aerospace, [CP data].[Medical & Pharmaceuticals], [CP data].Automotive, [CP data].Rail, [CP data].Chemical, [CP data].Accreditations1, [CP data].Accreditations2, [CP data].Accreditations3, [CP data].Accreditations4, [CP data].Accreditations5, [CP data].Accreditations6, [CP data].[Full Assembly], [CP data].Component, [CP data].OMR
FROM [CP data]
WHERE ((([CP data].Accreditations1)=[Forms]![Search]![Accreditation])) OR ((([CP data].Accreditations2)=[Forms]![Search]![Accreditation])) OR ((([CP data].Accreditations3)=[Forms]![Search]![Accreditation])) OR ((([CP data].Accreditations4)=[Forms]![Search]![Accreditation])) OR ((([CP data].Accreditations5)=[Forms]![Search]![Accreditation])) OR ((([CP data].Accreditations6)=[Forms]![Search]![Accreditation]));

Thanks

ms-access-2016 ms-access sql
2 Answers

Best resolution is to normalize data structure. If you don't you will continue to face aggravations like this.

Assuming there is a unique identifier field (or compound identifier), consider:

SELECT * FROM [CP data]
WHERE ((([CP data].[Full Assembly]) Like "*" & [Forms]![Search]![Full Assembly] & "*") 
AND (([CP data].Component) Like "*" & [Forms]![Search]![Component] & "*") 
AND (([CP data].OMR) Like "*" & [Forms]![Search]![OMR] & "*")) 
AND ID IN (SELECT ID FROM [CP data]
           WHERE ((([CP data].Accreditations1)=[Forms]![Search]![Accreditation])) 
           OR ((([CP data].Accreditations2)=[Forms]![Search]![Accreditation])) 
           OR ((([CP data].Accreditations3)=[Forms]![Search]![Accreditation])) 
           OR ((([CP data].Accreditations4)=[Forms]![Search]![Accreditation])) 
           OR ((([CP data].Accreditations5)=[Forms]![Search]![Accreditation])) 
           OR ((([CP data].Accreditations6)=[Forms]![Search]![Accreditation])));

Another approach utilizes UNION query to rearrange fields to normalized structure - still need unique identifier field(s).

SELECT ID, Accreditations1 AS Accreditation, 1 AS Src FROM [CP data]
UNION SELECT ID, Accreditations2, 2 FROM [CP data]
UNION SELECT ID, Accreditations3, 3 FROM [CP data]
UNION SELECT ID, Accreditations4, 4 FROM [CP data]
UNION SELECT ID, Accreditations5, 5 FROM [CP data]
UNION SELECT ID, Accreditations6, 6 FROM [CP data];

Join that query to [CP data] and apply filter criteria. Be aware this will result in a non-editable dataset. Or reference this query object as the subquery of IN() clause as shown in first example.
AND ID IN (SELECT ID FROM QueryUNION WHERE Accreditation LIKE "*" & [Forms]![Search]![Accreditation] & "*"). The result may still be a non-editable dataset - I've never tested.

Strongly advise not to use spaces nor punctuation/special characters in naming convention.

user_76071900

I agree with June that normalizing the database structure is by far the better answer. But if you're just looking for a single query which includes the selection criteria of both the AND query and the OR query above, here it is.

SELECT      [CP data].[Company name],
            [CP data].[UK head office address],
            [CP data].[Other UK addessses],
            [CP data].[UK manufacturing operations],
            [CP data].[Company URL],
            [CP data].[Oil and Gas],
            [CP data].Renewables,
            [CP data].Aerospace,
            [CP data].[Medical & Pharmaceuticals],
            [CP data].Automotive,
            [CP data].Rail,
            [CP data].Chemical,
            [CP data].Accreditations1,
            [CP data].Accreditations2,
            [CP data].Accreditations3,
            [CP data].Accreditations4,
            [CP data].Accreditations5,
            [CP data].Accreditations6,
            [CP data].[Full Assembly],
            [CP data].Component,
            [CP data].OMR
FROM        [CP data]
WHERE       ((([CP data].[Full Assembly]) Like "*" & [Forms]![Search]![Full Assembly] & "*") 
AND         (([CP data].Component) Like "*" & [Forms]![Search]![Component] & "*") 
AND         (([CP data].OMR) Like "*" & [Forms]![Search]![OMR] & "*"));
AND         (
                ((([CP data].Accreditations1)=[Forms]![Search]![Accreditation])) 
    OR          ((([CP data].Accreditations2)=[Forms]![Search]![Accreditation])) 
    OR          ((([CP data].Accreditations3)=[Forms]![Search]![Accreditation])) 
    OR          ((([CP data].Accreditations4)=[Forms]![Search]![Accreditation])) 
    OR          ((([CP data].Accreditations5)=[Forms]![Search]![Accreditation])) 
    OR          ((([CP data].Accreditations6)=[Forms]![Search]![Accreditation]))
)
user_38170680
Related