Can MS-Access help - General question

[pzamory]

I have an Excel sheet with multiple tabs. Each tab contains a question in column A and the answer in column B.



I currently have a macro that will search all the tabs and display the cell that the search string finds "i.e. cereal".

If there are a hundred instances of the word "cereal", the search will results will display each tab/cell and can be a nightmare.

Can Access be used for this type of application?

I would like someone to be able to search for "cereal" and "healthy" or something similar and only show those results.

Thanks in advance.

Pete

[CJ_London]

Short answer is yes - by either applying criteria or a filter depending on overall requirement

you would need to provide more information to provide a more detailed answer such as an example of your workbook and the outcome required

be aware that access is not a bigger version of excel so chances are the structure you have in excel would be completely different in access

[pzamory]

Thanks for the quick reply. I may have a question on tab 1, column A - "What is a router" and the answer (or a link to an answer) in column b. I may also have the word router in another tab in the spreadsheet.

If someone in Access wants to search on the word "router", how can I show the question and answer/link for all occurances? I believe that I can import Excel into Access in separate tables.

[CJ_London]

with respect, that is just not enough - it's like you are asking your mechanic to fix your car, but the only thing you have told him is the colour.

I believe that I can import Excel into Access in separate tables.

why would you want separate tables if every tab has the same data format?

I really don't want to play 20 questions to gradually try to understand the reality of what you are trying to. The sort of things we need to know

what are your tab headings?
what are their significance?
how many questions to a tab?
what format are the answers? always text? sometimes numeric? might be a date?, might be yes/no?
are answers always one word? or could they be a part of a sentence? or an array of words?
etc
etc
etc

and from the output perspective lets say your user searches for 'life' - do you want 'life' only? or do you also want 'lifelike', 'lifetime', etc?

Don't just answer the above questions, provide enough example data to cover all eventualities

[pzamory]

Understood. Not sure I can upload an Excel sheet as an attachment which should clear things up. I am very grateful.

[mike60smart]

Hi Pete

It explains how to attach a file at he top of this page.

[pzamory]

Here is a sample Excel sheet. I added a new tab called MS_Access as this page has some sample questions and answers to search for on it.

I'd like Access to be able to search all of the tabs for keywords and present all the results. I hope this is helpful and again, I appreciate all the assistance very much.

Pete

[CJ_London]

So for example, if you searched for 'camel' it would find that in the sales tab, maybe elsewhere?

In access you would need two tables tblFunctions (the tab name) and tblData (the contents of each tab)

tblFunctions
FunctionPK - autonumber
FunctionName - text e.g. 'Side Gigs'
FunctionAbout - text e.g. 'Delivery and many other options to make money on the "side"'

tblData
DataPK - autonumber
FunctionFK - long (links to FunctionPK)
HLink - hyperlink
Description - text

You might want to split the Crypto sheet into it's two respective parts, or you might want an additional 'subheading' field

which this structure you could just use a continuous form based on tblData - have an unbound combo in the form header section based on tblFunctions from which you can select a specific 'tab' which can then filter on the selected tab (same basic function as the 'TOC' tab'. You can also have an unbound textbox from which you can filter based on a word or phrase - which is what you asked if it was possible.

The About and Disclaimer tabs could be some hidden labels containing the text made visible when required

What the filter algorithm would look like needs clarification from you - your example 'able to search for "cereal" and "healthy" or something similar and only show those results.' can be interpreted in different ways.

[ArviLaanemets]

I see no reason you have to switch to Access for such a trivial thing!

Instead of a bunch of sheets with questions and answers, have a single one, and define it as Table.
You can set various filter conditions for the Table - like to display only rows with certain text in column, or rows where text in column contains certain string, etc.;
You also can have any number of fields at top of page (above the Table - you have to left some number of free rows there, and freeze them and Table header row, so they remain always visible), where you can enter one or several filter conditions. Then you add additional columns to your Table - a column per condition field - and write formulas which return e.g. 0 or 1 depending column cell containing the text, matching with certain condition field or not. You can filter the Table on those calculated columns to display all rows where any selection of filtering conditions is True/1 or False/0. Etc.

[pzamory]

Thanks for all the comments. A bit beyond my headlights. The reason for Access is to host the database somewhere and have multiple users access it rather than having to download an Excel sheet. Again, grateful for the insight...... Pete

[Welshgasman]

We used to share an excel workbook all the time in work. Of course as we were updating it, it did get corrupted every now and again. Just reading would be fine though?