SELECT DISTINCTROW can this puzzle be solved?? (1 Viewer)

[sysHvV]

I stumbled upon a problem in my Access database: as a Movie Buff I have a single dropdown to select or add an artist on the (List Video Clips) then I was in need of more info on the screen so I added another field named ArtistSub. That was the beginning of my Odyssey.
So far I used a single field for which had enough length to contain more than one name but it was impossible to properly use a filter on that field.
Using DISTINCTROW should provide the possibility to select one single row but that is not the case. I keep getting a various number of records in the new field.
I ran the performance analyzer but it only reported too many ControlSources were on the form. Up till today I was not aware of that fact.

So far I made several attempts to add the desired ArtistSub,
OA - I created a query (without table) but I assume that the results were never stored.
OB - I created a new table on Database level named Herkomst with her own ID and text fields but I could not make any selection in it.
OC - MMArtistSub was the next addition but the results were very like those from Herkomst.
OD - Finally a new field was added to table MmArtist it is named ArtistSub and it shares its ID with Qartist which was the original field to choose from. Only this could be accessed only (probably due to internal links) there were too many rows selected.

Hopefully this explication is not to fuzzy, and looking forward to your contribution. My preference would solution OB or OC. Thanks in advance.


The query as created when splitting the Database

SELECT DISTINCTROW MmArtist.ArtistID, MmArtist.Artist, MmArtist.QKind, subMedKind.Groep FROM MmArtist INNER JOIN subMedKind ON MmArtist.QKind = subMedKind.KindID WHERE (((subMedKind.Groep)="Video")) ORDER BY MmArtist.Artist;

The new query with the faulty results

SELECT DISTINCTROW MmArtist.ArtistID, MmArtist.ArtistSub, MmArtist.QKind, subMedKind.Groep FROM MmArtist INNER JOIN subMedKind ON Mm
Artist.QKind = subMedKind.KindID WHERE (((subMedKind.Groep)="Video")) ORDER BY MmArtist.ArtistSub;

Overview of the relations in this query

_________________________________________________________
Beauty resides in the eye of the beholder
Configuration
MoBO – Asus P5QLD S775
OS = Windows 7 Home Premium (Still an enigma to me)
Video Saphire Radeon HD5450
Intel Core 2 Quad Q9550 at 2.66 GHZ
RAM = 4.00 GB
Office 97
Visual Basic 2008

 

[jzwp22]

Since you may have many artists associated with a film and an artist can be in many films you have a many-to-many relationship which has to be handled with a table not with fields or trying to put multiple artists within 1 field

The structure would look like this

tblMedia
-pkMediaID primary key, autonumber
-txtMediaTitle
other fields related to the media item

tblPeople (artists)
-pkPeopleID primary key, autonumber
-txtFName
-txtLName
other fields related to the person

tblMediaPeople
-pkMediaPeopleID primary key, autonumber
-fkMediaID foreign key to tblMedia
-fkPeopleID foreign key to tblPeople

 

[sysHvV]

I will try this and then get back to you

 

[missinglinq]

...So far I used a single field for which had enough length to contain more than one name but it was impossible to properly use a filter on that field...

jzwp22 has put you on the right path here, but to just emphasize one of the points he touched on, storing multiple bits of information in a single Field violates the 'atomic theory of databases!' Basically, this means one-bit-of-data in one-Field.

Memo Fields, and I'm guessing this is what you meant when you said "a single field for which had enough length to contain more than one name," should only be used to hold memos, notes, narratives, etc. They should never be used to hold data that will ever need to be manipulated in any way, shape or form! In other words, no searching, sorting, parsing or aggregating! If you'll need to do this to a bit of data, it needs to be in its own, separate Field,

Linq ;0)>

 

[sysHvV]

Since you may have many artists associated with a film and an artist can be in many films you have a many-to-many relationship which has to be handled with a table not with fields or trying to put multiple artists within 1 field

The structure would look like this

tblMedia
-pkMediaID primary key, autonumber
-txtMediaTitle
other fields related to the media item

tblPeople (artists)
-pkPeopleID primary key, autonumber
-txtFName
-txtLName
other fields related to the person

tblMediaPeople
-pkMediaPeopleID primary key, autonumber
-fkMediaID foreign key to tblMedia
-fkPeopleID foreign key to tblPeople

Thanks for your contribution but you emphasize the creation of Tables (I use a similar method) where my problem is more focussed on the queries cause that what I have scarce knowledge of. I am still puzzled with OUTER JOINTS, INNER JOINTS,etc.
Most of last weeks time I spent on trials on QviTitel where I was puzzled with an undeletable column named EXPR2010.
I will give this project a rest now but if you have or come across some query where the two are included I will be very great full.

For now so long and goodbye

 

[missinglinq]

Thanks for your contribution but you emphasize the creation of Tables (I use a similar method) where my problem is more focussed on the queries

Your focus may well be on Queries, but in order to accomplish the task you've presented here, you're going to have a Many-to-Many Relationship, and in order to deal with this, you're going to have to use a Junction Table, which is what the Table named tblMediaPeople, posted by jzwp22, is. You cannot handle a Many-to-Many Relationship simply using Queries.

...Most of last weeks time I spent on trials on QviTitel where I was puzzled with an undeletable column named EXPR2010.

The Field name (it's not a Column, in Access, it's a Field) of EXPR2010 suggests that it is a Calculated Field, i.e. an Expression. In Calculated Fields, in Access, Data cannot be Entered, Edited or, as you've found out, Deleted.

Linq ;0)>

 

[sysHvV]

Thanks for your contribution which gave me a lot to consider because it as an existing Database (well backed up) and it contains over 20.000 records so caution is required.
You convinced me with your exposé on a many-to-many relationship and otherwise my many trials on QviTitel confirmed that. Since there are many ways to skin a cat I kept on trying loosing more and more expectations.
In a while I will make the changes and all did have the desired characteristics:

tblmedia >>>>>>>> MmTitle
-pkmediaid primary key, autonumber
-txtmediatitle
other fields related to the media item

tblpeople (artists) >>>>>>>> MmArtist
-pkpeopleid primary key, autonumber
-txtfname
-txtlname
other fields related to the person

tblMediaPeople
-pkMediaPeopleID primary key, autonumber
-fkMediaID foreign key to tblMedia
-fkPeopleID foreign key to tblPeople

For now Thanks so long and goodbye

 

[smig]

if what you have now is a single to many relationship you can use it to create a query that will fill the new Many to Many table

 

[sysHvV]

After having carried out the instructions by jzwp22 and with that I created a new table. (tblMediaPeople)
Apparently no joy came from that because all actions with this table failed:
First I added the table or its query to (QviTitel) but after that everything blocked

I would very much like to begin but I still too confused, my choice was to use (tblMediaPeople) not having the `
slightest idea what (foreign key) means and it is a text field now

MmProtag (Table)
ProtagID
ArtistBis
ArtisNOG
This is the latest table (primary key, autonumber) and will be used to create a many-to-many relationship but since this exceeds my knowledge about Access any assistance will be highly appreciated.
 Does this table need any modification??
 How do I connect ArtistBis to the second column of (List Video Clips)

Slowly a feeling broadens that this cat can not be skinned (by me??). Please Help

 

[smig]

This many to many table don't need any key, though it can have it will probably never be used.
This table hold the keys from both single to many tables.

Table1:
Table1_key
Table1_data

Table2:
Table2_key
Table2_data

Table1_Table2 (many to many):
Table1_key
Table2_key

 

[jzwp22]

Apparently no joy came from that because all actions with this table failed:

What failed? Do you mean a query failed? If so, how?

Did it fail to return any records?
Were you not able to run it due to some error?

You have mentioned QviTitel several times, what is it?

Could you post the full structure (table names, field names with datatypes and relationships) you currently are using? If you cannot post the database could you at least post it similar to what I did?

tblmedia >>>>>>>> MmTitle
-pkmediaid primary key, autonumber
-txtmediatitle
other fields related to the media item

tblpeople (artists) >>>>>>>> MmArtist
-pkpeopleid primary key, autonumber
-txtfname
-txtlname
other fields related to the person

tblMediaPeople
-pkMediaPeopleID primary key, autonumber
-fkMediaID foreign key to tblMedia or MmTitle
-fkPeopleID foreign key to tblPeople or MmArtist


By the way, a foreign key is just a field that holds a value that is the same as a value in a related table.

For example, let's say that you have the artist Robert Plant in your MmArtist table as record #1; let's also include Jimmy Page as record 2. Using the field names above the data in the table would look like this:

pkpeopleid|txtfname|txtlname
1|Robert|Plant
2|Jimmy|Page

Now in your media table you might have Led Zeppelin's debut album entitled "Led Zeppelin" as record #3

pkmediaid|txtmediatitle
3|Led Zeppelin

Now since Robert Plant and Jimmy Page were both involved in the Led Zeppelin album we would have 2 records in tblMediaPeople tied to the album

pkMediaPeopleID|fkMediaID|fkPeopleID
1|3|1 (this record denotes the album 3 (Led Zeppelin) and the person 1 (Robert Plant))
2|3|2 (this record denotes the album 3 (Led Zeppelin) and the person 2 (Jimmy Page))

 

[sysHvV]

QviTitel
Is a selection query (Q)uery(vi)deo(Titel) and a screenshot from it is in the original post.
Yes the query failed, ass soon as I added it to QviTitel there was no movement possible.

Meanwhile I created MmProtag (working fine) but I still have trouble with the implementation. Some how this table has to be connected to QviTitel which has no one yet addressed.
How do I connect ArtistBis to the second column of (List Video Clips)
How can I see what would the position and characteristics (many-to-many relationship) etc be in the result.

I dabbled a little but chance did not deliver the solution yet (does it exist at all) please advice!

 

[jzwp22]

How do I connect ArtistBis to the second column of (List Video Clips)

With the change in table structure, the List Video Clips form cannot be used in its present state. To represent the junction table MmProtag it will have to be represented via a subform within your main form since many artists need to be shown.


I have attached an example database that illustrates the main form/subform design. (See form: frmMedia)