+ Post New Thread
Results 1 to 7 of 7
Office Software Thread, Access - Relationships in Technical; For arguments sake in this case I want to create a database in Access for all my assets. I have ...
  1. #1

    Join Date
    Oct 2008
    Location
    Lincolnshire
    Posts
    2,220
    Thank Post
    13
    Thanked 229 Times in 218 Posts
    Rep Power
    68

    Access - Relationships

    For arguments sake in this case I want to create a database in Access for all my assets.

    I have a table with all the makes in and have one with all the models in. The models on looks up to the makes one and pulls that information down.

    I then want to create the assets table. I want to be able to select the make and in the model field I want it only to display the items that are under the model I selected in the previous field.

    How do I do it?

    For arguments sake I have 10 makes and 50 models. I will then say have some 2000 assets.

    Can someone help please?

    Thanks in advance.

  2. #2

    bossman's Avatar
    Join Date
    Nov 2005
    Location
    England
    Posts
    3,942
    Thank Post
    1,199
    Thanked 1,069 Times in 760 Posts
    Rep Power
    330
    @MatthewL:

    Have a look here and save yourself some time: Free Access Database Sample - Asset Management Database

    This might save you time and effort

    Or if you still want to do it yourself here is a good tutorial: Building an Access Database From the Ground Up

    Either way enjoy yourself

  3. #3

    Join Date
    Oct 2008
    Location
    Lincolnshire
    Posts
    2,220
    Thank Post
    13
    Thanked 229 Times in 218 Posts
    Rep Power
    68
    I was only using asset management as an example to try and explain what I was trying to achieve.

    I will have a go myself and will have a read of that link.

  4. #4

    Join Date
    May 2008
    Location
    Cheshire
    Posts
    292
    Thank Post
    48
    Thanked 27 Times in 24 Posts
    Rep Power
    18
    Are you new at databases? I would have three tables - one for the make and model (tblMakeModel) one for the Make (tblMake) and one for the Model (tblModel):

    tblMakeModel:
    tblMakeMode_lID - auto num (pk)
    tblMakeMode_MakeID - num (fk)
    tblMakeMode_ModelID - num (fk)

    tblMake:
    tblMake_ID - auto num (pk)
    tblMake_Make - text

    tblModel:
    tblModel_ID - auto num (pk)
    tblModel_Model - text

    PK - Primary Key
    FK - Foreign key

    This would help save memory space and repeating your self.
    The SQL would look something like:
    Code:
    select tblMake_Make from tblMake left outer join tblMakeModel 
    tblMake_MakeID = tblMakeModel_MakelID
    left outer join tblModel
    tblModel_ModelID = tblMakeModel_ModelID 
    where tblModel_ID = @ModelID
    Note that the @ModelID is the variable you want to look up.

    If you're not sure what the above does here it is rewritten slightly differently:
    Code:
    select tblMake_Make from tblMake, tblMakeModel, tblModel
    where
    tblMake_MakeID = tblMakeModel_MakelID and
    tblModel_ModelID = tblMakeModel_ModelID and
    tblModel_ID = @ModelID
    Last edited by Pashers; 17th March 2011 at 12:18 PM.

  5. #5

    Join Date
    May 2008
    Location
    Cheshire
    Posts
    292
    Thank Post
    48
    Thanked 27 Times in 24 Posts
    Rep Power
    18
    Or a lazy/quick dirty way to do it (I would not recommend this as it means duplicating yourself but if it suites your needs and makes your life easier):
    tblMakeAndModel
    tblMakeAndModel_ID (PK)
    tblMakeAndModel_Make (text)
    tblMakeAndModel_Model (text)

    Code:
    select tblMakeAndModel_Make from tblMakeAndModel where tblMakeAndModel_Model = @variable

  6. #6

    Join Date
    Oct 2008
    Location
    Lincolnshire
    Posts
    2,220
    Thank Post
    13
    Thanked 229 Times in 218 Posts
    Rep Power
    68
    I have done some database work, I will have gander when I get some time at them few bits and see where I can get.

  7. #7

    GREED's Avatar
    Join Date
    Mar 2008
    Location
    Portsmouth
    Posts
    3,059
    Thank Post
    375
    Thanked 375 Times in 306 Posts
    Blog Entries
    8
    Rep Power
    177
    If using Access there are a set of GUI options that will 'filter' available fields based on selections in other fields. I going to take a punt and say you are wanting to use combo boxes for these fields?

SHARE:
+ Post New Thread

Similar Threads

  1. [MS Office - 2007] MS Access 07 - Relationships problem
    By edd in forum Office Software
    Replies: 20
    Last Post: 15th May 2009, 06:20 PM
  2. Relationships with local schools
    By FreeWill in forum General Chat
    Replies: 21
    Last Post: 20th May 2008, 02:33 PM
  3. Trust Relationships and DeepFreeze
    By AdamWilden in forum How do you do....it?
    Replies: 6
    Last Post: 4th February 2008, 11:16 AM
  4. Sales: building up relationships
    By contink in forum General Chat
    Replies: 8
    Last Post: 30th January 2008, 10:17 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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