Results 1 to 12 of 12
  1. #1
    ukdangerous is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    6

    New to Access 2016 - Help needed

    Hi All

    as the title denotes, I have created a database of films I have, however, it would appear that I shouldn't have set the names of the actors up as e.g. Sylvester Stallone but rather Sylvester in 1 column and then Stallone in a 2nd column, which by it's very definition means that a partial name (e.g. Stallone) would not be found, unless of course I am mistaken (is there an easy way to split them, or is it just a case of retyping the detail accordingly)?

    I am having issues with also searching the entire database for the specific detail as identified above (I can find youtube videos where they search on certain fields only (e.g. christian name or surname), would I have to define all the relevant column names to search against rather than the entire database which contains about 25 columns)?

    Finally (and I'm sure not least lol) is there an easy way to link / search tables, so that I could search both my film database and a tv series database for the same keyword or should I run seperate queries on them?

    Any help would be most appreciated (ideally a link to a video such as youtube) that I can follow in my own time would be most benficial or would a book be more beneficial (any recommendations would also be most welcome).



    I am using Access 2016 if that makes any difference.

    TIA

    ukd

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Names are normally split into LName/FName, else you start getting into LIKE operators in queries. Also, if there are two actors with the last name Jones, you may only end up with only one of the records if you do such a LIKE comparision. If you don't have LOTS of records already, the easiest thing may be for you to copy your table, then dump all the single field names into a spreadsheet, then use the Excel text to column wizard to split your names and copy these and Paste Append into your copy table. If you can do that yourself, you'll save time by not waiting for a working vba procedure to split the names for you. I'm assuming your names are divided by some sort of consistent character (,) or space. The odd one might give you problems (such as LL Cool J).

    To address your other questions is not really possible, given not much is divulged about your tables and relationships. Sounds like you might benefit from some normalization reading: Normalization Parts I, II, III, IV, and V http://rogersaccessblog.blogspot.ca/...on-part-i.html
    (and a lot of other stuff if you're really new to relational databases in general).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    As a general rule, I'd store names in separate fields. It's always easier to join them together than split them apart. That said, you can query your full name field with Like and wildcards to find Stallone:

    Like "*Stallone*"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the usual reason for splitting names is to do with indexing which has a big impact on how fast the db works.

    if you have your actors names in one field you can use LIKE with wildcards in a query e.g.


    [actorname] like "*Stallone*"

    or if using a search form, something like

    [actorname] like "*" & forms!searchform!txtsearchctrl & "*"

    would I have to define all the relevant column names to search against
    yes

    Finally (and I'm sure not least lol) is there an easy way to link / search tables, so that I could search both my film database and a tv series database for the same keyword or should I run seperate queries on them?
    I would expect both films and tv series to be in the same table - with a column to indicate which one it is

  5. #5
    ukdangerous is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    6
    Hi

    thanks to all that have replied, I will do more reading and will no doubt be back with more questions lol

  6. #6
    ukdangerous is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    6
    Hi All

    I'm back again!

    I have gone away and read up on some more information as advised and seem to be stuck on Primary and Foreign keys.

    Is it possible to have x 1 Primary key linking to several Foreign keys (x 1 per different table)?

    I have done up an overview of what I mean (see link below) I have listed the relevant details for ease of viewing and what fields (I think) should be connected.

    Am I on the right road or should I just start again as it almost appears that I am trying to combine x 2 different databases into x 1, in order to run queries / reports on the results?

    http://i65.tinypic.com/6f0cb8.jpg[/IMG

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  8. #8
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Going back to your original post, here is a link on data manipulation that should help you to split the names into two fields if you have not already done so.

    http://www.datapigtechnologies.com/f...anipulate.html

  9. #9
    ukdangerous is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    6
    Quote Originally Posted by alansidman View Post
    Going back to your original post, here is a link on data manipulation that should help you to split the names into two fields if you have not already done so.

    http://www.datapigtechnologies.com/f...anipulate.html

    Hi

    thanks managed to resolve that already

  10. #10
    ukdangerous is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    6
    Thanks

    I will look at that

  11. #11
    ukdangerous is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    6
    Related info on database design and planning here.

    Good luck.

    Thanks

    I will look at that

  12. #12
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by ukdangerous View Post
    Hi All
    Is it possible to have x 1 Primary key linking to several Foreign keys (x 1 per different table)?
    I have done up an overview of what I mean (see link below) I have listed the relevant details for ease of viewing and what fields (I think) should be connected.
    Am I on the right road or should I just start again as it almost appears that I am trying to combine x 2 different databases into x 1, in order to run queries / reports on the results?
    Regrettably, no you are not. First, a table should be all about an entity. The fields in the table are aspects (characteristics) of that entity.
    What happens if you want to add the possibility of a 6th star to a table as opposed to being limited to 5? You'd have to add fields, and this is a prime indicator of incorrect design. If you overlook an aspect of an entity and have to add a field to cover it, that is one thing. If you have to add fields to increase the number of the same aspect, that means your data is not normalized. This is what you'd have to do.
    Actors should be in their own table, characterized by whatever aspects you think appropriate. Titles would be in their own table. Here's where it likely make sense to separate TV actors from movie actors. Following the same principle I just mentioned, what are you going to do if you want to add theatre as an actor type - add a field? I hope not. The fact that you may never intend your db to encompass theatre actors is irrelevant. If a thought crosses your mind that to add an aspect would require another field, but you know you never will, it's still an indicator of incorrect design and you should carefully consider the implication of not correcting it.
    Is it possible to have x 1 Primary key linking to several Foreign keys (x 1 per different table)?
    Possible, yes. Good design? At the moment, I can't think of a case to support it. Generally speaking, the PK of one table becomes the FK to another. A Pk in the second table becomes a FK in yet another - and so on. Many to many relationships are a bit of a unique take on this, but I don't want to cloud the issue at this point because you haven't grasped the one to many design yet.

    The last answer you probably want is a suggestion on more reading, but take this kindly advice when I say that's what you need to do. Then post back with a revised concept of your db and we'll see if you get on the right track. It would be nice to see your concept without all the strikethrough on your text.
    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.ca/...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.ca/...ng-part-i.html

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.ca/...cation-in.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-02-2016, 11:41 AM
  2. Replies: 2
    Last Post: 06-19-2016, 12:32 PM
  3. Access 2016
    By Gina Maylone in forum Access
    Replies: 3
    Last Post: 12-11-2015, 09:44 PM
  4. What's new in Access 2016
    By pbaldy in forum Access
    Replies: 6
    Last Post: 09-28-2015, 12:41 PM
  5. Help needed : Library catalogue in access format is needed
    By dealers in forum Sample Databases
    Replies: 3
    Last Post: 01-16-2014, 02:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums