Results 1 to 5 of 5
  1. #1
    Luke is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    72

    Arrow Text/Number Relationship Problem

    I created a financial form and report which include the name of the company, the year, and the revenue. The form is based off of my Financial table called "tblFinancial". This table includes:

    Transaction#.......Autonumber, (primary key)
    EntityID...............Number,(Autonumber, primary key in another table called tblEntity)


    EntityName..........Text
    Year....................Number
    Revenue..............Currency

    With transaction# autonumber as my primary key, I can have multiple years and revenue values for the same entity.

    In my table, in design view, under the Lookup tab, BOTH my EntityName and my EntityID have the following:

    Display Control: Combo Box
    Row Source Type: Table/Query
    Row Source(for EntityID): SELECT tblFinancial.EntityID FROM tblFinancial;
    Row Source(for EntityName):SELECT tblFinancial.EntityName FROM tblFinancial;

    These combo boxes allow me to choose the EntityName and EntityID from a drop-down box in my tables, queries, and forms. My EntityName field has a direct coorelation/relationship to my EntityID. For example:

    EntityName: CompanyA......CompanyB........CompanyC........Comp anyD
    EntityID:..........1...................2.................. ...3....................4.......

    Here's my problem: I want to be able to just choose the EntityName from the combo box in a form, table, etc. so that it will automatically know the EntityID so that I won't have to input the EntityID each time I want to add Revenue for a different year. I'm guessing I won't need a combo box for the EntityID but I'd like to keep the combo box for the EntityName. How can I make some sort of relationship so that when I choose the EntityName it will also include its EntityID Number in a form?

    Thanks a lot.

    -Luke

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    You shouldn't store the entity name in your transaction table. It already exists in your entity table. the entityID in the transaction table should be a foreign key to the entity ID in entity.

    your combobx should contain the query:
    SELECT EntityID, EntityName FROM tblEntity

    EntityID would be the bound field. You hide it by making it's column width 0. Now when you run your Insert Into query for the transaction table, you simply do Me.ComboBoxName for the entityID.

  3. #3
    Luke is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    72
    EntityID would be the bound field. You hide it by making it's column width 0. Now when you run your Insert Into query for the transaction table, you simply do Me.ComboBoxName for the entityID.

    Ok that makes more sense but I'm still having a few problems. I removed EntityName from my transaction table. EntityID is still in BOTH my entity and transaction tables. I believe EntityID in my transaction table is a foreign key to my EntityID in my entity table because after making the relationship it has the "1" next to EntityID in tblEntity and the "" (infinity symbol) next to EntityID in tblFinancial.

    My combobox for EntityID in Design View of my transaction table contains the query:
    "SELECT tblEntity.EntityID, tblEntity.EntityName FROM tblEntity; "

    This is where I'm getting a little confused:EntityID would be the bound field. You hide it by making it's column width 0. Now when you run your Insert Into query for the transaction table, you simply do Me.ComboBoxName for the entityID.

    Am I supposed to make EntityID's column width 0 in my transaction table?? And I'm also confused as to how I run my Insert Into query for the transaction table and where I type Me.ComboBoxName for the entityID.
    Should I make a new query with my tblEntity and my tblFinancial so that I can make a form with my EntityName from tblEntity? And which table should I select EntityID from in the query, tblFinancial or tblEntity?

    Thank you so much.

    -Luke

  4. #4
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    The 0 width would be for the combobox. That's how you hide the ID field while still being able to use it. Otherwise the combobox would have 2 columns, one for ID and the other for the name. This way, with an ID column width of 0, it's visually gone.

    And regarding Insert Into query, it would be used on an unbound form. The form wouldn't be based off any table. It would just have data entry points to create SQL to add a record into the table. I personally don't like bound forms. I don't let anyone change what is on a table unless it is through an Insert or Update query that I've created.

  5. #5
    Luke is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    72
    Ok I made Column width 0 for EntityID in my transaction table. EntityID is still there when I run the table in datasheet view (I'm assuming it's supposed to be). I'm still kind of sidetracked on what exactly I need to do next. How exactly do you create an unbound form? I created a blank form but I'm not seeing exactly how I can add all of this info into a form. I want it to include EntityName, Year and Revenue (as well as EntityID and Transaction# but hidden if possible). Should I create a query based off of my transaction and entity tables and then create a form from that?

    Thanks.

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

Similar Threads

  1. Table/Relationship Problem?
    By WallbrownF in forum Access
    Replies: 7
    Last Post: 05-26-2011, 12:22 PM
  2. Relationship Problem?
    By j3lena in forum Database Design
    Replies: 1
    Last Post: 01-14-2011, 05:27 PM
  3. Relationship Problem
    By hawzmolly in forum Database Design
    Replies: 4
    Last Post: 07-18-2009, 05:39 PM
  4. Relationship problem?
    By amangill1984 in forum Access
    Replies: 0
    Last Post: 03-04-2009, 08:57 AM
  5. Normalisation and Relationship Problem
    By dromorkid in forum Database Design
    Replies: 3
    Last Post: 02-05-2009, 04:04 PM

Tags for this Thread

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