Basic record Structure for easy searching ?!

[al9315]

Had some great help from you guys - thank you very much.
LP CD collection - taking your advice - wish to normalise track table.
I wish all tracks to be searchable e.g. find all instances of "White Xmas"
......
Thinking of putting all tracks in one table, but cannot get head round best
way of structuring table - i.e. one disc may have 3 tracks, or 12 tracks, LPs
have tracks on two sides, CDs on one. I have the 'disc collection' table and
wish to be able to pull the relevant tracks into the relevant text boxes. At
the moment the tracks are in 24 columns - track 1 - track 2 etc. - slow to
search (Would there be any way of copying all the data from 24 columns into
one ?! AL

 

[John Vinson]

Had some great help from you guys - thank you very much.
LP CD collection - taking your advice - wish to normalise track table.
I wish all tracks to be searchable e.g. find all instances of "White Xmas"
.....
Thinking of putting all tracks in one table, but cannot get head round best
way of structuring table - i.e. one disc may have 3 tracks, or 12 tracks, LPs
have tracks on two sides, CDs on one.

I would suggest, then, a Tracks table with fields:

Tracks
*RecordingID < link to the table of disks
*TrackNo < which track
*Side < A or B; just call all CD/DVD tracks Side A
Title

The asterisk fields should be a joint three-field Primary Key: select
all three fields with ctrl-mouseclick and click the Key icon. This
will ensure that a given track can only be entered once.

This Table would be updated using a Subform; your Disk Collection
table would be on the mainform, and the Tracks table on a subform,
using the RecordingID as the master/child link field. If a given
recording has 24 tracks, there would be 24 rows for it in the Tracks
table, with values like 1 A, 2 A, 3 A, ... , 24 A; for a vinyl disk,
you would have 1 A, 2 A, ... , 8 A, 1 B, 2 B and so on.

I have the 'disc collection' table and
wish to be able to pull the relevant tracks into the relevant text boxes. At
the moment the tracks are in 24 columns - track 1 - track 2 etc. - slow to
search (Would there be any way of copying all the data from 24 columns into
one ?! AL

You can populate this properly structured table using a Query based on
[Disk Collection]. It's a bit of a complex query; you need to go to
the SQL window to create it. If you could post the fieldnames in your
collection table I'd be glad to suggest the correct syntax for the
query.

John W. Vinson[MVP]

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Don't have a column for each track - violates Normal Form.

You could have a table of song titles:

CREATE TABLE albums (
album_id COUNTER UNIQUE , -- AutoNumber
title VARCHAR(100) NOT NULL PRIMARY KEY
artist VARCHAR(255) NULL
)

CREATE TABLE song_titles (
album_id INTEGER NOT NULL REFERENCES albums ,
title VARCHAR(50) NOT NULL ,
track BYTE NOT NULL ,
side CHAR(1) NULL ,
CONSTRAINT PK_song_titles PRIMARY KEY (album_id, title)
)

Could also have a "composer" column for song's composer. Perhaps
another for lyricist. Or... another table of song_ids &
composers/lyricists.

Search for song title:

SELECT a.title As album, s.title, s.track, s.side
FROM albums a INNER JOIN song_titles s
ON a.album_id = s.album_id
WHERE s.title LIKE "*white xmas*"

====

You could just ignore track numbers, since you can find a song title;
get's its album name; pick up the album & find the track the song is on.
You'd have a "monster" table that'd hold album names & song names
(all_titles, below):

: look up table

CREATE TABLE types (
type_id COUNTER UNIQUE , -- AutoNumber
type_name VARCHAR(50) NOT NULL PRIMARY KEY
)

Data:

type_id type_name
======= =========
1 Album
2 Song

CREATE TABLE all_titles (
title_id COUNTER UNIQUE , -- AutoNumber
type_id INTEGER NOT NULL REFERENCES types ,
title VARCHAR(50) NOT NULL PRIMARY KEY ,
parent INTEGER NULL REFERENCES all_titles (title_id)
)

Data:

title_id type_id title parent
======== ======= ================ =======
59 1 The White Album NULL
88 2 Helter Skelter 59

The White Album's parent is NULL 'cuz the album is the parent. Helter
Skelter is a track on the White Album, therefore, it's parent = title_id
59.

Find "white xmas"

SELECT parent, title
FROM all_titles
WHERE type_id = 2
AND title LIKE "*white xmas*"

If you wanted to find both albums and songs w/ "white xmas" in the title
you'd just not include the type_id evaluation in the WHERE clause.

It's sorta like a bill of materials (BOM) set up. Search Google, or
newsgroup archives (Google groups) for info on BOM table set ups.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQgK2aoechKqOuFEgEQIbPgCgpVqeN1hoBlWk+oB0BIJosiY7n24AoKGD
aIVgJ6HrPbkyGkNNaEyQE/u7
=Tjyo
-----END PGP SIGNATURE-----

 

[Chris2]

-----BEGIN PGP SIGNED MESSAGE-----

Search for song title:

SELECT a.title As album, s.title, s.track, s.side
FROM albums a INNER JOIN song_titles s
ON a.album_id = s.album_id
WHERE s.title LIKE "*white xmas*"

Or, for executing directly via a Form:

WHERE s.title LIKE "*" & Forms!YourForm!SearchTextBox.Value & "*"


Sincerely,

Chris O.

 

[al9315]

Had some great help from you guys - thank you very much.
LP CD collection - taking your advice - wish to normalise track table.
I wish all tracks to be searchable e.g. find all instances of "White Xmas"
.....
Thinking of putting all tracks in one table, but cannot get head round best
way of structuring table - i.e. one disc may have 3 tracks, or 12 tracks, LPs
have tracks on two sides, CDs on one.

I would suggest, then, a Tracks table with fields:

Tracks
*RecordingID < link to the table of disks
*TrackNo < which track
*Side < A or B; just call all CD/DVD tracks Side A
Title

The asterisk fields should be a joint three-field Primary Key: select
all three fields with ctrl-mouseclick and click the Key icon. This
will ensure that a given track can only be entered once.

This Table would be updated using a Subform; your Disk Collection
table would be on the mainform, and the Tracks table on a subform,
using the RecordingID as the master/child link field. If a given
recording has 24 tracks, there would be 24 rows for it in the Tracks
table, with values like 1 A, 2 A, 3 A, ... , 24 A; for a vinyl disk,
you would have 1 A, 2 A, ... , 8 A, 1 B, 2 B and so on.

I have the 'disc collection' table and
wish to be able to pull the relevant tracks into the relevant text boxes. At
the moment the tracks are in 24 columns - track 1 - track 2 etc. - slow to
search (Would there be any way of copying all the data from 24 columns into
one ?! AL

You can populate this properly structured table using a Query based on
[Disk Collection]. It's a bit of a complex query; you need to go to
the SQL window to create it. If you could post the fieldnames in your
collection table I'd be glad to suggest the correct syntax for the
query.

John W. Vinson[MVP]

The field names in my collection table are TrackA1, Track A2 - Track A12 +
the same for B e.g. B1 -12
I have created a single (test) ' tracks table' as you suggest, which I
clearly see the logic of ! it sort of works ?! I do not like the tracks
appearing in a list with scroll bars, as they do, I would like to have
vertically on the left A1,A2 - A12 and vertically on the right B1 - B12, so
if Disc 'x' had say 3 tracks on Sdie 1 and 5 tracks on side 2 - they are
clearly seperated, preferably in text boxes or labels.
I suspect I am trying to run before.................. ? Your help is
fantastic nonetheless !
Al

 

[John Vinson]

The field names in my collection table are TrackA1, Track A2 - Track A12 +
the same for B e.g. B1 -12
I have created a single (test) ' tracks table' as you suggest, which I
clearly see the logic of ! it sort of works ?! I do not like the tracks
appearing in a list with scroll bars, as they do, I would like to have
vertically on the left A1,A2 - A12 and vertically on the right B1 - B12, so
if Disc 'x' had say 3 tracks on Sdie 1 and 5 tracks on side 2 - they are
clearly seperated, preferably in text boxes or labels.
I suspect I am trying to run before.................. ? Your help is
fantastic nonetheless !

You can do this easily enough. Put two Subforms on your form, each
based on a Query selecting either A or B tracks respectively.

If you really really REALLY want separate textboxes for every value
that you enter, rather than a continuous Subform with multiple rows,
then you will need to use advanced techniques: an unbound Form with
VBA code to open a Recordset based on your table, extract the data
from the textboxes on the form, write them to the table, etc.; and
vice versa, filling the textboxes from the table using code in the
form's current event. It's doable but it's a WHOLE LOT more work, and
in my opinion is *less* friendly and *less* flexible than a
well-designed subform. Your choice though!

John W. Vinson[MVP]

 

[al9315]

You can do this easily enough. Put two Subforms on your form, each
based on a Query selecting either A or B tracks respectively.

If you really really REALLY want separate textboxes for every value
that you enter, rather than a continuous Subform with multiple rows,
then you will need to use advanced techniques: an unbound Form with
VBA code to open a Recordset based on your table, extract the data
from the textboxes on the form, write them to the table, etc.; and
vice versa, filling the textboxes from the table using code in the
form's current event. It's doable but it's a WHOLE LOT more work, and
in my opinion is *less* friendly and *less* flexible than a
well-designed subform. Your choice though!

John W. Vinson[MVP]

Again - taken your advice - excellent !
1) Can I remove the scroll bar at the bottom of the sub-forms, they will
purely be used to view the tracks, no editing etc. - ideally I would like
both sub-forms always to show 12 lines irrespective if there are 12 tracks or
not ? The $64,000 question !! Can I - from my exisiting records copy from
the tracks fields (24 columns - I do not ever wish to have to re-type them
all !!!) into the new one keeping the fields in order e.g.
Current = Disc Name Track1 Track 2 Track 3
1) Hello Welcome Bye Bye See you again
2) Xmas Holly Lights Tree

I need to copy them to look like :-

Disc Name Tracks

1) Hello Welcome
1) Hello Bye Bye
1) Hello See You Again etc.

I hope this is clear, sorry it is a bit long !! Regards,

Ian

 

[John Vinson]

1) Can I remove the scroll bar at the bottom of the sub-forms, they will
purely be used to view the tracks, no editing etc. - ideally I would like
both sub-forms always to show 12 lines irrespective if there are 12 tracks or
not ?

Sure. Open the form you're using as a subform in design view; view its
Properties; and set the Scroll Bars property to Neither. Be sure the
default view is Continuous Forms, and size it to show twelve rows. (If
you have a CD with 14 tracks you will, of course, be concealing
data... not sure you *really* want to do this!)

Set the Form's Allow Edits and Allow Additions and Allow Deletes
properties to False if you want this form to be read-only.

The $64,000 question !! Can I - from my exisiting records copy from
the tracks fields (24 columns - I do not ever wish to have to re-type them
all !!!) into the new one keeping the fields in order e.g.
Current = Disc Name Track1 Track 2 Track 3
1) Hello Welcome Bye Bye See you again
2) Xmas Holly Lights Tree

Yes. You'll need a "Normalizing Union Query" to do this. You can't
build the query in the query grid, you need to choose View... SQL to
do so. Edit this into your Query. I'm assuming your current table is
named Discs and that if there is no Track 18 that you've left the
[Track 18] field NULL; and that you want Tracks 1 to 12 to be A, 13 to
24 to be B. You'll need to tweak the query if this isn't the case.

SELECT [Disc Name], "A" As Side, (1) AS Track, [Track 1] AS Title
FROM [Discs]
WHERE [Track 1] IS NOT NULL
UNION ALL
SELECT [Disc Name], "A" As Side, (2) AS Track, [Track 2] AS Title
FROM [Discs]
WHERE [Track 2] IS NOT NULL
UNION ALL
SELECT [Disc Name], "A" As Side, (3) AS Track, [Track 3] AS Title
FROM [Discs]
WHERE [Track 3] IS NOT NULL
UNION ALL
<etc etc for first 12 tracks>
SELECT [Disc Name], "B" As Side, (13) AS Track, [Track 13] AS Title
FROM [Discs]
WHERE [Track 13] IS NOT NULL
UNION ALL
SELECT [Disc Name], "B" As Side, (14) AS Track, [Track 14] AS Title
FROM [Discs]
WHERE [Track 14] IS NOT NULL
UNION ALL
<etc etc for the last 12 tracks>

Save this Query as uniAllTracks; look at the data by opening it to see
if it's got the correct information. If so, create a new query based
on uniAllTracks; change it to an Append query; and append it to the
Tracks table.

John W. Vinson[MVP]

 

[al9315]

Sure. Open the form you're using as a subform in design view; view its
Properties; and set the Scroll Bars property to Neither. Be sure the
default view is Continuous Forms, and size it to show twelve rows. (If
you have a CD with 14 tracks you will, of course, be concealing
data... not sure you *really* want to do this!)

Set the Form's Allow Edits and Allow Additions and Allow Deletes
properties to False if you want this form to be read-only.

Again thanks !! - I must be doing something wrong, as I did know how to
disable scroll bars etc. on form. I created the subform clicking subform in
the toolbox, sizing it once it was on the main form ( it looked like a large
text box !?) and then linked it with main form in properties box - there is
however no mention - in the subforms property boxes - of scrollbars, despite
the fact they appear when not in design mode ? The 2 text boxes do work -
and show the tracks etc.... ?
The databar shows 'Subform/Subreport: ATracks - so it looks okay to me ?!

 

[John Vinson]

there is
however no mention - in the subforms property boxes - of scrollbars

These are not properties of the Subform control; they are properties
of the Form you're using within that control. Either open that form in
design mode separately, or right mouseclick the edge of the subform
control and select "Subform in new window".

John W. Vinson[MVP]

 

[al9315]

1) Can I remove the scroll bar at the bottom of the sub-forms, they will
purely be used to view the tracks, no editing etc. - ideally I would like
both sub-forms always to show 12 lines irrespective if there are 12 tracks or
not ?

Sure. Open the form you're using as a subform in design view; view its
Properties; and set the Scroll Bars property to Neither. Be sure the
default view is Continuous Forms, and size it to show twelve rows. (If
you have a CD with 14 tracks you will, of course, be concealing
data... not sure you *really* want to do this!)

Set the Form's Allow Edits and Allow Additions and Allow Deletes
properties to False if you want this form to be read-only.

The $64,000 question !! Can I - from my exisiting records copy from
the tracks fields (24 columns - I do not ever wish to have to re-type them
all !!!) into the new one keeping the fields in order e.g.
Current = Disc Name Track1 Track 2 Track 3
1) Hello Welcome Bye Bye See you again
2) Xmas Holly Lights Tree

Yes. You'll need a "Normalizing Union Query" to do this. You can't
build the query in the query grid, you need to choose View... SQL to
do so. Edit this into your Query. I'm assuming your current table is
named Discs and that if there is no Track 18 that you've left the
[Track 18] field NULL; and that you want Tracks 1 to 12 to be A, 13 to
24 to be B. You'll need to tweak the query if this isn't the case.

SELECT [Disc Name], "A" As Side, (1) AS Track, [Track 1] AS Title
FROM [Discs]
WHERE [Track 1] IS NOT NULL
UNION ALL
SELECT [Disc Name], "A" As Side, (2) AS Track, [Track 2] AS Title
FROM [Discs]
WHERE [Track 2] IS NOT NULL
UNION ALL
SELECT [Disc Name], "A" As Side, (3) AS Track, [Track 3] AS Title
FROM [Discs]
WHERE [Track 3] IS NOT NULL
UNION ALL
<etc etc for first 12 tracks>
SELECT [Disc Name], "B" As Side, (13) AS Track, [Track 13] AS Title
FROM [Discs]
WHERE [Track 13] IS NOT NULL
UNION ALL
SELECT [Disc Name], "B" As Side, (14) AS Track, [Track 14] AS Title
FROM [Discs]
WHERE [Track 14] IS NOT NULL
UNION ALL
<etc etc for the last 12 tracks>

Save this Query as uniAllTracks; look at the data by opening it to see
if it's got the correct information. If so, create a new query based
on uniAllTracks; change it to an Append query; and append it to the
Tracks table.

John W. Vinson[MVP]

I got up at 4.30am today, spent 3 hours + 2 just now, and 'yes' - I have
managed to get rid of all the syntax and other errors, and have finally
transferred all my data to one 'normalised table' - I could NEVER have done
it without your help - Thanks you so much - I am learning quite a bit as I
go, my next challenge is to set up the search track button.
The only thing (at the moment ) that I am not happy with is :-
I can as you suggested remove the navigation buttons from the main form, but
there seems to be no way to remove them from the subforms, which spoils the
visual effect !!! The tracks 1 - x appear fine, but there is no need for
anyone to scroll through them
Enjoying this steep learning curve - what a piece of software !!!!?

Thanks, Al

 

[John Vinson]

I got up at 4.30am today, spent 3 hours + 2 just now, and 'yes' - I have
managed to get rid of all the syntax and other errors, and have finally
transferred all my data to one 'normalised table' - I could NEVER have done
it without your help - Thanks you so much - I am learning quite a bit as I
go, my next challenge is to set up the search track button.

You're welcome. I'm going to be gone for a couple of weeks (mostly) so
if you want to start up a new thread, one of the other volunteers will
be able to help.

The only thing (at the moment ) that I am not happy with is :-
I can as you suggested remove the navigation buttons from the main form, but
there seems to be no way to remove them from the subforms, which spoils the
visual effect !!! The tracks 1 - x appear fine, but there is no need for
anyone to scroll through them

Open the *FORMS* you're using as subforms - not the main form - in
design view and set their scrollbar and navigation button properties
as you wish.

Enjoying this steep learning curve - what a piece of software !!!!?

It's pretty powerful - and yes, pretty complex! Glad to have been of
help.

John W. Vinson[MVP]