Queries not retrieving the required data

[Guest]

My boss has asked me to create a query to find for example all songs by an
artist in a CD Collection database. The query can only retrieve some songs
for some artists and no songs at all for other artists. I've checked the
query which seems ok. I think the problem could be because it is a simple
database (just one table with about 25 headings and about 100,000 records. I
tried to use "Analyse --- Table" under Tools but have not been successful. To
avoid corrupting the database, I decided to get advice from you wonderful
people out there before doing anythin else. Can I change this table into a
relational table for better analysis? There are many duplications, ie songs,
artists, numbers, year, etc and I think perhaps this is causing the problem.
Is there a way in which I can retrieve the info required? Deadlines are
approaching fast so I'll be most grateful for urgent advice.
Thanks

 

[Jeff Boyce]

As you seem to understand, it all starts with the data...

Can you provide a field-by-field description of your "one table"?

Have you looked at any of the templates Microsoft includes for collections
(I think there's even a CD Collection)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

My boss has asked me to create a query to find for example all songs by an
artist in a CD Collection database. The query can only retrieve some songs
for some artists and no songs at all for other artists. I've checked the
query which seems ok. I think the problem could be because it is a simple
database (just one table with about 25 headings and about 100,000 records. I
tried to use "Analyse --- Table" under Tools but have not been successful. To
avoid corrupting the database, I decided to get advice from you wonderful
people out there before doing anythin else. Can I change this table into a
relational table for better analysis? There are many duplications, ie songs,
artists, numbers, year, etc and I think perhaps this is causing the problem.
Is there a way in which I can retrieve the info required? Deadlines are
approaching fast so I'll be most grateful for urgent advice.
Thanks

You can certainly migrate this data into a *SET* of related normalized tables,
though it may end up being a lot of work. The downside will be "near
duplicates" - for instance, if you have records with "Edward Kennedy
Ellington" and "Edward K. Ellington" and "Duke Ellington", you and I know that
they are all by The Duke, but to Access those are three different artists. And
if you have another record or two by "Duke Ellinton" you've got that as a
problem too!

The basic approach will be to create a properly normalized set of tables:
Recordings; Tracks; Artists; TrackArtists (resolving the many to many
relationship between tracks and artists); probably more. You'll run Append
queries from your "spreadsheet" into these tables using SELECT DISTINCT to
select just one instance of each (say) artist.

Then you'll need to check, check, check, proofread, check some more.


John W. Vinson [MVP]

 

[Guest]

Thanks Jeff for your advice. Are you requesting me to write here all the
column headings? The database was designed by somebody else and I worked on
data input and the query so any help as to possibly starting a properly
designed relational database and copy/pasting the entries into it will be
most helpful.
Thanks

 

[Guest]

Hi John
Thank you for answering my SOS. It seems from your advice that I may be able
to sort the problem out but would require a lot of work. Considering the
amount of data involve (100,000 entries) do you think I could copy and paste
into a relational table?
As I read your response, I've an understanding of what you say but I do no
know how to put it in practice as I'm not an expert but willing to learn.
Could you kindly break down your advice from SET ..... so that I can have a
better understanding of the requirement or possibly it step by step procedure.
Thank you once again for your help.

 

[John W. Vinson]

Hi John
Thank you for answering my SOS. It seems from your advice that I may be able
to sort the problem out but would require a lot of work. Considering the
amount of data involve (100,000 entries) do you think I could copy and paste
into a relational table?

No. Copy and paste is for spreadsheets; it'll just give you sore fingers and
headaches in Access!

Queries are your tool of choice here. To migrate data from one table into
another table, you would execute an Append query, selecting the fields and the
records which you wish to move.

As I read your response, I've an understanding of what you say but I do no
know how to put it in practice as I'm not an expert but willing to learn.
Could you kindly break down your advice from SET ..... so that I can have a
better understanding of the requirement or possibly it step by step procedure.
Thank you once again for your help.

I couldn't possibly suggest a detailed procedure without knowing the current
structure and fieldnames in your table, and (perhaps as the result of an
ongoing discussion) the structure and fieldnames of the properly normalized
tables.

John W. Vinson [MVP]

 

[Guest]

Thanks once again John. It's very much appreciated. I'll discuss with my boss
and come back to you for further advice.

 

[Guest]

Hello John,
First of all, I must say how much I appreciate your help in this matter. It
has just given me faith in the human race again. Please find below
fieldnames=data type.

{ID=text, Access Nos=auto-number, Catalogue No=text, Song Title/Track
Name=text, Performer/Artist=text, Band/Group=text, Label=text, Master
Held On-text, Composer=text, Record Company=text, Genre=text,
Sub-Genre=text, Sub-Genre2=text, Related T=text, Writer/Publisher=text,
Length/Track Time=text, Exclusions=text, Territories For Digital Rights
World=yes/no, Location=text, Owned With=text, Matrix/Record Company
Number=text, Year=number, Cleaned=yes/no, Vocalist=text, Band Member
1=text, Band Member 2=text, Band Member 3=text, Band Member 4=text, Band
Member 5=text, Band Member 6=text, Band Member 7=text, Band Member 8=text,
Format=text, Note=memo, Note=memo2, Number of Tracks=number, Tracks
Number=number, Copyright Line=text, Scheduled Release Date=date/time,
Original Year Of Release=number, ISRC=text, P Line=text, Artwork
Available=yes/no, Full Artist Description=memo, Full Album
Description=memo}.

There is not much selection in the field property details. Mmost of them are
set to default, eg. no validation rule; required=no; zero length allowed; not
indexed, etc and no Primary Key. Also, the entries are, eg a Peggie Lee CD
with 20 songs will have her name entered 20 times in the Artist field; and a
CD reference eg CD0001 in Master field 20x along each song. At present, most
of the data are in about the first 10 fields but it is hoped that the other
fields would be completed. For instance, John Coltrane, a giant in the jazz
world played with everyone and everyone played with him. So he might turn up
under Performer on a CD and Band Member 6 on another.

I hope that the above is clear and that you will be able to assist.
AggieZ

 

[John W. Vinson]

Hello John,
First of all, I must say how much I appreciate your help in this matter. It
has just given me faith in the human race again. Please find below
fieldnames=data type.

{ID=text, Access Nos=auto-number, Catalogue No=text, Song Title/Track
Name=text, Performer/Artist=text, Band/Group=text, Label=text, Master
Held On-text, Composer=text, Record Company=text, Genre=text,
Sub-Genre=text, Sub-Genre2=text, Related T=text, Writer/Publisher=text,
Length/Track Time=text, Exclusions=text, Territories For Digital Rights
World=yes/no, Location=text, Owned With=text, Matrix/Record Company
Number=text, Year=number, Cleaned=yes/no, Vocalist=text, Band Member
1=text, Band Member 2=text, Band Member 3=text, Band Member 4=text, Band
Member 5=text, Band Member 6=text, Band Member 7=text, Band Member 8=text,
Format=text, Note=memo, Note=memo2, Number of Tracks=number, Tracks
Number=number, Copyright Line=text, Scheduled Release Date=date/time,
Original Year Of Release=number, ISRC=text, P Line=text, Artwork
Available=yes/no, Full Artist Description=memo, Full Album
Description=memo}.

There is not much selection in the field property details. Mmost of them are
set to default, eg. no validation rule; required=no; zero length allowed; not
indexed, etc and no Primary Key. Also, the entries are, eg a Peggie Lee CD
with 20 songs will have her name entered 20 times in the Artist field; and a
CD reference eg CD0001 in Master field 20x along each song. At present, most
of the data are in about the first 10 fields but it is hoped that the other
fields would be completed. For instance, John Coltrane, a giant in the jazz
world played with everyone and everyone played with him. So he might turn up
under Performer on a CD and Band Member 6 on another.

I hope that the above is clear and that you will be able to assist.
AggieZ

Ok... you've got a job of work on your hands.

I'd see the following Entities (real-life things, events, people) in your
application; each kind of entity should have its own Table:

Recordings
AccessNo <autonumber primary key>
RecordingID <your ID, I'm guessing - how is it assigned?>
CatalogueNo <may need to allow duplicates if different labels use the same
number>
LabelID <link to Labels>
Title <of the recording, e.g. "Blue Train">
RecordCompany <text, or link to a RecordCompanies table>
<other information about the CD as a whole, nothing about tracks>

Tracks
AccessNo <long integer, link to Recordings, part of Primary Key>
TrackNo <integer, other half of Primary Key>
TrackTitle <"I'm Old Fashioned">

Artists
ArtistID <Autonumber Primary Key>
LastName
FirstName
<other biographical data>

TrackArtists
AccessNo <link to Recordings and Tracks>
TrackNo <link to Tracks>
ArtistID <link to Artists>
Role <e.g. "Band member", "soloist", ...>

There'll be more tables (I haven't dealt with Bands for example).

I'd really suggest googling for CD Collection databases - I'm pretty sure
Microsoft has one, and there have been several made available. You'll need to
design and run quite a few Append queries to migrate the existing data from
your wide-flat table into the normalized tables.


John W. Vinson [MVP]

 

[Guest]

John, Thank you very much for your assistance. It has given me a starting
point on this project. Very, very much appreciated and good luck in all you
do.