Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2017
    Posts
    2

    Unanswered: Problems creating a search form and report off of multiple table/queries

    I am using Access 2016 on a Windows 10.

    I am trying to create a simple issues tracking database to create a record for certain types of incidences. I have attached a copy of the database (recreated to expunge private information) to this post to make things easier, hopefully.

    I have come to the point where I need to create a search function in Access and a Report in order to print out relevant details. However, I have run into issues trying to make this a reality: my database uses a concatenated field that stems directly from a query I created. This concatenation holds the ID number, Last, and First name of the client, however, I have not been able to find out how to create a relationship between this concatenated field and the Event and Clientelle tables.

    I need to create either a search query or form that can pull the "Category" and "Type" information from the Clientelle table, while also pulling relevant data of that same person from the Events Table. The report needs to also be capable of pulling from these two tables as well.

    I am unable to figure out how I would create a search feature like this with the concatenated ID, Last, and First name in-tact: The search output should go off of a combo box that pulls data from the Concatenated Query; so someone only needs to click the drop down box in order to tell the query or form who needs to be pulled, and what data needs to show up.

    The search results should give the following information: the Customer (which is a concatenation of the ID, Last, and First name, in one field), the "Category" and "Type" from the Clientelle list table, and everything from the Events Table.

    The report needs to have the same setup as well, when printed out: Customer (concatenated), Category and Type from Clientele table, and some details from the Events Table.

    I have been unable to wrap my head around how to do this, so I do not have any "solutions" per se, since I'm not sure what to do. I did, however, find out a way to get what I want through a more straightforward method: remove the concatenation. I can then manually create a relationship between the ID key from the Clientele List table with the Events Table and generate a Report output that shows information from both tables, corresponding to the correct customer. I assume the same can be done for a query or form that is focused on searching for customers.

    The problem is, my employer wants the concatenation, so it can be made to be a combo box that other users can just click into, not type into. Thus, the concatenation is vital in this regard and needs to fit into the search feature and report.

    The overall database is meant to track issues and input data (via forms) of new incidents and new personnel entries. It must also be able as error free as possible, thus the liberal use of combo boxes, have a search feature, and can print out reports that can tell someone unfamiliar with the personnel: who they are, and what the incident was.

    Thank you for reading up to this point and if anyone needs any more clarification or if the access zip file attachment is not opening correctly, then please let me know! Thank you again for taking the time to help me!
    Attached Files Attached Files
    Last edited by BingA; 06-14-17 at 17:58.

  2. #2
    Join Date
    Apr 2017
    Posts
    14
    I created two new queries. One of them is a mod of your original query. They both run correctly, but I'm not 100% sure it's what you need. Here is the SQL for the two queries:

    This is the mod of your original query, which I called Copy of Clientelle List Query.
    Code:
    SELECT [CustID] & " " & [Last] & ", " & [First] AS Customer, [Clientelle List].*
    FROM [Clientelle List]
    ORDER BY [CustID] & " " & [Last] & ", " & [First];
    Here is the SQL for the second query.
    Code:
    SELECT [Copy of Clientelle List Query].*, [Events Table].EID, [Events Table].[Event Date], [Events Table].[Event Type 1], [Events Table].[Event Type 2]
    FROM [Copy of Clientelle List Query] INNER JOIN [Events Table] ON [Copy of Clientelle List Query].Customer = [Events Table].Customer;
    In the new query, I only included a few fields from the events table, as I don't know what fields you actually need.

  3. #3
    Join Date
    Jun 2017
    Posts
    2
    Quote Originally Posted by slandy18 View Post
    I created two new queries. One of them is a mod of your original query. They both run correctly, but I'm not 100% sure it's what you need. Here is the SQL for the two queries:

    This is the mod of your original query, which I called Copy of Clientelle List Query.
    Code:
    SELECT [CustID] & " " & [Last] & ", " & [First] AS Customer, [Clientelle List].*
    FROM [Clientelle List]
    ORDER BY [CustID] & " " & [Last] & ", " & [First];
    Here is the SQL for the second query.
    Code:
    SELECT [Copy of Clientelle List Query].*, [Events Table].EID, [Events Table].[Event Date], [Events Table].[Event Type 1], [Events Table].[Event Type 2]
    FROM [Copy of Clientelle List Query] INNER JOIN [Events Table] ON [Copy of Clientelle List Query].Customer = [Events Table].Customer;
    In the new query, I only included a few fields from the events table, as I don't know what fields you actually need.
    Thank you very much for replying, and I apologize for not getting to this sooner.

    This is exactly what I want to do! However, is there a way to put the second query under certain conditions: I want to be able to search for someone on that list (pulling all other related incidents as well) based on their Customer value. I originally accomplished this by creating a separate Form that had a txt name value (like EIDtxt) linked to the query, so I could essentially type values into the search form, hit enter, then it would populate in the Search Query itself.

    The issue I had with that was: I needed it to be more intuitive, to be blunt; I needed to change the search box in the form (which was a text box) into a combo box based off of the Customer value. Thus, anyone can just click on the combo box, and select who they wanted, then it would tell the Search Query what to look for and it would populate the query with only relevant results based off of the combo box in the form.

    What you have done is what I wanted to accomplish, but I also need to find out how to attach conditions (based on a combo box) to the search query, so anyone can go in and just click on a simple drop down box and select who they want to see, and the search query would bring up information that you listed, which corresponds to that person, including other incidents under that same Customer value.

    Thank you again for taking the time to help! I will try to figure this out in the mean time, so I will keep this thread updated, when I have experimented with the database a bit more. Unfortunately, I just don't have a good grasp of how I would go about creating a conditional for the Search Query without it breaking something.
    Last edited by BingA; 06-26-17 at 16:26.

Posting Permissions

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