Selecting Items from a Combo Box

[adrian007uk]

Sorry but i need help again.

I would like to make a combo box where on selecting an option from the box i
can see all records assocaited with that option. For example, if my list
contained a list of artists i want to select an artist and see all the
records associated with that artist.

At the moment i have made the combo box but it is showing duplicate artists
and as a consequence only one record at a time. Also is there anyway once i
can see all the records assocaited with an artist to select the record and
see it in more detail (e.g., in the add record view).

Adrian

 

[John W. Vinson]

Sorry but i need help again.

I would like to make a combo box where on selecting an option from the box i
can see all records assocaited with that option. For example, if my list
contained a list of artists i want to select an artist and see all the
records associated with that artist.

At the moment i have made the combo box but it is showing duplicate artists
and as a consequence only one record at a time. Also is there anyway once i
can see all the records assocaited with an artist to select the record and
see it in more detail (e.g., in the add record view).

Adrian

You can use a subform based on the table containing the details that you want
to see, and use the name of the combo box as the subform's Master Link Field
(and the ArtistID as the child link field). You can't display this kind of
information *in a combo box* in any practical way.

 

[adrian007uk]

Thanks John

I think what you are telling me to do is what i want to do. I have managed
to make a form with the sub form ready to display the information i want. I
can't figure out how to remove the duplicates from the combo box (e,g., so i
can see one artists many records as opposed to picking one artist many times
from the combo box) or set the "combo box as the subform's Master Link Field"?

Adrian

 

[Jeff Boyce]

How are you "filling" the combobox now? That is, what is the SQL statement
that's delivering "duplicates"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I think what you are telling me to do is what i want to do. I have managed
to make a form with the sub form ready to display the information i want. I
can't figure out how to remove the duplicates from the combo box (e,g., so i
can see one artists many records as opposed to picking one artist many times
from the combo box) or set the "combo box as the subform's Master Link Field"?

Base the Combo Box on a Query selecting each artist only once - typically this
would be based on the Artists table, if you have one; or you could set the
"Unique Values" property of the query to select each artist only once.

 

[adrian007uk]

Hi Jeff

I cannot find the SQL statement but there is an embedded macro in the 'after
update' option of the combo box with a 'where' statement that is:

, , First, ="[ID] = " & Str(Nz(Screen.ActiveControl,0))
:

Adrian

 

[adrian007uk]

Hi John

I can make the query but how would i make sure that an artist is only
included once frtom the artist field?

Adrian

 

[John W. Vinson]

Hi John

I can make the query but how would i make sure that an artist is only
included once frtom the artist field?

Please tell me how artists are stored in your database. I don't know because I
can't see it.

 

[adrian007uk]

Hi John

Artists are stored in table (Recordings_Table) which is:

Recording ID (PK)
Title
Artist
Music Category
Label
Year
Format
Number of tracks

This table will be linked to another table (Tracks_Table)
Track ID
Track Number
Track Title
Recording ID (PK)

As an artit can record more than one record/CD artists appears in my combo
box more than once.

Adrian

 

[John W. Vinson]

Hi John

Artists are stored in table (Recordings_Table) which is:

Recording ID (PK)
Title
Artist
Music Category
Label
Year
Format
Number of tracks

This table will be linked to another table (Tracks_Table)
Track ID
Track Number
Track Title
Recording ID (PK)

As an artit can record more than one record/CD artists appears in my combo
box more than once.

You'll really do better to have an Artists table. As you have it, you are
wasting space (storing the same artist name multiple times) and risking data
errors (you could have the same artist in the table three times with three
different spellings of the name. Consider having a table Artists with fields
ArtistID, LastName, FirstName, and maybe other biographical info, and using a
numeric ArtistID in your Tracks table. Surely a given CD may have tracks with
different artists, right?

As it is, with your current design, you could base your combo box on a Unique
Values query (setting the Unique Values property of the query to Yes). The SQL
equivalent would be

SELECT DISTINCT Artist FROM Recordings_Table ORDER BY Artist;

The DISTINCT will ensure that each name (or, to be more precise, each spelling
of the name) will be a separate row in the combo box.

 

[adrian007uk]

Hi John

Thanks for the help. I didn't think about the full nomilisation of the data
at this point and how it can effect forms and reports so i appreciate the
pointer. I will try and implement your suggestions and see how i get on.

Is the link from the Artists_Table to the Tracks_Table 1 - many?

What would the link be from the Artists_Table to the Records_Table 1 - many?

You don't happen to see any other problems with the current structure of the
tables do you?

Many thanks

Adrian

 

[John W. Vinson]

Hi John

Thanks for the help. I didn't think about the full nomilisation of the data
at this point and how it can effect forms and reports so i appreciate the
pointer. I will try and implement your suggestions and see how i get on.

Is the link from the Artists_Table to the Tracks_Table 1 - many?

That depends! Which of these statements is true:

1. Every Track has one and only one Artist.
Every Artist performs on one and only one track.

2. Every Track has one and only one Artist.
Every Artist can be on zero, one or many Tracks.

3. Every Track may have one or more Artists.
Every Artist can be on zero, one or more Tracks.

My guess is that (3) - a Many to Many relationship - best reflects reality;
there are lots of duets, trios and larger groups in many genres. If so, you
need *two* more tables, not one, to represent the many to many relationship:

Tracks
TrackID <primary key>
<your other fields, nothing about artists>

Artists
ArtistID <primary key>
LastName
FirstName
Birthdate
<other biographical details as appropriate>

Performances
TrackID <link to Tracks>
ArtistID <link to Artists>
<any info about this performer's role in this track, e.g. lead singer or
bass guitarist or....>

If you can get by with (2) in your collection, then it's a one (artist) to
many (tracks) relationship.

What would the link be from the Artists_Table to the Records_Table 1 - many?

No link at all. A Record doesn't itself have artists; each track on the record
does, and you can link in that way. You don't need to have an additional link
in the Records table.

You don't happen to see any other problems with the current structure of the
tables do you?

It'll be a bit limiting eventually, depending on how much information you want
to capture.

 

[adrian007uk]

Hi John

I have written out my table structures on paper but i need some
clarrification with:

Performances
TrackID <link to Tracks>
ArtistID <link to Artists>

When this links the fields to the respective tables (Tracks_Table and
Artists_Table) what is the realtionship then? I presume it is either 1:1 or
1 - many as this was the point of making the Performances_Table.

and

No link at all. A Record doesn't itself have artists; each track on the record
does, and you can link in that way. You don't need to have an additional link
in the Records table.

Does that mean that the Records_Table does not need Artist ID within it even
thohgh it is not linked?

Adrian

 

[John W. Vinson]

Hi John

I have written out my table structures on paper but i need some
clarrification with:



When this links the fields to the respective tables (Tracks_Table and
Artists_Table) what is the realtionship then? I presume it is either 1:1 or
1 - many as this was the point of making the Performances_Table.

Track will have as many Performances records as there are artists on the
track: 1 to many.

Each Artist will have as many Performances recrords as there are tracks upon
which he or she performs: again, 1 to many.

and


Does that mean that the Records_Table does not need Artist ID within it even
thohgh it is not linked?

The Records table should NOT in fact have an artist ID. If it did, then you
could have only one value in that field, even on a compilation album with a
dozen artists! The ArtistID should appear only in the Artists table, as the
primary key, and in the Performances table, as a many-side foreign key.

 

[adrian007uk]

Hi John

Are the Track ID and Artist ID in the Peformances_Table both primary keys?

I am having trouble once the links are made making a form that i can enter
all the data into. It wont let me make a form from the Artists_Table with
other tables.

Adrian

 

[John W. Vinson]

Hi John

Are the Track ID and Artist ID in the Peformances_Table both primary keys?

They are a joint, two-field primary key; or else you would have a separate
autonumber PerformanceID primary key and put a unique two-field index on
TrackID and ArtistID.

I am having trouble once the links are made making a form that i can enter
all the data into. It wont let me make a form from the Artists_Table with
other tables.

Don't. Have a separate Artists form and open it in the NotInList event of the
artist combo box on the performance subform.

 

[adrian007uk]

Hi John

I have read up 'NotInList Event' so i think i have some coide to help me. I
am struggling with understanding:

"Don't. Have a separate Artists form and open it in the NotInList event of the
artist combo box on the performance subform"

I have tried to make a form to enter the data into but there seems to be a
problem with some of my links for some reason but i have checked them and
they seem fine.

Adrian

 

[John W. Vinson]

I have tried to make a form to enter the data into but there seems to be a
problem with some of my links for some reason but i have checked them and
they seem fine.

Please post your links and the problems you're having. We can't fix what we
can't see!

 

[adrian007uk]

I have

Tracks_Table
Track ID (PK) auto number (1)
Track Number
Track Title
Recording ID (Number) (Many)

Artists_Table
Artist ID (PK) Auto Number (1)
Artist Name

Performances_Table
Track ID (PK) Auto Nuber (Many)
Artist ID (PK) Number (Many)

Recordings_Table
Rcording ID (PK) Auto Number (1)
Title
Music Category ID Number (many)
Label
Year
Number of Tracks
Notes

Music_Category_Table
Music Category ID (auto number) PK (1)
Music Category

I think the problem is Recordings_Table as i can't get any of the fields to
go into a form when adding the fields from the other tables in Form Design
(if that makes sense).

Adrian

 

[John W. Vinson]

I have

Tracks_Table
Track ID (PK) auto number (1)
Track Number
Track Title
Recording ID (Number) (Many)

Artists_Table
Artist ID (PK) Auto Number (1)
Artist Name

Performances_Table
Track ID (PK) Auto Nuber (Many)
Artist ID (PK) Number (Many)

Recordings_Table
Rcording ID (PK) Auto Number (1)
Title
Music Category ID Number (many)
Label
Year
Number of Tracks
Notes

Music_Category_Table
Music Category ID (auto number) PK (1)
Music Category

I think the problem is Recordings_Table as i can't get any of the fields to
go into a form when adding the fields from the other tables in Form Design
(if that makes sense).

Nope. You don't just throw all the fields in all your tables onto a form!

A form can be based on one table, or on a Query. Multitable queries are often
not updateable, and you would certainly NOT create One Great Master Query of
all your tables. I'd see this being set up with a Form based on Recordings, a
Subform based on Tracks_Table, a sub-subform based on Performances_Table with
a combo box based on Artists_Table. There'd be a combo box on the mainform
based on Music Category ID.

One nitpick question: can you in fact assume that a Recording is all of one
genre? Or is the category better considered an attribute of a track?