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
The new query with the faulty results
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
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
Code:
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
Code:
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