Creating a search function and using calculated fields

[accessnihon]

Good Morning,

I am new to this board, and sincerely hope that somebody could help me with a problem a have.

I am relatively new to MS:Access, and struggeling at the moment to realize a project I am undertaking right now.
What I would like to do, is the following:

1. Create a form, where I can input a short-code (e.g. "abc") into a field a.
1.1. The function should then search 10 columns in a table for the short code "abc" itself, or any code that contains it (e.g. "1abc2").


2. There are three more fields, that should then display information from corresponding rows to the value in field
a.

3. I then want to calculate the share of the product in a fith field. To accomplish that, I would like to sum up the total population of all products.


Sadly I have to admit, that I am currently struggeling with all three points. I would appreciate if you were able to tell me:

a) If it is possible to realize it in Access at all
b) How to solve the following problems:

1. I have not yet understood, how to implement a search function at all. Especially as I want to display static values as well as newly calculated values (especially the relative population share)
2. I haven't found a way to calculate column totals, and use them to calculate the relative share. I have researched on a couple of different sites, and it seems to be possible, but I haven't figured out how.

I am quite new to the access syntax, so any help to help myself would be greatly appreciated.

Best regards.

[hertfordkc]

You indicate that you already have a picture of your data table. However, I wonder why you must search 10 columns. This suggests that perhaps these should be 10 records in a related table. Having said that, create a table with a few records of the test data.
Then create a query that returns all of the records and data in the table. Look at the SQL view of the query, then add "*abc*" to the criteria as appropriate. Run the query, check the results, then look at the SQL again. The added info will go into a filter, which you will construct.
1. Create a form, where I can input a short-code (e.g. "abc") into a field a.
1.1. The function should then search 10 columns in a table for the short code "abc" itself, or any code that contains it (e.g. "1abc2").
When you have accomplished the above, compact and repair the database, and attach it to another post. I'll help with the linkage between the textbox and the filter.
Your question is so broad that it is difficult to communicate the many different ways of attacking the problem.
2. There are three more fields, that should then display information from corresponding rows to the value in field
a. This should simply be a matter of placing the appropriate fields on the form.

3. I then want to calculate the share of the product in a fith field. To accomplish that, I would like to sum up the total population of all products.
This can be done with queries. Is this to be displayed on a form, a report, or both.


Sadly I have to admit, that I am currently struggeling with all three points. I would appreciate if you were able to tell me:

a) If it is possible to realize it in Access at all
b) How to solve the following problems:

1. I have not yet understood, how to implement a search function at all. Especially as I want to display static values as well as newly calculated values (especially the relative population share) Once you have a table construct, use the query wizard to construct your first query to simply retrieve all of the data in the table. Once you have done that, you will have a much better idea of where your are going. Be sure to switch between the design and database views of the query, then go on the
SQL view.
2. I haven't found a way to calculate column totals, and use them to calculate the relative share. I have researched on a couple of different sites, and it seems to be possible, but I haven't figured out how. There are several ways to accomplish this.
Most users would rely on queries and forms exclusively. However, I'm more comfortable with VBA, and this would be straightforward.

Look at one of the samples and their objects while switching between the different views.

[accessnihon]

Dear Hertfordkc

I tried to follow your instructions, and attached a sample of my database.
While it was interesting to see how the filters change in the SQL view, I could not yet grasp, how I will apply this knowledge for creating a search function/ a filter function.

May I ask which samples you were referring to? I hope you excuse my noobishness, but I become more and more aware about my yet limited knowledge concerning Access.

Maybe you can have a look at my table/query/form and give me a couple of more suggestions.

I would like to thank you sincerely for the time you invested in writing this post, and hope you are willed to continue helping me with my problem.

Best regards

[hertfordkc]

I've been out since I last wrote, but I should be able to give you a better response soon.
I didn't have any particular sample in mind when I suggested that you look at them. I imagine all of them will give you a better understanding of how tables, queries, forms and reports work together. Examining the property sheets of each of the objects will give you insight into how powerful and flexible Access can be. Perhaps the downside to all of this is that you may realize there are usually two or more ways to do everything.
I haven't looked at the all, but the Northwind DB is fairly comprehensive. Enough so that I don't suggest you look at it first.

[hertfordkc]

Dear Hertfordkc

I tried to follow your instructions, and attached a sample of my database.
While it was interesting to see how the filters change in the SQL view, I could not yet grasp, how I will apply this knowledge for creating a search function/ a filter function.

May I ask which samples you were referring to? I hope you excuse my noobishness, but I become more and more aware about my yet limited knowledge concerning Access.

Maybe you can have a look at my table/query/form and give me a couple of more suggestions.

I would like to thank you sincerely for the time you invested in writing this post, and hope you are willed to continue helping me with my problem.

Best regards

After looking at our posts so far, I have some more questions:
In your first post, you mentioned searching on ten columns, but your example only searched on one column. Did you mean that you will have the potential to search on any one of ten columns, or that you may search on as many as 10 columns?
Your query1 was an attempt to sum the population of what? All of your records or a subset? Could you provide an example. In reading your earlier post, I would have guessed that you might want to sum the population for those records containing TOY1 in the BlockID (about 368,xxx) and subsequently calculate the percent of TOY1 market? held by each location, i.e. about 29% and 71% (+/-2%) .
Your date fields are text. If you need to do any calculations based on those columns, you should consider specifying them as date fields. You might also use the calendar popup which can be used in conjunction with date fields.
I'll try a few things on your sample and repost it.

[ssanfu]

I modified your mdb. Maybe this example will help you.

[hertfordkc]

Here's another modified version of your db.

[accessnihon]

Please excuse my late answer, I had no internet access yesterday, as I had to attend various meetings throughout the day.

After looking at our posts so far, I have some more questions:
In your first post, you mentioned searching on ten columns, but your example only searched on one column. Did you mean that you will have the potential to search on any one of ten columns, or that you may search on as many as 10 columns?

There are up to 10 different values that can be inside this column. These values are all separated by commas. My initial Idea was, to split this column into 10 separate columns, so that each column just contains one value.

Your query1 was an attempt to sum the population of what? All of your records or a subset? Could you provide an example. In reading your earlier post, I would have guessed that you might want to sum the population for those records containing TOY1 in the BlockID (about 368,xxx) and subsequently calculate the percent of TOY1 market? held by each location, i.e. about 29% and 71% (+/-2%) .

You are correct, I would like to calculate total sums and subset totals. e.g. Population TOY1 (Blockid) / Population Total = X%
Actually what I was shooting for is this, if you type in the Katashiki, you will get an result displayed on a block id level. The Katashiki(s) should be mentioned in the according field, but the block id should act as a primary key.

I hope my explanation is understandable. Please do not hesitate to ask if any of my statements is unclear to you.

Your date fields are text. If you need to do any calculations based on those columns, you should consider specifying them as date fields. You might also use the calendar popup which can be used in conjunction with date fields.
I'll try a few things on your sample and repost it.

Thanks for the nice suggestion. I will now take a look at the provided samples, and come back to you later.
Thank you very much for all the time you invested in helping me.

Best regards.

[accessnihon]

Concerning DBA-Mod:

This one is very close to what I imagined. However, I would really love to learn how to do it myself. Maybe you'll find the time answer a few questions of mine:

1. How did you manage it, that the Output date is displayed in multiple rows (e.g. multiple records on one page)? I tried to do that previously, but was not (yet) able to find out how to accomplish this.

2. Can you help me to accomplish the following two things? (and explain me how it is done):
2.1. I really like the selectable list, however, it would be best if the user was able to use wild-cards. He should, in fact, be able to search for any of the comma separated values in the Katashiki list.
2.2. I would love to change the order to: brand - blockid - key - katashiki - population. And Summarize the population value for each BlockID.

I will now continue to look at the code of your example.
Thank you again for your help and effort. It is greatly appreciated.

Best regards

[accessnihon]

I found the solution for the wildcard problem:

I replaced:
If Len(Trim(Nz(Me.txtKey, ""))) > 0 Then
strFilter = strFilter & "Key= '" & Me.txtKey & "' AND "
End If

If Len(Trim(Nz(Me.txtKey, ""))) > 0 Then
strFilter = strFilter & "Key Like ""*" & Me.txtKey & "*"" AND "
End If

Maybe this solution will help somebody with similar problems

[ssanfu]

Concerning DBA-Mod:

This one is very close to what I imagined. However, I would really love to learn how to do it myself. Maybe you'll find the time answer a few questions of mine:

1. How did you manage it, that the Output date is displayed in multiple rows (e.g. multiple records on one page)? I tried to do that previously, but was not (yet) able to find out how to accomplish this.

Put the bound fields from the query in the DETAIL section. Even though it looks like there is only one row of fields, Access uses the fields to display ALL the records in the record set. In the properties dialog for the form, Click on the FORMAT tab. Set the DEFAULT VIEW to "Continuous Forms".

2.2. I would love to change the order to: brand - blockid - key - katashiki - population. And Summarize the population value for each BlockID.

Just arrange the fields in the order you want.