can't understand access behaviour with relationship creation

[merlin777]

The table are related by a number not by a name.

I've added a query to the example called qryShowJoinedTables that shows how typically you use records from two tables.

Thanks stopher. For the full name I'm actually using 2 fields from the same table rather than 2 separate ones. Is that the same?

I think I'm gradually seeing the light. The wholename field in the appointments table is a number datatype. Whichever method you use to select the name displays the first and last name fields from the therapist table but when you select one the therapistid field (which is a number) is the value that is stored in the wholename field in the appointments table? (Please say that's right!).

Now I just have to understand the different ways of making the selection displaying the two fields together.

 

[stopher]

Thanks stopher. For the full name I'm actually using 2 fields from the same table rather than 2 separate ones. Is that the same?

I think I'm gradually seeing the light. The wholename field in the appointments table is a number datatype. Whichever method you use to select the name displays the first and last name fields from the therapist table but when you select one the therapistid field (which is a number) is the value that is stored in the wholename field in the appointments table? (Please say that's right!).

Now I just have to understand the different ways of making the selection displaying the two fields together.

Take a good look at the example I gave. Look at the two tables and the fields in them. Look at the relationship view.

 

[merlin777]

Is there a reason you've called the field in the appointments table 'therapistid' rather than 'fullname' or something like that?

I'm thinking this might be the reason I've not understood relationships for so long. Am I right in thinking that wherever you use this field (appointments table, 'therapistid') its not just relating to the name fields but gives you access to any or all of the fields in therapisttable in the record with that id number?

 

[merlin777]

You could have appointments for therapists that don't exist for example.

Because Access is encouraging you to define lookups at tabular level and in order to do that it must know the relationship to the lookup table. But avoid using them. Read here.

OK - that's a good article... I'm a convert.

There must be a lot of alternative methods for putting the names into the name field (which I believe I should start thinking of as the id number for the record that contains the names) without using the lookup wizard. Do you know of any tutorials that run through the alternatives?

 

[stopher]

Is there a reason you've called the field in the appointments table 'therapistid' rather than 'fullname' or something like that?

I'm thinking this might be the reason I've not understood relationships for so long. Am I right in thinking that wherever you use this field (appointments table, 'therapistid') its not just relating to the name fields but gives you access to any or all of the fields in therapisttable in the record with that id number?

Look at the table called tblTherapists. There are are three fields. The second and third fields are the first name and surname respectively. The first field is the TherapistID field and is the primary key for this table. It is used as the identifier for records in this table.

We in the appointments table tblAppointments we reference the relevant record in the therapists table using its primary key. Hence the field in the appointments table is also called therepistID and is numeric.

hope that helps

 

[merlin777]

It does. You've been brilliant today, I really appreciate your time. I think I'm finally gripping the part that's been holding me back.

 

[The_Doc_Man]

Merlin, here is a point to ponder. Defining relationships for Access is totally not needed UNLESS... the relationship reflects a real-world relationship.

For the case you are describing, if you have an appointment record and a therapist record and you link the two by putting the prime key (PK) of that therapist's record into the therapist foreign key (FK) of the appointment record, then you have made an appointment for THAT therapist and no other therapist. This reflects a real-world event, so a relationship is appropriate.

Now, why is one PK and one FK? (Substitute the words here, and get used to it because that is the way we often post.) The question is, which one depends on the other? If you make an appointment, you have to have a therapist, but if you have a therapist, it is possible for him/her to not have an appointment (at a particular time). So the independent element here is the therapist who exists regardless of the existence of a 10:30 AM appointment, and the dependent element here is the 10:30 appointment, which cannot exist without a therapist. The INDEPENDENT elements gets PKs whereas the DEPENDENT elements get FKs.

The same would be true for having patient records, where you would put the patient ID (which again would be in a PK) into the appointment record in a slot for the patient FK. Because if you have an appointment without a patient, I doubt it's much good. Do you see how to make that kind of decision?

As to implementing it, earlier posts in this thread mentioned combo boxes. For snorts & giggles, you could implement a form to make an appointment where you use a wizard to build the patient and therapist selection boxes, which would be combo boxes.

So when you use the wizard (for either case), it asks you where your data will originate, and one of the answers is "I want to look up a value in a table." So it asks you to select a table, and you do. Then it says, "What fields do you want to see from this table?" and you tell it the ID and the name fields. You get to adjust the displayed width of the fields that you pick. We usually just drag the right border of the ID field to the left so that the ID isn't visible. BUT that doesn't mean it's not there when you make a selection. Anyway, once that choice is made, it asks you what value to store for that selection and your choices will be whatever fields you selected earlier - one of which will be the ID from that table.

Do this once for the patient selection and once for the therapist selection. Then of course you have to enter date and time, which is a different exercise to be addressed later only if you need it. At some point, the appointment is complete and you are done. You can then save the appointment record. When you do, it has two FKs, one pointing to the patient record via the patient's ID number, and on pointing to the therapist's record via the therapist ID number.

I understand that some of the tutorials can be daunting. The difficulty is simply this: It is hard to gauge what level of knowledge your viewers have, so in essence the makers of those videos have to shoot for an unknown target level.

 

[merlin777]

That's all very useful - thanks. A couple of questions if I may?

If I'm building a form with the wizard and I just want the name fields, why do I need to include the ID - especially if I'm then going to hide it?

regarding relationships in general, I've been given a range of opinions that stretch from relationships being unnessary, to being essential and like yours where they may or may not be needed depending on the circumstances (and in my case, from what you say they will be needed). All the reasoning in all cases seems reasonable so I'm having trouble drawing a final conclusion....

If I follow your walkthrough to create selection boxes, will access create relationships automatically? As I have 'real world' relationships, following your examples I can see that I will need them.

 

[merlin777]

The table are related by a number not by a name.

I've added a query to the example called qryShowJoinedTables that shows how typically you use records from two tables.

I've had a play and I think I understand. I changed the therapist table to add a job title, added the job title to the query and added brackets around it and added an extra combo box to the form to pick the therapist's full name and their job title.

It's given me a few more questions if you're not fed up with me yet....

as I understand it, when you use a query as the source for a combo box, effectively all columns of the query are displayed and it's up to you to hide the ones you don't want to see. Is that correct?

is the only way to hide a column in the pull down list in the combo box to change it's width to zero in the properties?

and finally, I can see the full name and "(job title)" in the combo box pull down but when I select one I still only see the full name field as the result in the box. How do I make the selection display as both? I have a feeling I've missed something important here.:banghead:

 

[Gasman]

That's all very useful - thanks. A couple of questions if I may?

If I'm building a form with the wizard and I just want the name fields, why do I need to include the ID - especially if I'm then going to hide it?

Generally the 'bound' field is the key you need to insert in the table, in most case the autonumber field.

So in your case you will have ID, FirstName & SecondName and perhaps Rank
eg 1,Geoff Evans, Snr Therapist

As you mention later you hide any fields by setting column width to 0.
I always have ID as my first field and then hide it, and that is my bound field to the table

 

[Gasman]

and finally, I can see the full name and "(job title)" in the combo box pull down but when I select one I still only see the full name field as the result in the box. How do I make the selection display as both? I have a feeling I've missed something important here.:banghead:

I've only ever used two field in my combos, ID and whatever I wish to show.
I *think* the other fields are for clarity, in case you have two John Smith, one Snr Therapist and the other Jnr Therapist.

I've just had a play and you are right, only the second field is shown after selection.
I'd have another control (textbox, locked or disabled) and show the extra fields in those.
https://msdn.microsoft.com/en-us/library/office/ff192660.aspx

Be aware that the column count starts at 0, so in your case you would want column(2) for the third field.

Invest in an Access book to get a head start.

HTH

 

[Uncle Gizmo]

it's up to you to hide the ones you don't want to see. Is that correct?

You might find these videos useful:-
https://sites.google.com/site/msaccess457966vmfjg/form-controls/combo-boxes/combo-box-videos

 

[merlin777]

Thanks for that. So what is it that dictates which field ends up in the combo box when you've selected a record with the pull down.

Put another way, if I want the job title to go into the combo box, what do I set (and I think from what you say it's column 3?). Is it in properties somewhere?

You are right about the book. I have several and usually try to get my answers there before posting but where they fail is that they can't interpret my question or my misunderstanding and point me to a relevant passage like a human can. The best book I have is access 2007 step by step.

 

[The_Doc_Man]

Thanks for that. So what is it that dictates which field ends up in the combo box when you've selected a record with the pull down.

Two answers:

Using the wizard, you told it to let you select fields from the table that is the source of your combo data. You can select a bunch of fields from there. The ID should be one of them because you want it in the box because that is the "value" of the box when you select. We call that the bound column or bound field. There is a property for it which you can see if you open up the Properties sheet. As Gasman warned, remember that for reasons known only to Microsoft, God, and Man (in about that order), column indexes in combo boxes are 0-based, not 1-based.

You can have any number of fields in the combo box, most of which are invisible due to being of 0 width. Look at the property Column Widths (for the widths, in order, of each selected column) and Column Count (for the number of columns). Note carefully that the number of columns is NOT the number of visible columns. It is the column widths that force invisibility by making the columns have 0 width.

Once you have chosen the bound column and then make a selection from the box, if you then reference the box without qualifying the chosen property, the value of the box matches the bound column from the chosen row. So if the box was called cboPatient and you selected a patient with ID 1234, then in VBA code at that moment, [cboPatient] has the value 1234.

Now, the technically more precise answer to the quoted question above is that there can be a query by name or a literal SQL "SELECT" query in the combo box property ROW SOURCE. The fields in the SELECT clause are the fields you selected during the wizard dialog and the FROM clause names the source table you identified during the wizard dialog. If you had to, you could build a dynamic string with SQL "SELECT ... FROM ... WHERE ... ORDER BY ..." etc. I.e. a full-blown SELECT query. In this context, the row source DOES NOT have to match (and in fact, for this application, usually DOES NOT MATCH) the form's source table. The ROW SOURCE can reference a different table for EACH combo box or list box that you have.

You use this very often when building "junction" tables, which is what your appointment table really is - it expresses a selective joining of two tables using the appointment table as the bridge or junction-connector between the two. Junction tables are the way that Access implements many-to-many relationships, as in "Many patients, many therapists."

(This is by far NOT the only application of junction tables or combo box selection methods, but it is a very good example of how they are used.)

 

[merlin777]

I think I may have misunderstood something.

When you create a combo box, there are 2 elements to it - what you display in the pull down list and what actually goes into the box when you make your selection.

You have a lot of control over what is in the list as you can include any field if you use the wizard and choose to show or hide them using width settings in properties. However, if you choose which fields to display using properties you can only choose the first field and as many consecutive fields as you want which can also be shown or hidden in the list. The net effect is the same - you can choose to display any fields you want to.

Here's the bit I think I've misunderstood. I thought that whichever record you selected in the list, the displayed field(s) were what ultimately gets stored in the combo box. From what you're saying I seem to be wrong - whichever record you select, it's bound field, which is usually its id field and primary key - is the value that gets stored in the combo box.

I hope I got that right this time....

If so, that gives me another couple of questions. once this value (the id field) is stored in the combo box, where does it go? It can't go into the table you based the form on because there isn't a field for it and it can't be stored in the query you based the combo box on.

Now, the value of what you choose (id field) is stored in the combo box but what is displayed is the value you clicked on to choose a record. But I've managed to display the full name and jobtitle in the pull down list, so why do we only see the name? I think you may have tried to explain that in your last post but it was a bit technical for me at this stage.