Results 1 to 12 of 12
  1. #1
    eihi is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    8

    Question regarding creating tables, naming conventions, and forum policies

    Hello,
    I have a fairly simple homework project due on Sunday which I am currently working on, I wanted to go to my local learning center at my community college for tutoring services, but nobody proficient in Access is available the times I am available.

    As I work through this project on my own, would it be ok if I posted questions that came up. I am not looking for anyone to do my work or tell me the answers, just tell me if what I did was correct in their opinion.

    Thanks much
    Also if this is against forums guidelines, I am sorry just comment and I will delete my thread. Looked through the FAQ but didn't see anything regarding this(sorry if I missed it).

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum......

    Glad to see that you recognize YOU need to do the work to learn.

    Under these guidelines, ask away. There are lots of knowledgeable people here that will nudge you in the right direction.

  3. #3
    eihi is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    8
    Thank you!

    Yes ssanfu, if I don't learn, I am in trouble the rest of the semester , lol
    I am working through my project now, I will post in here if I have some questions or after I finish to get some feedback.
    Thank you for your kindness.

  4. #4
    eihi is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    8
    Hi There, I have returned. So far things have been going ok I did have a design question though if someone might point me in the right direction.

    So I am doing a database of books,
    I have a table called library, two of my field names in that table are fiction and non fiction, datatype for both is lookup wizard.

    I have two additional tables one named nonfiction and the other fiction, though this works ok, I think it might be done better.
    I was thinking instead of doing the nonfiction and fiction tables, doing genre then for records Autobiography(NonFiction) etc.
    Click image for larger version. 

Name:	Capture.JPG 
Views:	31 
Size:	82.2 KB 
ID:	26328

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Well, "ID" is a poor name for a field because it is not descriptive. Imagine if you had 25 tables all, with "ID" as the PK field!

    Also, I NEVER use Look up FIELDS in tables. From what I have read, most Access programmers don't either.


    Since this is the Library table, I would use "LibraryID_PK" as the PK field name.

    table Library
    LibraryID_PK Autonumber PK
    Title Text
    Author Text
    MediaType Text
    Genre Text
    GenreType Text


    If you are going to use Look up TABLES (different than look up fields),
    I might have this structure:

    LibraryID_PK Autonumber PK
    Title Text
    Author Text
    MediaType_FK Number - Long Link to table MediaType
    Genre_FK Number - Long Link to table Genre
    GenreType_FK Number - Long Link to table GenreType

    Table "Genre" might have values of Reference, Autobiography, Essay, etc
    Table "GenreType" might have "Fiction", Non-fiction".

    If a book can have more that one author, I would have a table for Authors.


    Have you drawn out the tables structures and relationships on paper and tested that the design works correctly?



    (and maybe come up with a better dB name??? look at the top right of the image)

  6. #6
    eihi is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    8
    Oh my goodness, lol. I hadn't noticed the top right of the image and the name.
    I have had quite a lot trouble getting my database to work properly and after many tries, I finally started a draft from anew and that was what I posted here.
    My frustration level was high when I started the draft, It doesn't look that I am able to edit that image out
    This is my first database from scratch and is a lot harder than I thought it was going to be.

    I did draw out my ideas for tables on a piece of paper, though in reference to testing(I think you mean did I try and input data), things were not coming out like I liked, or I simply didn't know how to do it.

    I appreciate your input regarding the tables and naming convention.

  7. #7
    eihi is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    8
    See post #10
    Last edited by eihi; 11-13-2016 at 08:06 PM.

  8. #8
    eihi is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    8
    Not possible to actually upload a database over 500kb?

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    No. But you could run a Compact and Repair on your database. Then, create a zip file with your database and post that.

    Good luck.

  10. #10
    eihi is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    8
    Need some advice if going in the right direction.... New Compressed (zipped) Folder.zip

    1. Better DB name.

    2. Tried to use the Leszynski naming convention. am I on the right track?

    3. In the tblLibrary for genre type I used the "look up table" which I got to work.

    4. For MediaType I used the "look up field", which also works. (This is the example my professor had used)

    5. I did try and further my understanding between the two, but still confused! I did one of each hopefully that will give me a little better understanding, at least for the time being.

    6. Yes, to your question a book can have many Authors, does that mean I will always need to enter A new author in the tblAuthors, before entering information into my database?

    7. When it comes down to data entry, I was going to try and make a navigation form now to see if/how I could streamline the process.

    8. Any thoughts on how I did the combobox for the Location?
    Last edited by eihi; 11-13-2016 at 10:55 PM.

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    1) Yep, better But strive for constancy. Two of the tables have the suffix "_PK" in the PK field name and the PK field in other three tables don't have the suffix. If you use the suffixs "_PK" and "_FK", they should be used in every table.

    2) For the field names, mostly yes.
    The bound field of the control "strMediaType" is "MediaTypeID_PK", but you have the field name prefixed with "str"; I would use "lng" (long integer)
    Same for the other long integer fields - you have the prefix "int", but the field type is long integer ("lng").

    For the control names on the forms, needs work. Access usually names the controls the same as the control source.
    For example, the combo box control for strMediaType is named "strMediaType". I would use "cboMediaType".


    3) What I use....

    4) I NEVER use "look up fields".

    5) See:
    The Evils of Lookup Fields in Tables
    http://access.mvps.org/access/lookupfields.htm

    6) You would use a sub form to add the authors. And, yes, any author would need to be in the Author table before being able to link an author to a book.

    7) I don't use the navigation form - I build my own main form. To me, the navigation form is limiting. My personal opinion.

    8) Well, when you entered an IP address, you have "https://http://www.freeclassicebooks.com/Jane%20Austen/Pride%20and%20Prejudice.pdf"
    If it works for you, use it.


    One more thing. PK fields that are an autonumber type should NOT be displayed on a form. They are used to link table and have NO real world meaning.

  12. #12
    eihi is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    8
    I just wanted to sincerely thank you for your help ssanfu.
    Also big thanks for the help in explaining how to upload, Mr. Orange

    I marked the thread as solved, as I think any future issues won't pertain to the threads subject matter as much.
    Best of wishes

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

Similar Threads

  1. Naming convention question? btn or cmd?
    By RLehrbass in forum Access
    Replies: 7
    Last Post: 09-21-2015, 07:51 PM
  2. Naming standards or conventions
    By Jennifer Murphy in forum Access
    Replies: 10
    Last Post: 02-01-2014, 08:04 PM
  3. Replies: 5
    Last Post: 05-08-2013, 05:36 PM
  4. Setting Naming Conventions for IDs
    By LukeJ Innov in forum Access
    Replies: 1
    Last Post: 04-25-2013, 06:27 AM
  5. Naming conventions
    By Yesideez in forum Database Design
    Replies: 3
    Last Post: 06-29-2011, 08:55 AM

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