Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2004
    Posts
    7

    Unanswered: Access 1997 Query

    I've created a database in Access 1997. I've made a form for entering data. Now I need to make a query that will be displayed as a report that will let people easily search ANY of my fields and display ALL of the information for a particular record when the query is run. I'm new to Access so programming language means nothing to me. I've made queries for each field that prompt the user to enter a value of that field; Ex. "Author" query says "Author?" when it is first run. I did this by setting the "Author" criteria=[Author?]. I can search all I want but I have 13 fields and 13 queries. How can I make one query that can search any of the 13 fields and then display all of the record information when it is run? I need to make this as easy as possible, other people with no Access experience will be using it. Thankyou for any help you can give me.

  2. #2
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    If you want to be able to rapidly jump all over the place with different query results I would suggest that you direct your learning to the following:

    1) Having query criteria being based on an entry on a form. When you do this the entry in the criterial will look like

    [Forms]![YourFormName]![TextBoxName] In the exampe you mentioned you would enter the authors name into the textbox. This clould also be done from a combo box with a drop down list of authors and when some clicked on an author in the list then a macro or code could be run which would then open the query.

    2) Leaning how to change the record source of a for. This is very easy to do with a macro action called Setvalue.

    3) Display your query in a tabular style form and on this form you can place various labels to click on that will change the record source.

    Hope that helps you get started.

    Mike

  3. #3
    Join Date
    Jun 2004
    Posts
    7
    3) Display your query in a tabular style form and on this form you can place various labels to click on that will change the record source.

    So how do I go about doing the 3rd suggestion?
    With the query do I need to change the criteria (should I leave it blank)?
    How do I make the form have drop down boxes to search with?
    All of my information is from one table. With the above suggestion can I make a query that will search any single field and then display all the information pertaining to a specific record? Thanks for your help, I just need some additional clarification.

  4. #4
    Join Date
    Jun 2004
    Posts
    7
    Also, how can I make a drop-down menu for my fields when I make a form? Could I just make a from full of drop-down fields to run a search that way?

  5. #5
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    When you make a form you have the option of selecting a continous form. Probably the easiest to start with is to use the form design wizard.

    For combo boxes you mkae them by selection the Access tool box in form design view. If it is not already showing in design view you display it by clicking a button on the tool bar that has what looks a hammer and spanner crossed over each other.

    When you select the combo wizard it will take you through different options. Basically a combo box is used to find records in your form or it used to insert data. Often that data is drawn from another table made just for the purpose. For example, someone in the insurance or money lending business might have a table with four records for Monthly, Quarterly, Half Yearly and Yearly and a combo box would insert the selection.

    A query criteria might be [Forms]![YourFormName]![ComboName]

    Thus with insurance I could select Monthly form a combo and a query would then display all records where the payment mode was Monthly.

    To change record source you use a Setvalue macro action. Click on the New in Macro section then click on the action line on the macro grid. A drop down list arrow appears and scroll down anc clicj SetValue. When you do this you will see appear at the botto of the macro screen:

    Item
    Expression

    For change record source you do
    Item [Forms]![MasterForm].[RecordSource]
    Expression "QueryName"

    Text boxes, combo boxes and labels you make yourself (as distinct from thos that appear when you drag a field into a form in form design) have a whole list of "Events" where either a macro or code can be run from. Right click on a text box in form design and then click properties and then click Event when the box opens. A very common one to select is OnClick. Thus when you click on the label, button, text box the macro or code runs.

    An important event for combo boxes is AfterUpdate.

    A macro you might have on AfterUpdate on a combo box would have

    OpenForm
    SetValue as described for record sourced.

    If form text boxes or combos are used for query criteria then if they box is blank the query will not display any records.

    Changing recordsource is normally done with queries that have already been made. For example, part of my data base is used for telemarketing and we have 20 categories of prospect. There are 20 queries where the criteria is the category number, 1 through to 20. In my case a for is opened that looks something like this:

    Category...Descriptions.....Salesman....Number Of Names

    1..............Doctors..........John .........803
    2..............Dentists..........Mark ........543

    and so on through to 20.

    A macro is attached to the field with category number and it runs OnClick and changes the recordsource of the form that is used to make the calls from.

    To do this you need to use the Conditions column of the macro making screen. To see Conditions colum click on View on the tool bar of the macro design screen and click conditions.

    In the above example there are 20 action lines in the macro and they are all of the SetValue for change record source and the various conditions entered on the appropriate Condition line are

    [Forms]![Categories]![CatNo]=13

    Thus when click on 13 in the form that the appropriate recordsource is used.

    Then at the end of the 20 SetValue actions is a Close Form action and that closes the form that lists the categories.

    If you going to search all over the place on a table (because the table is where the records are) then you might make a form that has what amounts to a menu of selections with a description.

    I like to use labels for the description and the macro runs when you click on the description label itself.

    You probably have some mouse clicking and reading to do

    Mike

  6. #6
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    One other thing you might think about is having more than one table.

    You mentioned search on Author and that suggests book titles and if that is the case you have authors in one table and book titles in another table. This is fundamental to Access.

    Think of it in terms of boxes of cards.

    One box of cards has the doctors patient details and the other box has a card for each visit.

    The insurance salesman has a card for each client in one box and a card for each policy benefit in other box.

    One author might have 7 book titles. So there mighty be 100 records in the Author table and 800 records in the Book Title table

    Mike

  7. #7
    Join Date
    Jun 2004
    Posts
    7
    Okay, I've got a combo boxes. How do I make it so multiple questions will be asked to the user but only one answer is needed to give full information. I made a smaller database to use with Field 1-Field 5 and specific number values for each entry. I have Field1 and Field2 set up as combo boxes and their criteria labeled as "Field 1" and "Field 2." I have to fill in both parts from a single record before it will display the record. Can I make it so only one part needs to be filled in? Also, is there a way to modify it so if I enter the information from one record when prompted for Field1 and then the information from a second record for Field2 can I get it to display both records? Here's what I'm doing for my actual project: I have a database full of reports, authors, dates, components, etc. They need to be in one table for presentation purposes. I need to make it so the information can be searched through any of the fields and between dates. I also need it so part of a report or author name can be entered and all the records are displayed for that part of the word. Drop down lists would help a lot for certain fields, but those same fields need to be able to be typed into if it is a recent record that has not been updated to the drop down list. Thankyou very much for all of your help Mike. It has helped me to understand this program much better than anyone else.

  8. #8
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Okay, I've got a combo boxes. How do I make it so multiple questions will be asked to the user but only one answer is needed to give full information.

    In your query criteria you can put a whole lot of this sort of thing an din one field:

    [Forms]![YourFor]![Text1] Or [Forms]![YourFor]![Text2] etc.

    Here is a real one from my data base and this is for a query supporting a sub form. The first part is selecting categories of prospects that were used for telemarketing. The second part is selecting dates of the calls.

    [Forms]![Attempts]![C1] Or [Forms]![Attempts]![C2] Or [Forms]![Attempts]![C3] Or [Forms]![Attempts]![C4] Or [Forms]![Attempts]![C5] Or [Forms]![Attempts]![C6] Or [Forms]![Attempts]![C7] Or [Forms]![Attempts]![C8] Or [Forms]![Attempts]![C9] Or [Forms]![Attempts]![C10] Or [Forms]![Attempts]![C11] Or [Forms]![Attempts]![C12] Or [Forms]![Attempts]![C13] Or [Forms]![Attempts]![C14] Or [Forms]![Attempts]![C15] Or [Forms]![Attempts]![C16] Or [Forms]![Attempts]![C17] Or [Forms]![Attempts]![C18] Or [Forms]![Attempts]![C19] Or [Forms]![Attempts]![C20] Or [Forms]![Attempts]![C21] Or [Forms]![Attempts]![C22] Or [Forms]![Attempts]![C23] Or [Forms]![Attempts]![C24] Or [Forms]![Attempts]![C25] Or [Forms]![Attempts]![C26] Or [Forms]![Attempts]![C27] Or [Forms]![Attempts]![C28] Or [Forms]![Attempts]![C29] Or [Forms]![Attempts]![C30] Or [Forms]![Attempts]![C31] Or [Forms]![Attempts]![C32]

    The category pf prospects is actually 1 to 32 but my naming of the text boxes is jusr for me ease. If I wanted to see results on prospects from category 3 category 4 then it does not matter which box the 3 and 4 are entered.

    This is in another query field (same query) and is for dates

    >=[Forms]![Attempts]![1] And <=[Forms]![Attempts]![2]

    I use very short gext box names for a couple of reasons and not the least is that Expression builders are limited to 255 characters.

    You can have the criteria entered into the boxes anyway you like and that inlcudes doing it from another form, provided both forms are open.

    You can also reference a bound text box and this is hand when opening a form to match a record ina form that is already open. For example

    [Forms]![12ProspectT]![NameNumber] I have this in a query. A macro on the form 12ProspectT opens are form called PrintandClose and then same macro changes the record source of form PrintandClose. In this case I a doing this because so that for does not open in a "filtered" manner because someone could release the filter on the tool bar.

    They need to be in one table for presentation purposes.

    Not really since tables can be and are joined as one table and that is a big part of Access. You can also link queries to a Word document and have Access insert data into Word Bookmarks. In fact I lost often have presentations done this way rather than printing out reports.

    But if your data is of a type that would normally involve two tables with a One to Many situation (patients and patient visits....client and policy benefits) I would recommend that you try and set up this way as early as possible. One reason alone is that people on these forums that can lend a helping hand or books and so on.....will all be geared up to the One to Many and two tables.

    I need to make it so the information can be searched through any of the fields and between dates. I also need it so part of a report or author name can be entered and all the records are displayed for that part of the word.

    Joining tables is all about that.

    There are exceptions but in general if you are entering forms of the same data across a row then two tables is the way to go.

    For example I might have on one row (one record) and which would be wrong

    Client Name etc....PolicyDetails1.......PolicyDetails2......Po licyDetails3

    Just one of the problems is that each policy beneft details will have different field names.

    If I can save you any grief with Access then it is to very strongly recommend getting tables as right as possible as soon as possible. As the data base grows (which it will ) making changes to tables can be like changing one of the bricks in the foundations of your house. Just changing one of those bricks will be a bigger deal than changing the entire roof.


    Drop down lists would help a lot for certain fields, but those same fields need to be able to be typed into if it is a recent record that has not been updated to the drop down list.

    You can type directly into the text box fed by the combo drop down list. Much of the time I don't use combos.

    For example in the illustrattion with the 32 prospect categories I have 32 little boxes across the bottom of the form and the numbers 1 to 32 are above each box. Amacro is on the OnClick even for both the little label with the number and the next box under it. That is simply a SetValue macro with the expression being the category number. A single combo could not do that. However, I could have a combo fed by a table that has 32 records, that is, 1 to 32 for each record instead of clicking on the individual labels or boxes.

    Thankyou very much for all of your help Mike. It has helped me to understand this program much better than anyone else.

    I went through all of this in 1996. I honestly believe with Access that you can do far more if you know 10 "actions or operations" like the back of your hand as opposed to roughly knowing 30 actions.

    If you want to a lot with queries there are two things that can add greatly to what you can achieve and they are:

    Making a calulated field in a query. To do that just open your query in design view and then on one of the black field spaces type MyNewFieldName:

    After the colon : you then enter what is required. If put 2*3 and then open the query the new field will be full of 6s.

    However, the clincher is the IIF function.

    In its most basic form it is a simply true of false such as

    IFF([Price]>=100,"Discount,"No Discount")

    But you can do more than two items. Here is one that does year premiums for me. What happens is that one field [Payment Mode] has either a M, Q, H or Y and another field [Reg Premium] has the actuall installment premium. Hower, I also need to see the total yearly premium and this created field does that.

    Premiums: IIf([Payment Mode] Like "H",[Reg Premium]*2,IIf([Payment Mode] Like "Q",[Reg Premium]*4,IIf([Payment Mode] Like "Y",[Reg Premium]*1,IIf([Payment Mode] Like "M",[Reg Premium]*12))))

    IFF functions are also good for changing names to something that is more meaningful to someone. For example, the insurance company will have different codes for different types of policy like LOIBEN, ZYN001 and so on. Now that is pretty usless if we are sending something out to a policy holder in a mail out so...

    NiceName:IIF([CodeField] Like "LOIBEN","Income Replacement insurance" and so on like the above one for premiums.

    You might use IIF fucntion in a similar way to classify books by some number.

    And one more for you which can do a real lot is the Mid fucntion. This lets you extract characters from a field. Going hand in hand with results from Mikd functions is joining field.

    So a new field that will give you Book title by author.

    NewFieldName:[BookTitle] & " " & "by" & " " & [AuthorName]

    You can play with this stuff forever

    Mid function and other text manipulation fucntiions (there are several) could be handy for sorting book titles such as

    The Big Green Giant
    A Place in Paradise.

    To get a proper sort you have to get rid of the A and The

    Mike

  9. #9
    Join Date
    Jun 2004
    Posts
    7
    It's been a little while since I've worked on the database but here is where I'm at. I have no patience to program so I just used one query with criteria questions in it (ex: "System" field has [System?] as it's criteria). I then put the criteria for the other fields in the "Or" boxes under the respective field (if I knew how to post a screen shot I would). Know when I run the query (or form or report) It cycles through a bunch of questions, asking for an entry for each field; it only needs one question to be answered to generate the correct results. Here are the new problems I am encountering: I can't answer questions for multiple fields, it'll just generate seemingly random results (I tried using "And" and "Or" arguments at the same time but then it wouldn't work); also, when I answer the question that is asked for one field I have to keep pressing enter to cycle through the rest of them before it displays results, is there any way I can answer just the one field (ex: the 3rd field out of 13 total) and just skip straight to the results? I'm sure I will have more questions later, and I'll try to figure out how to post a screen shot so all this makes more sense.

  10. #10
    Join Date
    Jun 2004
    Posts
    7
    Fixed the problem. Just made a new form with a bunch of buttons that will select a specific query to search by, then launching the report. New problems: how can I get my combo boxes to store a value in their lists (that was not originally in the lists) without going into their properties and changing the Row Source? Other problem: I have a form specifically for data entry, by default it opens and displays the first record; how do I get it to open and display a new record so no one accidentally changes the first record? I've got 3 weeks to finish this database and then my internship is done, hopefully I can get this figured out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •