Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Posts
    3

    Unanswered: Calculated from a linked table

    I have two tables, in one I have the person's First and Last name. In the other I have a auto generator number.

    I am trying to add a calculated field to a form. The calculated field consists of the person initial's then the auto number. So if there was an entry that was linked to myself the calculated field should look something like this:

    HP-56

    To do some tests I have tried just putting in the entire value from the other
    table. When I go through the expression builder I end up with something like
    this:

    = [Salesmen]![FirstName]

    When I close and save the form it changes itself to:

    =Salesmen!FirstName

    How do you insert a calculated field so that it contains data from a linked
    table?

  2. #2
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by hazza96
    I have two tables, in one I have the person's First and Last name. In the other I have a auto generator number.

    I am trying to add a calculated field to a form. The calculated field consists of the person initial's then the auto number. So if there was an entry that was linked to myself the calculated field should look something like this:

    HP-56

    To do some tests I have tried just putting in the entire value from the other
    table. When I go through the expression builder I end up with something like
    this:

    = [Salesmen]![FirstName]

    When I close and save the form it changes itself to:

    =Salesmen!FirstName

    How do you insert a calculated field so that it contains data from a linked
    table?
    Hi hazza96,

    A CalculatedField is just a TextBox you add (not a field in your table) that holds the calculations you want. Just for a sample of what I mean is this. I have a field in my table FirstName and a field called EntryDate. To place these two together I would create an UnboundTextBox and place this in it:

    [FirstName]&" - "&[EntryDate]

    What this would give me would be this: Bud - 10/07/2004

    In your particular case I assume you have a Field on your form that is FirstName? Next, are your tables linked already? Form based on a Query that incorporates both tables? If so then ..............................
    Better yet look at the sample DB I just created for you. It does exactly what you want. I created 2 tables and pulled the data together in the form. When you open the DB go to DesignView on the form and look behind that last TextBox and see how I did it. Very simple.

    have a great day and let me know if it helps you.
    Bud
    Attached Files Attached Files
    Last edited by Bud; 10-07-04 at 06:05.

  3. #3
    Join Date
    Oct 2004
    Posts
    3

    Not quite

    I too had no problems pulling data from the same table and using it in a calculated text box/field.

    Now what would you do if you wanted the calculated field to be the DeptName not DeptID. The DeptName is not in the Employee table, it is in the linked table.

    Using your example database instead of seeing:

    SB-1

    I want to see:

    SB-Admin

    Is that at all possible?

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Good morning Hazza,

    I have attached the updated DB here. All I did, as you can see behind the scenes is just took the DeptId out and replaced it with DepartmentName, that way that dept. name will show.

    Let me know if this works well for you,
    Bud
    Attached Files Attached Files

  5. #5
    Join Date
    Oct 2004
    Posts
    3

    Thank you so much

    I work in the industry and should know better than to trust MS with what they are doing with their own product.

    MS Access is not my first choice, I tried convincing my client to go with MySQL and PHP but they were not willing to do that.

    If you go through the Exprerssion builder you end up with stuff like:

    [tblOffice]![DepartmentName] instead of just [DepartmentName]

  6. #6
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Unhappy

    Quote Originally Posted by hazza96
    I work in the industry and should know better than to trust MS with what they are doing with their own product.

    MS Access is not my first choice, I tried convincing my client to go with MySQL and PHP but they were not willing to do that.

    If you go through the Exprerssion builder you end up with stuff like:

    [tblOffice]![DepartmentName] instead of just [DepartmentName]
    Good morning Hazza,

    Not sure but did my second sample address your situation??? It had the Initials and the OfficeName as you asked. Only, you don't need to go through the expression builder as I didn't. Once you create the TextBox you simply go into it in DesignView and on the DataTab of the Properties sheet, you simply type in this:
    Code:
      =Left([FirstName],1) & "" & Left([LastName],1) & "-" & [DepartmentName]
    True you can actually do it in the ExpressionBuilder but no need to actually go there. In the ControlSource area just hit Shift+F2 to Zoom the window open and type in the above. Same thing just a different way. Let me know if this is still not what you are seeking, but I looked at the results and got just what you asked for. Otherwise, Post a sample of your database and I will try to fix it for you.

    Bud

Posting Permissions

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