Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2008
    Posts
    40

    Unanswered: beginner db dummy help

    HI

    I am new to databases but I have been reading here and there...I have a few questions:
    1-I ran the import excel wizard which handle my 2d table quite nicely but I am wondering how to break out a table from the main one after the wizard has already run through - I was able to do it pretty easily during the wizard but I think a couple more columns could be broken out into their own tables...no idea how to do this

    2-a yes/no boolean - should it be identified as a yes/no or should I leave it as text? I am getting 0s and -1s...

    3-is it wrong to use multiple entries in a table where each each entry would typically only have a one to one relationship?

    4- this may demand its own post, but I am wondering if anyone has seen any templates for architectural space databases...I haven't been able to find anything, but there must be something like it out there...

    thanks!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    1. By using a Make Table query.
    2. Yes/No.
    3. Not sure I understand this one.
    4. No.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Apr 2008
    Posts
    40
    to explain no. 3 - in MS access 2007 you can specify multiple entries per field - can this take the place of specifying on each row a relationship between only two entities?

    for example in a predator-prey table:
    predator prey
    lion antelope, zebra, gazelle

    or
    lion antelope
    lion gazelle
    lion zebra

    as for the YES/NO situation, what is with the 0s and -1s?

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    0 is false and -1 is true.

    And I'd personally use multiple records rather than multiple entries in one field.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Definately use multiple records.
    Last edited by gvee; 05-30-08 at 09:47.
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ^ That's ambiguous! Do you mean multiple record entries or multiple entries in one field?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ha, thanks for pointing that out - I just re-read it and it didn't make much sense eh!

    multiple records, as per your example
    Quote Originally Posted by oompa_1
    lion antelope
    lion gazelle
    lion zebra
    George
    Home | Blog

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I had to point it out! For a second there I thought you were stating that you supported the multiple entries in one field method
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Apr 2008
    Posts
    40
    so does that mean that the multiple entries in one field method is invalid? or is MS Access just doing the dirty work behind the scenes?

  10. #10
    Join Date
    Apr 2008
    Posts
    40
    and back to issue no.1 - "1-I ran the import excel wizard which handle my 2d table quite nicely but I am wondering how to break out a table from the main one after the wizard has already run through - I was able to do it pretty easily during the wizard but I think a couple more columns could be broken out into their own tables...no idea how to do this"

    you replied that I should use a make table query...couldnt figure this one out. I was able to make the table, but it wasn't linked back to the root or master table that essentially is keeping track of how all the entries are connected...
    Last edited by oompa_l; 06-01-08 at 18:33.

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Perhaps not "invalid" but it is clearly not the best practice.

    Issue no 1. Make a query to extract out the fields you want to "break out" in to another table. Then select the Make Table query type and run it. This will create a new table that contains what your query would normally show.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Apr 2008
    Posts
    40
    ok, thanks...I was able to make the table as you instructed previously, but then I found that the entries from the original table were still listed as though they remained in that table - instead of being called in the column heading as "Lookup in <insert table name here>" - this leads me to believe that I now have those entries in two spots without the linking that I was after...I don't know if you can understand my commoner's attempts to describe what is happening...

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    That's what it is designed to do TBH, that is what I thought you wanted to do ^^

    Once you have the extra table, you can then start looking at:

    * Creating an ID column for the new table.
    * Removing redundancies from the new table.
    * Adding the foreign key in the main table.
    * Creating a relationship between the two tables.
    * Verifying that what is done is correct and complete.
    * Deleting redundant fields from the main table.

    That's what I would be doing.

    There is no easy way to move out and relate such data fragments... actually there is one thing, the table analyser (Tools - Analyse - Table), but I have never found it to be terribly accurate. Give it a go but for your sake, do it on a copy of the database and make sure you have adequate backups!!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  14. #14
    Join Date
    Apr 2008
    Posts
    40
    ok, i think i got it now.

    now I have moved the animalID table out to its own table separate from the one that starts to bring their IDs together with their attributes. I am making a table that is a predatorPrey table - one comlumn predator, one side prey. I looked a little more into the multiple entries issue and it was as I suspected, a special MS feature that does all the breakdown into individual rows behind the scenes.
    I have lookup fields for both predator and prey and they refer to the animalID table. I am trying to get the lookup table to restrict the values to those who are attributed as carnivore or omnivoreim in a complete different table. I tried to use criteria in the Design view of the Query but when I went to make the table I got unexpected results.

    so just to brak it down - there are a number of tables that try to reduce the number of entries, eg a table dedicated to the animals identified by their names, a table for diets, a table for specialization, one for tables etc etc.

    then I have a table - let's call it Master - which connects these disparate tables composed of lists of attributes.

    Then I want a table which is constructed from a query of the Master table with a "criteria" imposed on it which restricts the values - in this case "carnivore" AND "omnivore" for predators.

    Am I missing something? Is this possible?

    thanks so much for all the help,

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes it's possible. Using queries to restrict records shown is very normal.

    If you could post up your table structure, you could get more specific advice to address issues like a query giving you "unexpected results" ... of course you'd need to elaborate on what this means ... in what way were the results unexpected?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •