07-04-10, 19:55 #1Registered User
- Join Date
- Jul 2010
Unanswered: How to make a multiple select query and according form?
Ok, I am not very skilled in Access. I am too accustomed to Excel but I don't think it can do what I have in mind.
Let's say there is a list with music tracks, each with its name and categories it belongs to. Each one belongs to several categories (eg rock and vocal). I want to make a form that I can add a new track and tick each category it belongs (I don't need to add new categories through the form, that can be done in the table I guess). Also, I want a form that I can tick the categories and through a query the appropriate results to be shown.
I think that the whole thing could be settled easily if I could handle tag based data...But people somehow did this before tag based searches.
Oh, I am using Access 2002
07-05-10, 00:59 #2Registered User
- Join Date
- May 2010
If I understand what you want, it should be possible with Access.
The key to success here will be in the proper design of your tables.
Note: A relational database is very different than a spreadsheet/workbook. A properly design relational database normally looks nothing like a spreadsheet/workbook.
To help you design your form, it would really help to know your table structures. Would you please post them.
07-05-10, 08:04 #3Registered User
- Join Date
- Jul 2010
By tag based I mean the possibility to add tags. For example, instead of creating the music categories prior to entering the data and then inserting true/false values (by ticking), I think that a tag based solution would be entering the data without creating categories and then adding values to them like rock etc. If an entry does not possess a certain tag value then it is considered to have a false value.
I know Access and Excel are totally different but the latter was (until now) far easier for me to use for simple lists. But this "project" is something I don't think Excel can handle (or me with my limited knowledge).
Hmm, as far as table structure goes, I was thinking something like this:
Let's say there is a number of music tracks that are offered for download by a site for sale (no I don't have such a site nor I will ever be).
Column 1: Some kind of ID number. I think it simplifies things in Access a lot, although it is not a part of the data in the database
Column 2: Name of track
Column 3: A true/false value to indicate whether the track has been downloaded by anyone. This way, it will be easy to monitor if a track has never been downloaded, so as to be removed or be put in an offer.
Column 4-50: True/false values for different kinds of music. The values are not exclusive, for example being rock does not exclude being vocal or instrumental only etc. The categories could also include the name of the artist for example. And it does not necessary have to be 50 of them, it was an example.
Column 51: A picture of the cover containing the track.
Column 52-... : A true-false value for specific customers (each one assigned a column). This would help find what tracks someone has downloaded as well as NOT downloaded.
Ideally, customer info could be stored in other tables and make a form for displaying combined information (customer info from the customer info tables and tracks bought from the track table). Better still, another form could present statistics (track most bought, customer who bought more tracks etc.)
I suppose you get the idea of what data and process of them I have in mind. I have a feeling that my choice of structure is not ideal but I am stuck.
07-05-10, 09:30 #4Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
having that many true false columns isn't neccesarilty a smart ide...
why becuase what happens if some comes up with a new item they want to associate recordings by.. it requires a table and application redesign
you use a system generated ID when
...the value of that number has no meaning outside the system
...when theres no better natural key ijn the data already. by natural key I mean something that already exists such as a ISBN book number, of international record numbering scheme
there is a natural key but its so long or complex (say made up of several other elements of the data that its a pig to maintain or use.
normalisation of the data (something that practitioners in the relational DB field will always push for suggests that discrete data should be stored in discrete tables.) if you haven't come across normalisation yet, then do a search and try to get to grips with it before you design anything further (or at least commit that design to code)
normalisation tries to identify repeating groups and remove them form that table into another table.
I think your genre should be a separate table, identified by a code in that table which is used in the recordings table.. a reason for doing that is it stops typos, spelling mistakes, it also makes it easier to move or modify the data (you only change the genre once
update mytable set mygenre = "crap" where genre = "RnB"
I';d suggest the picture should not be stored in the same table as the recording details, I'd suggest yoiu store a URL to where the image can be found. depending on what image(s) you have you may want to have a table for imageURLs and another table which associates a specific image with a specific recording
I can see that your design is prompted by the design ideas of spreadsheets, however it isn't the way to design data entry in a db, unless you are analysing composite information.
to give a flavour of what a data model could look like see the attached image
note this isn't suggested as a solution, to be honest I can't remember how far the model was developed before it was abandoned. however it should (I hope), along with your reading of normalisation provide a basis for developing your own model. rahter than ask questions up fornt I'd suggest you have a look at the model, say print it off and annotate what you think each entity/table is there fore, what is supposed to store, why its done that way. think of each table as something that stores everything unique about.... (eg Artists stores everythign unique about the artist (eg their names), howver an artisit may have several roles when recordings are made.. they could be the/a performer, they could be the composer, the arranger, the producer or more than one of those roles
a performer may have many roles on a recording (they could play an instrument, several instruments, they could be the lead or backing singer as well)
having understood or at least made an effort to understand the model then by all means come back if you have any queries. note this isn't a solution, it isn't even offered as a complete or working model just a perception of how a model could be developed. ultimately you are developing a model for your requirement and yours alone so the model must reflect your perception of whats required, not anyone elsesI'd rather be riding on the Tiger 800 or the Norton