If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Queries not retrieving the required data
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 -- AggieZ |
#2
|
|||
|
|||
Queries not retrieving the required data
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 "Zeenabu66" wrote in message ... 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 -- AggieZ |
#3
|
|||
|
|||
Queries not retrieving the required data
On Thu, 11 Oct 2007 16:05:01 -0700, Zeenabu66
wrote: 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] |
#4
|
|||
|
|||
Queries not retrieving the required data
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 -- AggieZ "Jeff Boyce" wrote: 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 "Zeenabu66" wrote in message ... 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 -- AggieZ |
#5
|
|||
|
|||
Queries not retrieving the required data
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. -- AggieZ "John W. Vinson" wrote: On Thu, 11 Oct 2007 16:05:01 -0700, Zeenabu66 wrote: 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] |
#6
|
|||
|
|||
Queries not retrieving the required data
On Fri, 12 Oct 2007 18:44:00 -0700, Zeenabu66
wrote: 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] |
#7
|
|||
|
|||
Queries not retrieving the required data
Thanks once again John. It's very much appreciated. I'll discuss with my boss
and come back to you for further advice. -- AggieZ "John W. Vinson" wrote: On Fri, 12 Oct 2007 18:44:00 -0700, Zeenabu66 wrote: 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] |
#8
|
|||
|
|||
Queries not retrieving the required data
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 -- AggieZ "John W. Vinson" wrote: On Fri, 12 Oct 2007 18:44:00 -0700, Zeenabu66 wrote: 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] |
#9
|
|||
|
|||
Queries not retrieving the required data
On Mon, 15 Oct 2007 18:46:01 -0700, Zeenabu66
wrote: 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] |
#10
|
|||
|
|||
Queries not retrieving the required data
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. -- AggieZ "John W. Vinson" wrote: On Mon, 15 Oct 2007 18:46:01 -0700, Zeenabu66 wrote: 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] |
Thread Tools | |
Display Modes | |
|
|