Solved New to Access, little SQL knowledge - cascading combo boxes query (1 Viewer)

[HeadAgainstABrickWall2021]

Hi all,

CURRENT KNOWLEDGE LEVEL: Tables and Reports all good, simple queries all good, learning Update Queries and Union Queries, Form formatting all good, comboboxes NOT GOOD.

My database has four tables, with four related forms for data entry:

Customer
Case (multiple cases per customer)
Event (multiple events per case)
Contact (multiple contacts per event)

I have set my Customer ID as the primary key in tbl.Customer, and as a foreign key for all others. Case ID is primary key for tbl.Case, which is foreign key for Event and Contact. Event ID is primary key for tbl.Event, which is foreign key for Contact.

I am trying to achieve two key things at the moment:

1. Use cascading comboboxes in Case, Event and Contact forms to populate Surname and First name, which will then update the foreign keys in that form/table.
2. Trigger a message/warning if the person's name is NOT in tbl.Customers, and request that this be updated BUT still allow data entry (as updates are happening live, there isnt always time to go back and populate the Customers form on the spot, and I'm hoping to reduce the amount of backtracking.

I have followed tutorials online for:
Cascading comboboxes
Bound comboboxes
Query returns
and can get the physical results, but they aren't populating my fields as desired. I currently have a bound combobox in my Case tab which displays the names as I want them too, but wont let me select anything!

Any help would be appreciated - SQL welcome!

With thanks,
HAABW

 

[June7]

Use combobox NotInList event to allow adding new customers 'on the fly' during data entry.

Surname and FirstName data should not be duplicated into Case, Event, Contact tables - only CustomerID should be saved. Eliminate this cascading combobox feature. Single combobox RowSource:

SELECT CustomerID, LastName & ", " & FirstName AS FullName From Customers;

 

[HeadAgainstABrickWall2021]

The surnames and first names need to be in the tables for now as there is a lot of old data being ported in. Can I use that combonix as a calculated source for a 'Name and ID' field in the respective tables?

 

[jdraw]

Before you get too far into a physical Access database, how about telling us your situation.
You are new to the forum, so bear with me a little.


You have

old data to be ported in

.
Where does that data come from (where is it now)? What is the output/final purpose of this new database?
What is your proposed process(es) to get from where you are to where you want to be (step by step)?

Existing Data--->Some Process(es)---->Final Set up

Do you have a model of your proposed database?

 

[HeadAgainstABrickWall2021]

Not a problem at all!

I work with a small nonprofit. This database is intended to keep records of persons interacting with our small-scale social aid program - we need to keep track of who has ever accessed the program (Customers), the times they have applied and been approved for aid (Case), the individual aid packages they have received during period of need (Event), and tracking the contact and follow-up around delivering those aid packages (Contact).

We have approx. 5 years of data from a spreadsheet which used CustomerID + surname + first name as the identifiers in the respective worksheets. This spreadsheet was so bogged down by the time I got to it that it would take 5-10 minutes to open, and has a lot of missing data.

My first assigned task was to create a new database for immediate use - I have ported all of the Customers and Cases in from the spreadsheet as they were easy to transform and move in. I haven't added any Events from the old dataset as there were thousands with the majority of the information stored in a free text box, so a lot of analysis required to transform it into fields for import. This will be my next step once I have the database running smoothly - it is currently live and is going well, I'm just trying to make the processes as smooth as I can and reduce the risk of data entry error (and there is a LOT of error in the old dataset ) hence wanting to use dropdowns rather than free text where possible.

As this database is being accessed by multiple people, I have hidden navigation from users and am having them enter data and generate reports from a form menu (it will be split eventually), which is going really smoothly. The problem is that I want to avoid the person having to print out the Customer ID list, or try and remember the ID, when entering new events and contacts - hence wanting to have the name entered on the forms (so the suggestion from June7 should tick that box nicely!).

Re: wanting names in the tables, I am not the data analysis person at my work - that role is held by someone who has limited understanding about data analysis and no experience at all with Access (don't ask, it's a whole thing). They are passable with Excel so exporting results for analysis is fine, but my concern is that if the names aren't in the table there will be risk of error if the person doesn't understand how primary and foreign keys work.

I am only on a short contract with no guarantee of funding to extend this, so I am trying to futureproof as best I can.

 

[June7]

Combobox RowSource:

SELECT CustomerID, Surname & ", " & FirstName AS FullName, FirstName, Surname From Customers;

Code in combobox AfterUpdate event:

Me!FirstName = Me.ComboboxName.Column(2)
Me!Surname = Me.ComboboxName.Column(3)

But really should import data into properly normalized data structure. Abandon the compound key and don't duplicate data into multiple tables.

Data analysis person 'has limited understanding about data analysis' - really!!? You are developer and should provide a db interface that doesn't require users to understand database principles.

If you can't select anything in combobox bound to field, then possibly form is bound to a non-editable query.

 

[jdraw]

need to keep track of:

- who has ever accessed the program (Customers)
(is there consistency in Customer identification or is it an issue?)
- the times they have applied and been approved for aid (Case)
(do you track unapproved applications)(do you have reasons/conditions for approval)
- the individual aid packages they have received during period of need (Event)
- tracking the contact
(How and how often is this done?)
- follow-up around delivering those aid packages (Contact)
(How and how often is this done?)(is there some status or summary info kept on customer or contact)

Customer-->ApplicationForAid--> [Approved<--Aid Package]

Agency --->Contacts--->ContactFeedback

Can you show us your table designs (relationship window)?

Seems like [Approved<--Aid Package] is an Event??
and {ApplicationForAid-->Approved} represents a Case??

 

[HeadAgainstABrickWall2021]

- who has ever accessed the program (Customers)
(is there consistency in Customer identification or is it an issue?) *Consistency is an issue. I have been able to clean it up where there have been spelling mistakes in names, or the ID number has been missed.*
- the times they have applied and been approved for aid (Case)
(do you track unapproved applications) *we do, to make sure that person's being rejected are because they don't meet criteria and allow for criteria revision if these don't seem to be meeting community need (e.g. duration of crisis changed drastically during COVID)* (do you have reasons/conditions for approval) *We do - persons who have been approved in the past don't need a full reassessment but are assessed for suitability by the program manager. Referrals can be by referral form or in person*
- the individual aid packages they have received during period of need (Event)
- tracking the contact
(How and how often is this done?) *This is a new part of the dataset, contacts weren't previously being tracked so a lot was happening off the books. This could be multiple times, e.g. phone call to advise package ready, person attends office to collect, followup call to confirm suitability*
- follow-up around delivering those aid packages (Contact)
(How and how often is this done?) *As above* (is there some status or summary info kept on customer or contact) *Referral status is open or closed - I have an update Query to return referral status based on event outcome, e.g. someone who was approved for aid to get a job would have their case closed on provision of work clothes for first day of work*

Customer-->ApplicationForAid--> [Approved<--Aid Package]

Agency --->Contacts--->ContactFeedback *Contacts is a very basic table at this time as this is a new process - these were all occurring without tracking previously so we don't know how much staff time is taken up with fielding contact enquiries. These can come from the person receiving aid, their family members, support team, agencies, etc - I am tracking contact type and contact person type but the name/agency is freetext*

Can you show us your table designs (relationship window)? * I will double check when I get in to work on Monday - from memory at this stage I have: field PKCustomerId in tbl.Customers linked to field FKCustomerId in all other tables (one-to-many); field PKCaseId in tbl.Case linked to field FKCaseID in tbl.Events and tbl.Contact (one-to-many). Field PKEventID in tbl.Events is not linked to anything. Field PKContactId in tbl.Contacts is not linked to anything. *

Seems like [Approved<--Aid Package] is an Event?? *Correct - note aid package may be distributed across many contacts e.g. on [DATE1] person received taxi voucher to attend interview, on [DATE2] person received steel toe boots for job trial*
and {ApplicationForAid-->Approved} represents a Case?? *ApplicationForAid regardless of approval status represents a case - case can be closed on referral due to not meeting criteria*

 

[jdraw]

I don't know if this will help but it's a first draft of potential tables you may be dealing with.
Good luck.

 

[arnelgp]

simple sample.

 

[jdraw]

I looked at arnel's sample database, here is its underlying model (tables and relationships). It is more refined than the draft I gave. It appears to support most of what you need - at least it is a great starting point that you can adjust/extend as needed. Thanks arnel.
You should review some sample scenarios based on your existing data and work them against the model. Ensure that the model supports your needs. You may have to add some attributes(fields) to handle the details you are dealing with. See my stump the model info. Also, I'm not sure how much info you need for Followup.
Date is a reserved word in Access so you should qualify it eg CaseStartDate, EventContactDate or similar etc.

Also NOTE: It is much easier to adjust a model than a physical database. A well tested and vetted model will serve as a blueprint for your physical database.

Good luck with your project.

 

[HeadAgainstABrickWall2021]

Hi JDraw, thanks so much for this, very helpful. I am trying to split a bit more out of my four tables where there is a 1-1 relationship to allow use of subforms (e.g. one section in referrals is 'referrer', so can subform that out). Work in progress when starting from an existing spreadsheets with mandatory reporting requirements!

 

[HeadAgainstABrickWall2021]

Combobox RowSource:

SELECT CustomerID, Surname & ", " & FirstName AS FullName, FirstName, Surname From Customers;

Code in combobox AfterUpdate event:

Me!FirstName = Me.ComboboxName.Column(2)
Me!Surname = Me.ComboboxName.Column(3)

But really should import data into properly normalized data structure. Abandon the compound key and don't duplicate data into multiple tables.

Data analysis person 'has limited understanding about data analysis' - really!!? You are developer and should provide a db interface that doesn't require users to understand database principles.

If you can't select anything in combobox bound to field, then possibly form is bound to a non-editable query.

Hi June7, your suggestion worked great with displaying the data but I'm running into two issues:

1. Data is not updating to table (i.e. CustomerID)
2. When going back to previous records the combobox is showing as blank, meaning it is impossible to tell which Customer the form is referring to. While I appreciate that we shouldnt be storing data duplicates, names and DOB need to be displayed on each form (Customer, Case, Event, Contact) so that managers can see at a glance what happened on a given day and revise content if there is anything in there inconsistent with protocol (e.g. "Person showed up smelling bad" needs to be reframed to "Person presented untidily and with hygiene concerns, recommend further discussion").

Also, as a side-note, I'm not a developer by trade or role, I'm just the go-to "oh, they're good with technology!" person who has been given a challenge. Learning on the job!

 

[June7]

1. Why? If combobox is bound to correct field, CustomerID of selected item should save to record.

2. Cascading combobox will not show alias values for records that have data which has been filtered out of the combobox RowSource. This is easily rectified with code on single view form. However, gets complicated with continuous and datasheet views. Well-known issue. https://accessusergroups.org/et/faqwd/cascading-combo-box-in-continuous-form-jan-2021/

I am not a developer by trade or role either. I learned as incidental duties to my job. I am now retired.

 

[jdraw]

I don't see any 1-1 relationships - perhaps you could tell us more detail. As I mentioned, work from a model to ensure all the pieces you need are identified, and work through sample scenarios to exercise and test the model. It might be time for you to show us your evolving database. People here will offer advice, but they need some info and context and even examples/samples of issues for that advice to be focused. In general we don't like to guess at what an issue or relationship is - so details may be quickest route to a solution.

 

[NauticalGent]

I am not a developer by trade or role either.

Maybe not, but your abilities stand you in good stead if you decided to be one. That's what I think anyway...

 

[NauticalGent]

I'm just the go-to "oh, they're good with technology!" person who has been given a challenge.

That is a common tale here at AWF...

 

[HeadAgainstABrickWall2021]

I don't see any 1-1 relationships - perhaps you could tell us more detail. As I mentioned, work from a model to ensure all the pieces you need are identified, and work through sample scenarios to exercise and test the model. It might be time for you to show us your evolving database. People here will offer advice, but they need some info and context and even examples/samples of issues for that advice to be focused. In general we don't like to guess at what an issue or relationship is - so details may be quickest route to a solution.

I am slowly splicing out extra data sections that are in the respective tables, e.g. 'Management meeting decisionmaking' fields (multiple steps that need to be captured) under 'Event' because there will be one management meeting decision outcome per event. The fields I describe were just the bare bones I originally needed assistance on, there are heeeeaaaaps of others in these fields.

I will do up a mock-up and post it, just have to remove a lot of branding and identifying names because we are very easily identifiable and subcontracted to government so don't want to introduce any risk of data breaching.

 

[HeadAgainstABrickWall2021]

EDIT: Dont worry all I figured it out! Hooray!!!! Populating textboxes that are hidden in Add mode but visible in Edit mode

Hi all, please find attached stripped database - I have removed sections and fields unrelated to current focus (sorry for error messages)

I need to achieve the following:

[Example - add New Referral]
1. Select name from dropdown list.
2. ProgramID field is populated with value that matches name in dropdown list.
3. ProgramID, surname and first name save to tblReferrals.
4. When scrolling back through previous referrals, name and HRYC display in form that match record (I am happy to use OnEvent macros to hide the combobox and display alternate textboxs when displaying data for editing, very confident with that!)

If my contract gets extended I am hoping to refine the database and bring it closer in line with best practice in Access, once I've learned what that is!

 

[Jimis]

Hi all
I have a problem setting up a relational cascading combo box. The join table is confusing me on how to do this.
I want the "CustomerERP" combo to populate/depend on whatever the user selects on "Device customer" combo. I have setup query1 which is not doing what I want. My SQL knowledge is basic so I was trying to do it with the Access Query designer. I attach my db

Thanks in advance