Filter datasheet via combo box selection

[vector39]

Hi everyone

I know this topic has been covered before and my issue is similar but there's one key difference. Here is a screenshot of part of the user interface I've been working on. To filter this datasheet in split form, the user can ideally either type in an agency name (which filters by agency) or select a program code from the combo box.

The search bar works just fine. Here is the code and SQL

VBA:

Option Compare Database
Option Explicit


Private Sub cmdReset_Click()
Me.txtSearch = ""
    Me.SrchText = ""
    DoCmd.Requery
    Me.txtSearch.SetFocus
End Sub


Private Sub Form_Load()
'DoCmd.GoToRecord , , acNewRec
End Sub


Private Sub SrchText_AfterUpdate()
Me.SrchText.Requery
End Sub


Private Sub txtSearch_Change()
'Create a string (text) variable


    Dim vSearchString As String
    vSearchString = txtSearch.Text
    SrchText.Value = vSearchString
    If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
        Exit Sub
        End If
    'Me.SearchResults = Me.SearchResults.ItemData(1)
    
    'Me.SearchResults.SetFocus
    DoCmd.Requery
    Me.txtSearch.SetFocus
    If Not IsNull(Len(Me.txtSearch)) Then
        Me.txtSearch.SelStart = Len(Me.txtSearch)
        End If
End Sub

SQL:

SELECT tblAgencyInformation.Agency, tblAgencyInformation.Subsidiary, tblAgencyInformation.ProgramCodes, tblAgencyInformation.ProgramNotes, tblAgencyInformation.Address, tblAgencyInformation.City, tblAgencyInformation.Prov, tblAgencyInformation.PostalCode, tblAgencyInformation.FirstName, tblAgencyInformation.LastName, tblAgencyInformation.Position, tblAgencyInformation.ContactNotes, tblAgencyInformation.OriginalEmail, tblAgencyInformation.PhoneNumber, tblAgencyInformation.PhoneNotes, tblAgencyInformation.AAExpiryDate, tblAgencyInformation.InsuranceExpiry, tblAgencyInformation.InsuranceExpiryComments, tblAgencyInformation.WSIBYesNo, tblAgencyInformation.WSIBNotes
FROM tblAgencyInformation
WHERE (((tblAgencyInformation.Agency) Like "*" & [Forms]![frmAgencyLookup_FA]![SrchText] & "*"));

Now I think the problem is largely because initially, the programcodes field in the main table used to be program acronyms (e.g. BsCE) but was replaced with it's respective numeric value (e.g. 0212). In addition to this, the entire program codes table was later added to this database called tblProgramCodes. A lot of the values in this table are not present in the programcodes field at all. Here is a pic of the relationship (all one-to-one enforced referential integrity).



I also made an query to append the agency and program codes tables with the following structure:
apc_ID, autonumber, primary key
apc_CodeID, number, links to tblProgramCodes.ID
apc_Agency, number, links to AgencyInfo.ID

And this is what the table looks like:

I've play around with the combo box by doing things like: setting it's control source to the appropriate field, or unbound, putting DoCmd.Requery in the combo box's after_update event property, and adding forms!frmAgencyLook_FA!cboProgramCodes in the query's criteria but nothing has worked and I'm sure it has something to do with how these tables are all related. As usual, any sort of feedback or constructive criticism is always appreciated!

[June7]

Your 1-to-1 linking makes no sense to me. Why would AgencyID be equivalent to ProgramCodeID and SubsidiaryID?

[John_G]

If SubidiaryID, AgencyID and ProgramCodeID are all Autonumber type, that is guaranteed fo fail. Tables should rarely, if ever, be linked on AutoNumber fields. You are far more likely to link tables by using an Autonumbr PK in one, and linking to an FK field in the other.

So above, you would join tblSubsidiaryInformation to tblAgencyInformation using tblSubsidiaryInformation.SubsidiaryID and tblAgencyInformation.Subsidiary.

In tblAgencyInformation, I am concerned about the field name ProgramCodes - plural. Does that mean that field can contain multiple values, i.e. is a multi-value field? If so, I doubt if it can be used in a relationship. If it only contains one value, then you can use it to join to tblProgramCodes.

But:

Can an Agency have more than one subsidiary?
Can an Agency or Subdidiary have more than one Program Code associated with it?
Can any one Program code apply to more than one Agency or Subsidiary?

If yes to any of these, you are going to have to redesign your database.

[orange]

Further to comments made by others, can you step back and tell us in plain English about the business involved? What is the business?
Your showing a form to generate reports, but we need to know "the things" important to your business and how those things relate to each other.

Table structure and relationships are critical to database.

[vector39]

Can an Agency have more than one subsidiary?
Can an Agency or Subdidiary have more than one Program Code associated with it?
Can any one Program code apply to more than one Agency or Subsidiary?

Hi thank you for reply. You're right the field ProgramCodes in the agency table, varies, some cells contain one, multiple or no value (NOCODE). What would you suggest in this case be done? Should I add a new line for each program code so that every cell has only one value? As for subsidiary..

An agency can have one, multiple or no subsidiaries. An agency or subsidiary can also have more than one program code. And lastly yes, any one program code can apply to more than one agency/subsidiary.

[vector39]

Basically this database will be used to monitor and track agency information that educational programs use for student internships. Many agencies have subsidiaries which are child components of the main site, thus it wouldn't make sense to have a subsidiary without an agency. I know for people using it, being able to find records by program code is beneficial because many agencies can have multiple program codes or none. The program codes are the numeric value attached to the program name (for example 3231 is for Nursing). So basically, an agency with this code would possibly accept students from that program during their internship. What users need to do is look up agency info, who is the subsidiary(if any), what are the program codes (if any) and other details like contact name, address, city, phone, etc. In addition to this, there needs to be a way for this database to monitor which site is active. Forget all the historical data that's already in here. MOVING FORWARD there needs to be some kind of feature that enables users to see which agency is active based on program code and date (e.g. 1324 | 2017/18 Fall). This part can be talked about further in detail but for now, users looking up information, entering in new information and looking at reports based on a criteria is what I'm focusing on atm. This database will eventually go into sharepoint, so do you think cleaning up the data on sharepoint would be easier? Also do you have any advice for new data that needs to go into this database while it's still under construction?

[orange]

In my view it seems you have a data structure issue. However, exactly what the issue is depends on the details of your business.
In simple English you have
Agencies which may have 0,1 or many Subsidiary(s)
Students
Internships
Educational Programs that have codes and names
and possibly Contacts

How do these relate to each other in clear business terms? The relationships depicted in your post #1 make no sense as others have mentioned.

I don't know Sharepoint, but I don't think it matters if it's Access or Sharepoint--you have to get the data structure set up to match your business.

Describe an example of Student: Jane Doe and Program Nursing --tell us about her in the context of your business and requirement (simple English, no jargon).
Good luck

[June7]

Agency and Program have many-to-many relationship. This requires a 'junction' table of records to define associations of agency and code. Or use of multi-value field in tblAgencyInformation. The same for program codes in tblSubsidiaries. I NEVER use multi-value field. You should thoroughly understand what a multi-value field is and how it works before deciding to use. Web search. https://support.office.com/en-us/art...C-6DE9BEBBEC31

Agency and subsidiary have a 1-to-many relationship. Each subsidiary can associate with only 1 agency and each agency can have many subsidiaries. AgencyID primary key would be saved into tblSubsidiary as foreign key. Or, again, multi-value field in tblAgencyInformation for subsidiary IDs. However, if a subsidiary can also have its own subsidiaries, tblSubsidiary may be unnecessary. tblAgencyInformation could just have another field for ParentAgency. This table would have recursive data - like a family tree. Again, web search the topic.

[vector39]

Hi everyone, I apologize for the tardy reply.

To sum it up in plain english the point of this database is for the educational placement department at a local community college to monitor and track agency information. Different agencies and subsidiaries (child component of agency) have agreements with this school, so basically what users need to know are, which agencies are involved with the school based on program codes and seeing if their contract is going to expire soon via insurance and other date fields.



After doing some data cleanup, I realized that some agency information with subsidiaries is different because all the information after the subsidiary field is information about the subsidiary (programcodes/notes, address, city, etc) and not actually agency information, so technically all those fields should be N/A in this agency table for those specific records. I have separated these records into another table called tblSubsidiaryInformation. It also occurred to me that some records have more than one program code value (e.g. 2321, 2910), and I think this could potentially cause problems done the road, so I created a separate program codes table and readjusting this main agency table so that each agency row only has one program code value. Afterwards I was also told that some program codes are not presently in the main agency table but will eventually be added so I think creating this table was a good idea.

I have 3 tables:
- An Agency can have 1, 0 or multiple relationships
- Subsidiaries must have one or more agencies
- Both agencies and subsidiarys can have 1, 0 or multiple program codes




Presently reading through all your comments and playing with the relationships atm. Trying to connect SubsidiaryID (tblSubsidiaryInformation) to Subsidiary (tblAgencyInformation) but getting an error saying Relationship must be on the same number of fields with same data types and I thank all of you for your feedback and give you an update soon enough.

UPDATE:
I'm not sure I did this right but this is what I did.

I added another field in tblAgencyInformation called Subsidiary_FK. I was able to connect the two tables however the subsidiaryID doesn't match the subsidiary_fk

vs

[John_G]

Subsidiaries must have one or more agencies

This doesn't make sense to me, (I think it's the reverse) but you know your data. However, your screencap of tblAgencyInformation shows the opposite - "Accurate Hearing" has two subsidiaries, i.e. 1-to-many as well. This, plus your quote above, means the tblAgencyInformation to tblSubsidiaryInformation relationship is many-to-many. Many-to-many relationships require a third table, (sometimes called a junction table) to link the two. In your case, that table (I'll call it tblJunction) would include at a minimum, 2 fields:

AgencyID (FK to tblAgencyInformation)
SubsidiaryID (FK to tblSubsidiaryInformation)

The data structure in tblAgencyInformation is not correct. "Accurate Hearing" should not have two records for it, it needs only one. tblAgencyInformation or tblJunction would then have two records in it for agency "Accurate Hearing", one for each of the two subsidiaries.

If the Agency - Subsidiary relationship really is many-to-many, then you can't do what follows. If the relationship is one-to-many, from looking at your data, then I think you could combine the Agency and Subsidiary tables into one. All you would need is two new fields: 1) a Yes/No field to indicate whether a record is a subsidiary or not, and 2) the ID of the "parent" agency if there is one.

For codes, I would make a separate table linking Agencies to Codes, since that is a many-to-many as well:

CodeID (FK to tblCodes)
AgencyID (FK to tblAgencyInformation)

Actually, if you can do it, having Agencies and Subsidiaries in one table would make life a lot easier if you are searching by code but don't care whether it's a subsidiary or not.

Can you clarify the bit about the relationship between Agencies and Subsidiaries be fore we go further?

[vector39]

Of course. You're right, Accurate Hearing does have 2 subsidiaries

The reason I say subsidiaries must have at least one agency is because it realistically, it wouldn't make sense for the child component to be here without first having a parent. Basically, the relationship between agencies and subsidiaries is that this whole database revolves around agency information and most of the agencies in this db don't even have a subsidiary but for the ones that do, it's important to see what their information is too. E.g. Accurate Hearing has 2 subsidiaries, a Halifax location, and Sackville location, therefore, do they have the same program codes? what's their contact info? etc. Looking back at this data, each subsidiary belongs to only one agency, but one agency can have many subsidiaries with different locations. I guess, it doesn't really matter too too much if users need to differentiate who is subsidiary or not, I just thought I'd make a separate table because if the user is adding a new agency with new subsidiary info, then we'd want to capture the subsidiary information as well, as to avoid the original problem of mixing up agency vs subsidiary info and having to separate them.

[orange]

Since Agency and its subsidiary(ies) are all "organizations", I recommend a single table tAgency. It the table have an OrgID or AgencyId--whatever you want to call it and have details for that "organization" --name, address, phone.... and also a field called Subsidiary. Subsidiary will be populated with the OrgId of the Parent.

Placements and ProgramCode would be in a separate table. That table will have relationships based on your business rules/facts.
Every Org will have details in the tAgency, and each Subsidiary will have its subsidiary field valued with the parent's orgID.

[June7]

Agree with Orange, this is what I describe in post 8 - recursive data.

[vector39]

Alright, I think I'm starting to see what you guys are saying now, more and more when I think about it, it's a bit redundant to have tblSubsidiaryInformation. So let's focus our attention on the tblAgencyInformation and tblProgramCodes relationship. I made a new query to try and combine these two and the query looks as follows:

And it's SQL is:

SELECT tblAgencyInformation.AgencyID, tblProgramCodes.ProgramCodeID, tblProgramCodes.ProgramCode, tblProgramCodes.ProgramName
FROM tblProgramCodes INNER JOIN tblAgencyInformation ON tblProgramCodes.ProgramCode = tblAgencyInformation.[ProgramCode];

So although this looks correct. My next (incredibly novice) question now is, how do I "use" this query? I know I need to make a new table that combines agency and program codes but every time I click "Make Table", nothing happens.

[June7]

Why do you need to make a table? The two tables have a relationship. Use query like a table as source for reports