Question Help With Search Results Being Clickable and Opening up a Form

[j.razz1]

Intro:
Hi all, this is my first post here and today is my first day attempting to build a database. I spent a long while doing searches for "search" and "Access 2010" only to later realize that the term must be Query and not Search. So, I don't have a great understanding of the terminology or the methods needed to adequately build a database. Even so, that hasn't stopped me from trying.

Context:
I have so far made 3 tables. One called "Advocates" which is a list of employees. Another called "Collection Fields" which is where new data is stored and collected from a form. And the last one is the "Switchboard Items" which contains the options on the switchboard.

I have one query that may not be necessary as I made it only to deal with the issue I am posting about here. It is called "Collection Fields Query" and only queries "First Name" Last Name" and "Client ID". This Query is utilized by one of my forms called "Search" which allows for searching of these three fields to bring up results (All that works as it should).

I have three forms. One called "DA Input Fields"- it is where a user can type in information in fields and save the data (by pushing a save button) to the "Collection Fields" Table. Another is called "Search" and it runs the "Collection Fields Query" I listed above and shows the results in a datasheet (again, I don't know the terms). And the last one is called "Switchboard".

I also have one report that I don't think has any bearing on my question.

Question:
I am sure this is answered somewhere but again, I don't know the terms for which to search.

How can I have a user enter in information into "DA Input Fields" and let's say three weeks later, do a search and update the record (every record will need to be updated multiple times and these records will grow into the 1,000's)?
A caveat is that I want it to be user friendly. Currently when I do a search and pull up results in teh datasheet from the Query, the user cannot do anything with that information in regards to updating the record.

What I am After:
Here is the scenario I am after. End user goes to the search feature on the switchboard. Types in either the first and/or last name and/or Client ID and gets a list of results (I have the query set up with this code and it works great: Like "*" & [Forms]![Search]![Case ID] & "*"). Once this list comes up, they can then click on the record they want to update or check and the result comes up in the "DA Input Fields" form where they can then make their changes and ultimately save them.

One additional Issue:
We will have Client ID's unique to each individual but there may be multiple records that have the same last name, first name and Client ID (as it is the same person), but some will be shown as closed and others as still open. What would be a good way to distinguish between these types of issues when doing those searches? How would you do it?

Thanks for your help and for allowing me to post here. Hopefully someone will point me in the right direction!

 

[cpberg1]

Welcome to the forum!

I'm still a bit new here as well and as such have recently been through many of the issues you are/will find.

Can you give us a bit more information about your general needs for having this database? What type of items are in the collection fields table?

You mention having multiple records for one client ID. Some distinction needs to be made between Primary Key and foreign key.

Take a read through the site below written by an access MVP,the first 3 chapters will help out your understaning a lot I think.

A bit of time spent now fixing up the table structure will go a long, long way!

UseCamelCaseForYourTableNamesInsteadOfSpaces. Or_underscores_work_okay_too...

Chris

http://www.accessmvp.com/Strive4Peace/Index.htm

 

[j.razz1]

Can you give us a bit more information about your general needs for having this database? What type of items are in the collection fields table?

Fields are as follows (not many):
Client ID (number)
First Name
Last Name
Case Open (Date-Calendar)
Case Closed (Date-Calendar)
a check box that denotes whether an email has been sent
A request field (Date-Calendar)
2nd request field (Date-Calendar)
Date of contact field (Date-Calendar)
Appeal date (Date-Calendar)

That's it.

You mention having multiple records for one client ID. Some distinction needs to be made between Primary Key and foreign key.

I don't know what that means. I will look it up. Basically what I was trying to communicate is that a person can be active in the system more than just once. Some will have multiple cases that have been closed with one or more open simultaneously and others will have any variation thereof. When a search is done by the user, they need to be able to distinguish between all of these cases and know how to pick the correct one. Hopefully that makes sense.

Thanks again for your willingness to help and for the link.

j razz

 

[cpberg1]

Makes a lot of sense. Each client can have many cases. Are there ever more than 1 clients involved with 1 case.

For example

Table "client"

Fields
clientID (primary key)
LastName
FirstName


Table "cases"
CaseID (primary key)
ClientID (foreign key)
CaseOpen
CaseClosed

Maybe some other items that are related to this one case only.

Usually if you need several fields for the same information it will be a new table, for example request1 and request2. What if a client/case needs a third request? Should probably be a request table with a foreign key to case/client table. I'm not exactly clear on whether the request is associated with a case or client directly.

having the primary and foreign keys on the tables allows you to link them together and see which clients are with which case,ect.

 

[j.razz1]

Are there ever more than 1 clients involved with 1 case.
No.

Table "client"

Fields
clientID (primary key)
LastName
FirstName


Table "cases"
CaseID (primary key)
ClientID (foreign key)
CaseOpen
CaseClosed

So you are recommending that I divide up my one table that has all fields currently in it and make it two right? This will allow me to better control the information if I am following you correctly. It will also allow me to have a duplicate field (clientID and Client ID) with slightly different names- which I don't understand. But I do understand that this will allow for one to be a foreign key. I'll need to read up on the logic of this.



Usually if you need several fields for the same information it will be a new table, for example request1 and request2.
For our needs, these will be separate as the first request will take place at the 30 day mark and there will be things that take place. The second request will take place at the 60 day mark and other things will take place (they are not the same even though they have similar names).

What if a client/case needs a third request?
They won't. Policy won't allow for it.

I'm not exactly clear on whether the request is associated with a case or client directly.
They will be associated with the current case (each client could have multiple cases with multiple levels of requests, but the relevant requests will always be tied to the current case. Hopefully that's not confusing).

having the primary and foreign keys on the tables allows you to link them together and see which clients are with which case,ect.
Can you explain how I tap into this type of functionality? Or if it is in the link you provided- somewhere within the 100 pages, just tell me as much and I will look there for an explanation.

Thanks again for giving of your time to help me.

j razz

 

[Beetle]

So you are recommending that I divide up my one table that has all fields currently in it and make it two right?

Yes, that is what Chris is recommending. A somewhat simplified way to think of this is that Tables represent entities and the columns (fields) in each table represent the attributes of that entity. Clients is an entity. Things like FirstName, LastName, etc. are attributes of the Client entity. Cases is a separate entity. Although it is related to Clients it is still its own entity and therefore needs its own table.

It will also allow me to have a duplicate field (clientID and Client ID) with slightly different names- which I don't understand.

That's just a slight typo in the post. They do not have to have different names.

For our needs, these will be separate as the first request will take place at the 30 day mark and there will be things that take place. The second request will take place at the 60 day mark and other things will take place (they are not the same even though they have similar names).
...

They will be associated with the current case (each client could have multiple cases with multiple levels of requests, but the relevant requests will always be tied to the current case. Hopefully that's not confusing).

Your description of the Requests leads me to believe that you should have a separate table for those as well, which would be related to the Cases table. Couldn't say for sure though without having a little bit better understanding of the circumstances and the "other things" that take place when a request is generated.

having the primary and foreign keys on the tables allows you to link them together and see which clients are with which case,ect.
Can you explain how I tap into this type of functionality?

You establish a relationship between the Primary Key field in the "parent" table (in this case Clients) and the Foreign Key field in the "child" table (in this case Cases). Open the relationships window from the toolbar, add the Clients and Cases tables, then drag the ClientID field in the Clients table to the ClientID field in the Cases table. When prompted, make sure you select "Enforce Referential Integrity". Creating table relationships without referential integrity is really nothing more than an exercise in drawing lines.

 

[cpberg1]

So you are recommending that I divide up my one table that has all fields currently in it and make it two right? This will allow me to better control the information if I am following you correctly.
Yes, it will allow you to avoid duplicate data entry and allow for useful display of the input information.

Imagine you want to store more information about the client. It may be nice to have their address, phone number, email for example. If you have all the fields in one big table you will have to duplicate the client information each time you add a new case.
If you have two related tables you enter the details specific to each client once in the client table and the details specific to each case when you have a new case. To "add" a client to a new case all you'll have to do is specify the name. You'll then have all their personal information already stored and we just need to decide what and how to display it.

You'll need at least two tables and probably more. We haven't discussed employees at all yet.
How is an employee related to a case... Is there always only one employee per case? Do employees handle multiple cases? Does 1 employee handle all cases for a particular client? If multiple employees on 1 case, can different employees make request1 and request2? If so, is it important to know who is associated with a request?

These type of questions allow you to set up the table structure appropriately. If we create no link between employees and clients/cases for example you can't know who has how many open cases (would be an important question in my business).

It will also allow me to have a duplicate field (clientID and Client ID) with slightly different names- which I don't understand.
Ideally you spell them exactly the same. That will allow access to know they contain the same information and automatically relate them later when we start making queries and forms.

But I do understand that this will allow for one to be a foreign key. I'll need to read up on the logic of this.
The one that is not a unique number will be a foreign key. In your example ClientID in the Cases table. Primary Keys must be unique numbers and therefore will usually be of field type autonumber. The end user usually doesn't need to see the Primary keys, they only exist to to uniquely identify a client or case. You need to identify Primary keys for access (in design view right click and select primary key)

End users also don't usually need to see foreign keys which only exist to relate the information between tables. You won't need to identify these for access until you have your table structure designed.

Can you explain how I tap into this type of functionality?
You'll be able to relate clients to cases (and employees if appropriate) via the primary and foreign keys on different tables in queries. To do so and get useful information you'll need to set joins between tables. Queries can then be used to populate reports (for printing or viewing) or forms (to edit/input data).

Happy to help! I also needed it to get as far as I have come...

 

[cpberg1]

Yes, that is what Chris is recommending.

Beat me to it on my Ipad. Hi Beetle!

 

[j.razz1]

Your description of the Requests leads me to believe that you should have a separate table for those as well, which would be related to the Cases table. Couldn't say for sure though without having a little bit better understanding of the circumstances and the "other things" that take place when a request is generated.

The things I was referring to are things outside of the database. Phone calls that do not need to be captured as data, waiting for others to do their job outside of the agency, etc. Once the request has been approved, the case closes. If it has not, then the second request. If that request is rejected, then the Date of Contact occurs (third attempt at a higher level) and if that fails, that is when the appeal is made. That is the final step in the process. Hopefully that clarifies things a bit.

If you have all the fields in one big table you will have to duplicate the client information each time you add a new case.
That makes sense to me. So, in my situation, I would want the Client ID and the First and Last Names to be in two different tables so that they would not have to retype at least one of those (id or first and last name) when they pull it up from a search.

This brings about two questions though.
1. How do you pull data from two or more different tables into a "search" form? Using the wizard (which I am sure is limited) I was only able to choose one table or query to pull information from. Would I have to have a different search form for each type of search I want to all the user to do? Also, how would this impact reports- I think I have ran into the same limitation there as well.

2. My original question comes to mind here: How do you then get this information back into the "DA Input Fields" after selecting the record you want from a search? In the Search form I only have three fields (that's all that's needed) Client ID, First Name, Last Name.

How is an employee related to a case... Is there always only one employee per case? Do employees handle multiple cases? Does 1 employee handle all cases for a particular client? If multiple employees on 1 case, can different employees make request1 and request2? If so, is it important to know who is associated with a request?
An employee will be assigned to a case via a supervisor (this doesn't have to be a function of the database, but it would be nice to learn how at some point- but it would be a secondary issue at this point). There will always be just 1 employee tied to a case and preferably, that one employee will receive any and all future cases for that particular client. Employees will handle multiple cases at any given time- it could be upwards of 100.

...you can't know who has how many open cases
That would be good to know.

You need to identify Primary keys for access (in design view right click and select primary key)
I'm glad I registered here- this information is really helpful

In regards to having the naming convention to include no spaces, can I change that on the fly and Access will update all links or what is best practice for doing that?

Here is a link to three screenshots of my forms in a zip file as that may help you help me some
ge.tt/7mfV1UH/v/0?c I am not allowed as of yet to post links, but I think you will figure out that all you have to do is add the w's in front of the ge.tt and you should be good to go.

 

[cpberg1]

So, in my situation, I would want the Client ID and the First and Last Names to be in two different tables so that they would not have to retype at least one of those (id or first and last name) when they pull it up from a search.

close, we're back to the Primary/foreign key concept.When talking about ClientID now you need to specify which table.

A somewhat simplified way to think of this is that Tables represent entities and the columns (fields) in each table represent the attributes of that entity. Clients is an entity. Things like FirstName, LastName, etc. are attributes of the Client entity.
For each entity you'll need all the attributes and a primary key. Your clients table (just 1 table) needs to have ClientID, FirstName, LastName, PhoneNumber, ...
To link a entity to another you start by creating a foreign key in the second table. Use the title of the Primary Key from the table you want to link.

How do you pull data from two or more different tables into a "search" form?
"You'll be able to relate clients to cases (and employees if appropriate) via the primary and foreign keys on different tables in queries. To do so and get useful information you'll need to set joins between tables. Queries can then be used to populate reports (for printing or viewing) or forms (to edit/input data)."

2. My original question comes to mind here:
"...or forms (to edit/input data)."

An employee will be assigned to a case via a supervisor (this doesn't have to be a function of the database, but it would be nice to learn how at some point- but it would be a secondary issue at this point). There will always be just 1 employee tied to a case and preferably, that one employee will receive any and all future cases for that particular client. Employees will handle multiple cases at any given time- it could be upwards of 100.

Cool, take a stab at the primary and foreign keys for this description, post your results.

In regards to having the naming convention to include no spaces, can I change that on the fly and Access will update all links or what is best practice for doing that?
Nope, suggest to fix it now before you have many forms and queries based on the tables. It will seriously save you loads of time down the road. Do It!

 

[j.razz1]

Okay, I have made the changes to the naming convention, Subdivided my table into three separate tables (Collection Fields is no longer and in its stead I have: Cases, Client, and Requests), and I have established relationships between the three "Client_ID" fields- but I didn't see where in the drop down options to make it autonumber as mentioned in one of the previous posts.

Here is a link to a picture of the relationships and it also shows my fields: ge.tt/4IbGNUH/v/0?c

Just as a disclaimer, I am not really understanding how relationships work (I understand how it works in theory) I'm just lost in trying to wrap my mind around setting them up in a beneficial way.

From the picture I have linked to, it should show that I have the following:
The Client Table has the Client_ID set as the Primary Key. It has two links coming from it to a "Client_ID" in the Cases table and a "Client_ID" in the Requests table. I assume that by me dragging from the Client table to the Cases and Requests tables that is what dictates Parent/Child status for the tables or do I need to label them in some way? I did a search on that and found nothing helpful about how to do that other than what I have assumed above.

Please do let me know if I am on the right track!

Also, now that I have the relationships set up and assuming they are right, how do I go from where I am now to where I can have a query populate the "DA_Input_Fields" form? I don't understand the process and I don't understand how to get a query to pull from more than one table.

Thanks again for the help. I have learned a lot and still fill like there is so much more to learn!

j razz

 

[j.razz1]

Okay, a slight problem I have found. Since renaming everything to get rid of spaces I found that I am getting #Name? in my fields on my "DA_Input_Fields".

I tried to figure out how to change this and I went under "Design View" and opened up the Property Sheet and then clicked the drop down for "Control Source". Since the information is in a different Table, it is not in the drop down. If I click the ellipses next to the drop down arrow in the "Control Source" row, I get the "Expression Builder" pop up window. From here I choose my database under "Expression Elements" and choose "Tables" and choose "Client" and double click on "Child_First_Name". I then click OK. This gives me this code: [Client]![Child_First_Name] which results in the #Name? error in the "Child_First_Name" box when looking under the "Form" View.

Any help on this would be appreciated. I found this, but am unsure if it is relavent: dbforums.com/microsoft-access/1117854-control-source-another-table.html

j razz

 

[cpberg1]

Please do let me know if I am on the right track!
Yep.

I believe that a "request" is related directly to a case and not a client, correct? You should adjust your relationships if so. Delete the one from clients to requests and form a new one between requests and cases. You can remove the Client_ID field from the request table, because of the relation Clients->Cases->Requests you'll be able to see which clients are associated with which requests.

This is one of the more difficult concepts when first learning database design because it is very different than other "Flat" data storage where you have one line of information for each case. It is also what makes databases so powerful.

If you view one particualar table in design view you can change datatypes. Primary keys will be autonumber and foreign keys number.

Post back with your progress. I know it feels like slow progress but you're learning the meat and potatos of the DB deal right now.

The query in design view will allow you to add two tables. I use Access 2003 so looks slightly different than yours. Sample attached. Once we have good tables with a query that gives you all the info you need on your form we link up the form again

I assume that by me dragging from the Client table to the Cases and Requests tables that is what dictates Parent/Child status for the tables or do I need to label them in some way?
Assumption correct.

 

[Beetle]

The things I was referring to are things outside of the database. Phone calls that do not need to be captured as data, waiting for others to do their job outside of the agency, etc. Once the request has been approved, the case closes. If it has not, then the second request. If that request is rejected, then the Date of Contact occurs (third attempt at a higher level) and if that fails, that is when the appeal is made. That is the final step in the process. Hopefully that clarifies things a bit.

What type of data are you storing in these fields (Request1, Request2, DateOfContact and Appeal)? Dates?

 

[j.razz1]

When I attempt to change the data type to "AutoNumber" for Client_ID I get this message: "Microsoft Access allows only one AutoNumber field per table." The ID field is AutoNumber. Do I need to just leave the Client_ID as "Number"?

These are the little gotchas that make you wonder if you will ever understand!

j razz

 

[j.razz1]

Yes. Just dates. When you click the field, a calendar will pop up.

j razz

 

[j.razz1]

I believe that a "request" is related directly to a case and not a client, correct?
Yes. You are correct.

You can remove the Client_ID field from the request table, because of the relation Clients->Cases->Requests you'll be able to see which clients are associated with which requests.

I removed it (which it wouldn't allow me to join it to the Case_ID of the Cases table anyway), but now I don't understand which two fields I should be joining up? ID and ID? or Appeal_Date and Case_Open fields? I don't understand enough to know which one relationship to establish (or that it even matters) between Cases and Requests tables. I'm going to look up the logic behind it though right now.

j razz

 

[cpberg1]

If you view one particualar table in design view you can change datatypes. Primary keys will be autonumber and foreign keys number.

Change ID field to number first, then set your ClientID to autonumber.

Take a few minutes to go back to that link back in my first post. If you can't take the time to read more get through the beginning of chapters 3 and 4.

 

[j.razz1]

Okay, a slight problem I have found. Since renaming everything to get rid of spaces I found that I am getting #Name? in my fields on my "DA_Input_Fields".

I found out the problem. It is that I can't pull from more than one table in more than one form. I can use a query for this (but it isn't easily editable) or I can use subforms... which is what I will attempt to do now. So simple, but so hard to find the right answer for! Yet, I still don't understand why you can't pull from multiple tables. This would seem to make things a lot more easily doable within Access.

I'll keep reading.

j razz

 

[Beetle]

Yes. Just dates. When you click the field, a calendar will pop up.

The reason I asked is that it appears you are just entering different types of the same attribute, so your Request table probably only needs fields like the following;

tblRequests
*********
RequestID (Primary Key - Autonumber)
CaseID (Foreign Key to tblCases - Number)
RequestTypeID (Foreign Key to tblRequestTypes - Number)
RequestDate
Approved (Yes/No field)

along with a lookup table for Types of Requests (1st Request, 2nd Request, TSUContact, etc.)

tblRequestTypes
************
RequestTypeID (PK - Autonumber)
Description

Also, I don't see a need to have CaseOpen and CaseClosed in your Cases table. They're mutually exclusive. If a case is not open then it is closed. You only need one boolean (Yes/No) field for this.

I am attaching an example database to illustrate some of this. This db includes a few code examples as well that control things like;

• If a Request is approved it displays a message box asking if you want to close the case.
  
• If a case is closed it prevents you from entering any more requests for that case.
  
• There is also a search (combo) box in the header of the main form to select a client. This is pretty common form functionality so it will give you an example of how to implement it. The combo box is unbound and it uses a few lines of code in its After Update event to find the record you want.

I know you're not at that stage yet but it may give you some examples that you can reference some time later when you get to that point.

Another note. The main form (frmClients) has two subforms, one for Cases and one for Requests. When you select a Case in the first subform the related Requests are displayed in the second subform. This method uses a hidden text box on the main form that holds the CaseID from the first subform and is referenced in the Master/Child link of the second subform. I realize this is probably a bit over your head right now but if this example db represents essentially what you are trying to accomplish, then it will give you some ideas to work with.