• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Crisis Of Design (1 Viewer)

MickJav

It wasn't me
Local time
Today, 03:20
Joined
Nov 28, 2005
Messages
2,199
I have added a ERD picture for this one.
Please note tbltracks in red.
My issue is the Track Title as I'm expecting the db to hold a large amount of data as the freeware project is being designed for use by DJ's I am trying to think of the best way to handle the track title.
there will be a import system which complicates things as I need it the not strangle the system.
I was thinking of adding an additional lookup table to hold the track title but it will create a lot of U.I problems not so much with the import system thats just a extra time thing, but the problem will come with manully adding records and tracks as there could be more tracks than a combo will display on lookup think thats something like 50k records.
Hope you can understand what I'm trying to explain, how would you handle the track title.



 

CJ_London

Super Moderator
Staff member
Local time
Today, 03:20
Joined
Feb 19, 2013
Messages
12,528
think thats something like 50k record
it's 65k records..
How would I handle the track title? in the context of listing in a combo or listbox I would pre filter in some way - might be artist or year or album etc or simply 'starts with' a specific character
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Yesterday, 21:20
Joined
Feb 28, 2001
Messages
17,931
As far as track-titles are concerned, consider "cascading combo box" techniques. Dynamically build the WHERE clause for your track titles where your lookup would filter by artist, album name, record label, or any one of a dozen other possibilities in the music industry. Of course, it would start out as a huge multi-table join, but it wouldn't take long to narrow down your choices. I had a case when dealing with a hosting site database where the dynamic filters included make of computer, project name, operating system, and responsible team so that I could identify vulnerabilities. I used the cascading combo box method to change a table of about 500K records down to perhaps 20 records. Won't say it was pretty. But it was doable.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:20
Joined
Feb 19, 2002
Messages
29,690
You could also use a filtering technique that filters the RowSource after each character is typed. You can set it to start the filter after 3-4 characters to limit the rows returned if you need to. Or use something like this Address book example.
 

Attachments

  • AddressBook_20200813b.zip
    115.1 KB · Views: 10

Gasman

Enthusiastic Amateur
Local time
Today, 03:20
Joined
Sep 21, 2011
Messages
6,764
You could look at MajP's excellent classes?
 

Attachments

  • MajP FAYT V12.zip
    195.7 KB · Views: 8

isladogs

CID VIP
Local time
Today, 03:20
Joined
Jan 14, 2017
Messages
13,920
I also use cascading combos for such purposes. Even if you are far below the limit of 65,536, combos with hundreds of records are fairly unusable.

In one extreme case I use 5 of them to filter UK postcodes down from around 2.6 million to a single postcode by selecting area then district then sector and zone to reach the final list of postcodes for the selected zone.
If you want to see a cut down version of that example, look at http://www.mendipdatasystems.co.uk/cascading-combo-boxes/4594455723
 

MickJav

It wasn't me
Local time
Today, 03:20
Joined
Nov 28, 2005
Messages
2,199
You could also use a filtering technique that filters the RowSource after each character is typed. You can set it to start the filter after 3-4 characters to limit the rows returned if you need to. Or use something like this Address book example.
Thanks Pat but thats like my filter form example It wont work for what I need as I need to be able to add a recording track and select a track title from a lookup table.
 

MickJav

It wasn't me
Local time
Today, 03:20
Joined
Nov 28, 2005
Messages
2,199
This is something I Built 15 years ish ago The new project wont use this but it will show the entry system this uses a text field for the track title I am thinking of changing that to a lookup and/or search engine

 

MickJav

It wasn't me
Local time
Today, 03:20
Joined
Nov 28, 2005
Messages
2,199
it's 65k records..
How would I handle the track title? in the context of listing in a combo or listbox I would pre filter in some way - might be artist or year or album etc or simply 'starts with' a specific character
I just did somethng like that with my northwind Product And customers codes I.E the the code is made up of the first 3 letters of the first 2 words I think I'll add that to the track title lookup table So the track wind beneath my wings would have a code like WINBEN1234
 

MickJav

It wasn't me
Local time
Today, 03:20
Joined
Nov 28, 2005
Messages
2,199
this is my solution, I've only stated adding track but it seems to be working nicelly Hopefully the same can be said when I add the 30k records
The tables have a red border


The manual Entry Screen


I still have the Track Genre To Add to above screen the screen is an unbound form.
 

Users who are viewing this thread

Top Bottom