Queries :: Need Query For Category Keywords Using Tables
Nov 21, 2014
I have a database with hundreds of records in a main table called tblPARTS which has three fields - Part Number, Part Description and Category. I have another table, tblKEYWORDS, with two fields; a field called KEYWORDS which has words like "cable", "bracket", and "relay". The table has a second field called CATEGORY. The CATEGORY field has either the word ELECTRICAL or MECHANICAL associated with each of the KEYWORDS. So, for example, "bracket" would have "MECHANICAL" as its associated category. There are dozens of these entries in the KEYWORD table.
The problem: I need to auto fill the parts table (tblPARTS) with the correct category by looking at the DESCRIPTION field in the PARTS table which is made of several words, e.g. "STAINLESS STEEL BRACKET - 3" X 2" ". I need to find the word BRACKET and fill in the CATEGORY field in the parts table automatically via a query. There are several hundred parts in the main table.
I can do this easily using a standard update query, but it is limited to 10 IFF statements and there will be many more than this. Can I do this in VBA and if so, where do I start? I am an intermediate user of ACCESS.
I am just starting out in access. I have a table that has different vehicle models. There is a "category" field that has vehicles under boats, cars, planes, heli, etc... How do I make a query that will only show the different categories that are used? So I would just want the category field showing one of each category that exists and not multiple records with the same category.
Basically trying to create a chart through a Query.
Table is as follow:
Applebrand Date Volume per week Category ------------ ------ -------------------- -----------
I am trying to display the aggregated volume in a chart. The volumes can change depending on the demand from the buyers and its always the latest volume for the specific applebrand that is in play.
Example
Apple A 20150101 10 Retail Apple B 20150202 100 Restaurants Apple B 20150303 200 Retail Apple A 20150404 50 Retail
The chart i would like to see is a aggregated volume for category "Retail" displaying a line from 10 to 210 to 250.
Instead my chart displays 10 to 200 to 50
Is there a way a solve this or should i use a different approach?
I currently use forms for adding new pupils, and updating pupil subjects/arrangements/levels.
I also have forms to search for specific pupils, and to create lists for faculty heads showing which pupils are taking subjects within their faculty and the arrangements we expect to provide.
I use the forms to run queries, which can then output to reports for printing.
Where I am currently having an issue is the faculty field in the tbl-SubjectLevelArrangements. (If I didn't have to report to faculty heads I would just leave it out, but management will insist.)
Currently I have a form with dropdowns for adding subject, faculty, level and arrangement manually. This is acceptable for the subject, level and arrangement because they are completely interchangeable and dynamic throughout the academic year as pupils may drop down a level, or change the type of arrangement they require.
However as faculties are inextricably linked to subjects, I want to remove the possibility of human error. i.e. when a user (me) chooses either geography, history, or RE, then the faculty will always be Humanities, likewise if the user chooses French, German, or Spanish, then the faculty can only be Modern Languages etc.
I'm convinced there must be a very simple way to ensure that the faculty field prefills based on the limited keywords available in the subject field, but I just cannot figure it out.
i have a "library-database" with a query that allows you to search books by author/title/category/... in the title-field i have the following criteria: Like "*" & [enter keyword] & "*"
eg. i have two books "Roman and Greek literature" and "Roman literature"
if i type "Roman literature" in the prompt box it'll only give the second book because the criteria doesnt split up the keywordstring.
Is it possible to get both books as result of the query with only one promptbox?
I would like to make an Access database for my collection of scientific papers. From that database I should be able to find articles using descriptive keywords. The problem is I haven’t figured out how I could search for the articles needed using multiple keywords at the same time (eg. papers that fill the criteria: <keyword a> AND <keyword b> AND <keyword c>).
What kind of tables and queries I have to create to do that?
I managed to create a form that is able to search keywords on Access 2007. However, the keyword search must be exact to what is contained in the table.
For example, I stored "red blue yellow" into the database, and I can only search "red blue yellow", "red blue" or "blue yellow" in order to call that data.
How do I make it such that I am able to search "blue red yellow", "yellow blue red" or "red yellow blue"?
These are my current codes:
Code: Private Sub txtSearch_AfterUpdate() Dim strWhere As String Dim strWord As String Dim varKeywords As Variant Dim i As Integer Dim IngLen As Long
Hey guys it's been a long time since i've been here but i again need your help
my job asked me if it's possible to have keywords highlighted in search results in report am i clear? meaning, when you search for keywords in one of the fields, and then your results come out in the report, can they be selected, like in Word, or in searches on the Internet, like when doing a search on Monster, all your keywords will come out in red, that way you can easily read the results
Currently, I am trying to search a memo field on a table by having the user enter keywords on a form (up to 10 keywords can be entered). It works when just one keyword is entered, however it doesn't work if more than one is entered. How do I account for more than one keyword being entered to search the memo field and return the recors where any of the terms show up in that field. Each one of the keywords [KW1] ...etc are in a separate unbound box.
Current formula looks as follows: Like "*" & [Forms]![KeywordInputForm]![KW1] & "*" OR "*" & [Forms]![KeywordInputForm]![KW2] & "*" OR "*" & [Forms]![KeywordInputForm]![KW3] & "*" OR "*" & [Forms]![KeywordInputForm]![KW4] & "*" OR "*" & [Forms]![KeywordInputForm]![KW5] & "*" OR "*" & [Forms]![KeywordInputForm]![KW6] & "*" OR "*" & [Forms]![KeywordInputForm]![KW7] & "*" OR "*" & [Forms]![KeywordInputForm]![KW8] & "*" OR "*" & "*" & [Forms]![KeywordInputForm]![KW9] & "*" OR "*" & [Forms]![KeywordInputForm]![KW10] & "*"
I'm looking for some type of custom function that will search a specified column for any keywords listed inside another table.
I can run a query on each keyword individually, however there are 50 and it takes a long time each time I do it. I was hoping to write in a function for that column and it would just select all records that match.
These would all need to be a "like" with an " * " on each end of the word.
With SQL it would look something like:
Code: select a.address1 from main_tbl as a where a.address1 like '* north *' or a.address1 like '* park *' or a.address1 like '* south *';
I just want it to read each of the table values instead of hard coding them and the column name would be the function name so it can be used in any column I specify. I'm just not sure how to incorporate this into a custom function.
I want a drop down box where you select the category Downtape or bonds, then another drop down box showing all the possible choices for bonds if you selected bonds, but wont show you any of the down tape options.
I have been trying to work out the relationships here and been getting in a muddle so any help would be much appreicated.
Hi, I am trying to set up a form so that i can view the employees at my workplace by the scheme they are in. I have tried a number of things with no luck. If anyone could help me i wold really appreciate it. An example would be a god send. Thanks alot.
I have a table of categories with the columns: CatID | CatName | CatParentID
This way I'd like the ability to have unlimited subcategories.
My question is, how do I create a form that will let me enter the CatName with an autogenerated CatID, then enter a subcategory and select the first category as it's parent?
In a form, I am using two comboboxes per record (i.e. when you add a new record, two more comboboxes for that record appear).
My goal is to have the second combobox show entries specific to what the first combobox was, with certain caveats (see below).
My current approach is using three tables - tblTypes, tblOptions, and tblEntries. tblTypes would store the different types of entries. Each of the types in tblTypes would contain options depending on its ID.
Basically, the first combobox will list the data in tblEntries and the second combobox will list the data in tblOptions, depending on what "Entry" was selected. New "Entry's" for the first combobox would be created in tblEntries, where the ID in this table would indicate what "Options" are available for that entry. As shown in the sample table below, I want to allow more than one entry with the same "ID" (i.e. there could be more than one entry with the same options, but the entry name would be different.)
A sample of what the tables would look like is below:
tblTypes --------- ID |Type 1 TypeA 2 TypeB 3 TypeC
Is this approach sound? If so, how would I create the comboboxes to use with this set of data? I tried doing so but ran into a certain problem - that is, the first combobox doesn't allow you to "select" entries with the same ID - it lets you select the first one, but no other... (using a query that simply selects all of the entries and its associated ID - I used the ID to populate the second textbox appropriately.
If this approach sounds unreasonable, how would you approach it? And after creating the tables, how would you go about setting up the combo boxes?
I am tring to generate a report or form, its generated by a combo box, which list all the categories of the products, when the category is clicked on it will generate the report or form showing all the products for that particular category. Can anyone point me in the right direction? Any help would be appreciated.
I am working on a small database for a school to track their live and silent auction. Currently I have a table set up as follows.
Silent Auction table : Item Number Short Item Description Long Item Description there are more fields that I am not interested in right now.
Mainly what I am after right now is to do some decent reporting for generating the catalog and posting the silent auction items on the web site for viewing before the night of the event.
I need to generate a report that contains the Item Number, short and long description. I need to group on Category which corresponds to the item numbers. Example:
Item Number 100 - 110 = Class Projects 110 -199 = Live Auction Items 200 - 299 = Sports and Recreation 300 - 349 = Entertainment and so on......
I realize that had they numbered them in the 100 range I could group in my report by 100's and everything would be pretty good however, I did not have the opportuinity to be involved in the numbering scheme.
Even if the numbers had been in the order of 100s I still would need a way to say that the 100 -110 range is Class Projects.
I think I need another table and link it but I am not sure how to set up the range of numbers for the category.
Hi all, I want to create a pivotchart from a query, embed it in a form and use a combobox in the main form to allow the user to select the x axis value. So in a query with fields city, state, country, etc the user would be able to chose one of those fields as the category(x axis across the bottom) to display data. Is this even possible? If it is, how do I do it? As it is now I can add multiple categories to my chart- but it just makes a big mess of the chart. I can embed a chart in a form and use a combobox- but it allows me to choose from within a field- but not from among fields. Also- how do I access the code to manipulate the chart? Thanx in advance
I am creating a report which is about 200 pages and would like to add a table of contents for each category i have with page numbers, however i have googled and tried many different things but to no avail and what it the correct way or even showing me on a sample database.
Question... i Wanted to create a Graph that has a bar for each category or column that has dates.. i want it to filter to show only the dates greater than 12-31-2011 so basically all dates in 2012.. i figured out how to do a query but i was only able to do a query for ONE column at a time so i'll have like 30 querys...is there a way to do this with one query and have each bar on the graph be for each column and have the graph showing how many people have completed these after 12-31-2011. URL...
This is just simple example, actually i have many table January until December. So when i key in data using form on every month, I want just 1 queries covering all month, so easier to check.
