Cascading Combobox to select table

[crummie]

Hi

I am designing my first database and are trying to figure out how to use a cascading combobox to select a record from one table to be added to another table.

I was looking at having a series of tables for each year listing the sites our team is to inspect for each year. I then wanted to use a combobox on a form so the user can select a year in the first box then in the second combobox to select a site from the corresponding years table.

Is this possible or do I need to alter the structure of the database?


Thanks
Crummie

[PhilOfWalton]

What you want is certainly achievable.

What is the structure of your Db. An image of your relationships with the tables fully expanded would be a great help

Phil

[NeoPa]

You may find Cascaded Form Filtering helpful.

[crummie]

Hi Phil

Thanks for your reply.

The image Access DB V1 is the structure and relationships I have where each inspection program had its own table (Tables within the red oval) and each year's inspection targets for each program would have a separate table (Green oval). This is where I wanted to be able to choose a year from a dropbox then have another dropbox to select a program then based on those results have a third combobox to select the inspection premises from the relevant inspection targets table.

However over the weekend I thought that the structure in image Access DB V2 would probably simplify the process by having all the inspection records in 1 table (Red oval) and the list of inspection targets for each year as fields within an overall list (Green oval). Hopefully I'm on the right track in this thinking.

Either way I would like to have the data entry form to have the cascading boxes to select a inspection target. Hopefully this makes sense.

Any help would be greatly appreciated.
Crummie

[PhilOfWalton]

Thanks for the images, but unfortunately they are too small to read. As I magnify them, the just get more blurred. Not your fault, it is a failing with this website that it is not very good with images.

Can you send them directly to me, or if the information is not confidential, send a zip file of your Db.

Phil

[crummie]

Hi Phil

Sorry about the images. Please find attached the zipped files for the two versions of the database which I took the screen shots from.

Thanks
Crummie

[PhilOfWalton]

Thanks for that, Crummie. I am trying to make head or tail of of it.
Can you explain a little bit about what the whole Db is used for.

I notice there a a few 1 to 1 relationships, and while perfectly acceptable, they are a little unusual is there is an implication they various tables are really 1 big table.

I also notice there is a lookup in SupportingInspectors in your InspectionTecords table. I would try to avoid these as they are inclined to obfuscate where data is coming from.

I am pleased to see there are no spaces in your field names, but may I suggest, unless you are a super fast typist that you keep names as short as is meaningful.
You will also find it helpful (again not always possible) to give the same name to primary keys and foreign keys, so for example in table Breach Records you have "BreachType" as the FK and "BreachTypeID" in the BreachTypeList table. "BreachTypeID" would be better in both.

Again very confusingly, you have unrelated "ComplianceToolID" in both BreachToolRecords and ComlianceToolList.

I hope you don't think I am being critical. What you have done looks brilliant for anyone's first database. I hope you will just regard these remarks as helpful pointers.

As I said, if you can describe a bit more about the overall purpose of your DB, I will be delighted to help you.

Phil

[crummie]

Hi Phil

Thanks for the feedback regarding my DB.

I work in a compliance team and the DB is to keep track of our teams inspections. We currently use multiple Excel spreadsheets and we want to change to Access to get around some of the limitations of Excel.

Our team conducts inspections of numerous sites which can be associated with several inspection programs each year. I need users to be able to select an inspection site from a program table(s) then enter the details in the inspection table(s). I also need them to enter info in relating to the report that goes with each inspection. From each inspection there may be multiple breaches that need to be entered and possibly a licence amendment which users would also need to enter the info for.

I hope this makes sense.

Regards
Crummie

[PhilOfWalton]

I now have a better understanding of what is wanted, and it is obviously quite complex.

It really would be helpful to talk it through. If you have Skype, it would save a lot of time and typing as I think we need to review the tables and get the normalisation correct.

If you want to proceed along those lines, send me a personal message with your Skype details and we can chat.

Phil

[crummie]

Hi Phil

Sorry I don't have Skype available at work, so I will try to explain as best I can below.
I have made changes to my DB (attached) based on your feedback regarding the 1x1 relationships and I now only have one Inspection Records table. The issue I now have is selecting the inspection targets for use on the data entry forms and I guess this will depend on how I set-up the Inspection Target table(s).
The two options I have thought of is having a series of tables for each program each year or having a table for each program which has a field for each year. Hopefully there is a better way of achieving this that I haven't thought of?
The thing I'm struggling with is how to use the selections made in two comboboxes to select a single target whether that be a particular table as in my first option or a particular field within a table for my second option.
I thought of using a separate table as a lookup table but I'm not sure if I would be able to use the table name as a data record and then use this in a query or reference it in code?

Clear as mud I'm sure!

[crummie]

Just though of a another option which may make it easier.

If I have a table for each year and a field within the table for each program, could I then use the first combobox to select the correct years table and the second combobox to select the correct table field to run a query which returns the correct inspection targets?

Regards
Antony

[PhilOfWalton]

Hi Antony

Am looking at your DB now and have made a few obvious changes, but my problem is terminology, which is why I wanted to speak to you.

At this stage, the important thing is to get the table structure right. I know your original question is about cascading combo boxes, but that is irrelevant until we get the table structure sorted out.

As databases get more complex, it is vital to get the structure and field names correct, as changing things later on gets more and more time consuming. I fully understand the frustration in not getting down to designing forms and reports, but first things first.

So please correct me if I am wrong (pretty sure I still haven't grasped this correctly)

Each year (or several times a year - please clarify), you select a site (Unlicenced Premises Site) and inspect it for 1 (or more - please clarify) problems (I think you call these programs).
As a result of the inspection you produce a report (Assessment Record). I presume that if you are inspecting for more than 1 problem, you produce an Assessment record for each one. (Again please confirm).

I understand the inspectors, Breaches & Compliance.

I do not understand the PrescribedPremisesInstrumentList table or the CWInstrumentList table.

The former appears to contain date information, which almost certainly is incorrect.

So can you be as specific as you can about how your company does things and what information they want from the database.

Phil

[crummie]

Hi Phil

Thanks for your help, as I struggle through this process I'm realising that the structure is one of the most important aspects so any help is most appreciated.

Each year we have multiple inspection programs which our team undertakes, these programs are decided on at the start of the inspection year (July).
Each program has a list of sites/premises which are to be inspected.
One issue I have is that 1 site may be in more than 1 program. I originally thought of having separate tables for each program each year but then thought it may work better to have all sites in a single table and have a fields for each year's program or to have a table for each year with fields for each program.
The PrescribedPremisesInstrumentList and CWInstrumentList tables were intended to be lists of some of sites with fields for each year's programs.
Any advice on the best structure for this would be most welcome as I think the rest of the DB structure is fairly straight forward in comparison.

For each inspection there will be an assessment (report) which will need to completed (outside the DB) and details recorded (AssessmentRecord table). For each inspection there could a licence amendment required (AmendmentRecord table) and numerous breaches (BreachRecord table) which need to be recorded. For each breach I would then like to be able to track what action(s) were taken and when (BreachToolRecords table).

The main requirements I have are to track the number of inspections in each program and gather information on the number and type of breaches & amendments. This information will be required for fortnightly/quarterly/annual reporting.

I hope this makes things a little clearer.

Crummie

[PhilOfWalton]

Right, Antony, we're getting somewhere.

Please accept these as recommendations, no guarantees they are correct.

Firstly much of the information about the sites is duplicated in 3 tables. Consider using a single table and having an indicator (SiteType) to indicate whether it's a PrescribedPremisesInstrumentList site, a UnlicencedPremisesList site or a CWInstrumentList site.
When entering the details in a form, we can determine which fields are required to be filled in and which are irrelevant.... But that is for later.

So a Table of Sites

TblSites
    SiteID         Autonumber    PK
    Site           Text          No Duplicates
    SiteType       Number        ' as above
    SiteAddress    Text
    etc
 

A "Program" appears to be basically a list of sites and the date they are to be inspected.
So we have a table of years

TblYears
    YearID     AutoNumber    PK
    TheYear    Number Long    No Duplicates  ' 2017, 2018 etc
    YearStart  Date
    YearEnd    Date
 

We now need to join those sites with a date to form a "program"

TblJoinSiteYear
    YearID         Number Long    JointPK
    SiteID         Number Long    JointPK
    InspectionDate Date
 

Your Assessment Record now has some redundant fields, but needs an additional field ProgramID. See Attached.

Without data, it is difficult to check.

General point is as I understand it, Access worlds faster with numerical indexes rather than text indexes.

Hope this helps.

Phil

[NeoPa]

Antony:
as I struggle through this process I'm realising that the structure is one of the most important aspects so any help is most appreciated.

I couldn't read that and refrain from commenting. It's actually the most important aspect bar none, but you've made a great realisation there. Good for you.

[NeoPa]

Phil:
General point is as I understand it, Access worlds faster with numerical indexes rather than text indexes.

That's generally true Phil, but not an absolute. Autonumbers are Long Integers of 64 bits. That's four bytes of space. A string of that length (Two chars unicode or four chars ASCII) would have exactly the same efficiency depending to a small extent also on the order of adding values but that is not a big factor.

The size effects how many entries can be saved in an index page, which usually equates to a single disk read. The more pointers loaded per read, the fewer the reads and the more efficient the index is for reading. Writing is more complex but affected by the same factors for the same reasons.

Obiously, even smaller fields can be more efficient even than Longs/AutoNumbers.

Another factor is that Numbers typically can contain more valid values than a string, unless you're using every available character code of course. That feature typically means that more entries can be stored using fewer bytes of data - hence your original assessment is generally correct.

[crummie]

Hi Phil

Thanks for the recommendations.

I'm not sure if having one table of sites with a site type field would work. We have approximately 2500 sites licensed with our department of which we inspect about 25% per year. Each year we have multiple programs and each program we have looks at different aspects. An issue may arise where one site may end up on more than one program.

Crummie

[crummie]

Hi Phil

Taking a lead from your suggestions above, would the attached structure work?

Regards
Crummie

[PhilOfWalton]

That looks more like it.
The only criticism is I hate these lookup tables that you are using in TblJoinSiteYear (SupportingInstructorLookup)

Have a look at the way I handled it in the Version 5 I sent you.

So I suggest, the next step is to populate the tables, and see what it looks like.

Phil

[crummie]

Hi Phil

Thanks for all your help. With that structure should I combine the Inspection Join table and the Assessment table as there should only ever be 1 assessment per inspection?

I have added the multivalue field to record the support inspectors which there could be more than 1 per inspection. Not sure if this field will be used at this stage, hoping to add a simple report/form down the track which may display this info.

Regards
Crummie

[PhilOfWalton]

Yes, Crummie, that is what is shown in version V - Phil.

However if you look at the way I handled the Inspectors in V 4, you will find that you can have as many Inspectors as you like for each assessment and designate 1 (or more) to be lead inspector.

That way, you also can easily ask which assessments was a particular inspector involved with.

Phil