2

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

renren
  • 21
  • 1
  • You might want to add the tag "ms-access-forms" to this question. – Nicholas Hunter Apr 03 '21 at 16:24
  • I would suggest you create a query object using the "AND" SQL and call it Query1. You could run that query from the button on the form. Then create a second query object using the "OR" SQL but change the FROM clause to read "FROM Query1 As [CP Data]". Call it Query2. You can execute Query2 from the second button on the form. That might work but I don't know enough about forms to know how to embed the form control values in the where clause of a query object. – Nicholas Hunter Apr 03 '21 at 16:31
  • Thank you!! I was thinking of this approach but wasn't sure on how to write the query. The "FROM Query1 As [CP Data] is the bit I couldn't work out. This will work I think until I can work out how to run both at once. High 5's – renren Apr 03 '21 at 16:51
  • As you already recognize, should normalize data structure. Instead of multiple accreditation fields, have a related dependent table where each accreditation is a record. A UNION query can rearrange fields to a normalized structure. That query could be joined to table for search. That's the only work-around I know of to 'run both at once'. – June7 Apr 03 '21 at 17:58
  • That might have to be the case as I have just tested Nicholas' idea and the 2nd query just came back blank when it shouldn't – renren Apr 03 '21 at 18:08
  • Do you need to replace all the references to [CP Data] to Query1 AS [CP Data] e.g in the Where section as well.? I'm just not sure why this doesn't work, I know its not conventional in a database sense but surely you should be able to search by X and then use the results of X to search by Y – renren Apr 03 '21 at 18:13
  • Actually, I may have thought of another 'work-around' - see answer. – June7 Apr 03 '21 at 18:19
  • I have just got @NicholasHunter code to work!!! Its not that great having to push 2 buttons to get to the result but at least its working and im getting the result I need. – renren Apr 03 '21 at 18:43
  • You don't have to push two buttons. Running Query2 as I described above is equivalent to running both your original queries at the same time. Both selection criteria will be applied. For some reason, I thought you wanted to keep both queries separate. If that is not the case, you can combine them both into one query just by adding the OR query where clause in parens to the AND query where clause. – Nicholas Hunter Apr 03 '21 at 19:03

2 Answers2

0

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.

June7
  • 19,874
  • 8
  • 24
  • 34
  • Thank you for the above example I will take a look at the but my mind does get confused with how to link the different accreditations to the companies that I have in the list (I will have to do this with various other criteria). I understand the method behind a primary key to link the data from different tables together but then it confuses be further with how to link these accreditations to the companies as they have have more than one. I believe this is a many to many relationship. – renren Apr 03 '21 at 18:32
  • This is a vertical learning curve to be able to create a working database but the data I will be using will not be changing anytime soon so the option of having a query run from the result of another query to further filter to me seems the logical option given the time it would take to able to build a database from scratch. TBH I'm not really after a database, more using access as a way of searching a table better than using Excel Ta – renren Apr 03 '21 at 18:32
0

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]))
)
Nicholas Hunter
  • 1,791
  • 1
  • 11
  • 14