Results 1 to 8 of 8
  1. #1
    phoebonacci is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2012
    Posts
    4

    How to automatically assign values from another table when a new record is created?

    Dear all,

    First off let me admit that I am a novice at Access and am well aware that my question could be stupidly simple, so my apologies if this is the case! That said, I'm also at a complete loss for how to proceed..

    1st, I would like to add a field to a form where I can input data to records from a second table, and then have values from the first table automatically included in the new record in the second table. I can do this manually with a subform but want to avoid extra data entry where possible.



    2nd, if to replicate the information in this way is redundant, then how do I ensure that any reports I do with the records from the second table will pull the values from the first table?

    A bit of background: Table1 contains info for lectures recorded. Table2 will share some of Table1's info (unique values like who gave the lecture, the language it's in, etc), but will be used to house info on specific moments in those lectures that we will want to return to later. I would just include these moments as a field in Table1 except that there may be more than one per lecture and I understand that reporting would therefore be more difficult.

    Any assistance to this noob would be hugely appreciated!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is this what you're after?

    BaldyWeb - Autofill

    Generally you wouldn't store anything but a key value from the first table in the second. You'd get the related info for forms/reports by using a query that joined the two tables together.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    phoebonacci is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2012
    Posts
    4
    Thank you! It is very close to what I'm after. It would work but there is a slight difference in my needs from that of the Customer-order analogy. In my case, there would only be ONE customer, with the order records in the second table "belonging" to the one customer record in the first table.

    So I do not need to select the values from the first table with a combo box; instead I would need the information from the one record in the parent table (using the term as an analogy, probably inappropriately) to be assigned to whatever child records are created in the child table so I can pull reports with that information later. In other words I would need to have the customer name, language preference, address etc appear in reports on the orders later.

    I hope this makes sense! I realize that half the battle is in describing something I don't have the terminology for!

    Thanks again for your time!

    Phoebe






    Quote Originally Posted by pbaldy View Post
    Is this what you're after?

    BaldyWeb - Autofill

    Generally you wouldn't store anything but a key value from the first table in the second. You'd get the related info for forms/reports by using a query that joined the two tables together.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    There's only one record in the parent table? You wouldn't normally store that type of thing in the child table anyway. You'd base your reports on a query that joined the two tables, enabling you to return fields from both.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    phoebonacci is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2012
    Posts
    4
    There will be more than one record in the parent table, but each record in the child table will "belong" to only one record in the parent table. I think you are right though, this can probably be accomplished via queries... now I just have to learn how to assemble them! I may have to haunt this thread again after a few more days of head bashing

    Thanks again for your time.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem. You don't have separate child tables for every record in the parent table, do you? That would normally be a mistake.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    phoebonacci is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2012
    Posts
    4
    Nooo! The situation is this: I am tracking academic lectures (parent table) and among other things, moments in those lectures that are quoteworthy (the "gems", child table). So each gem belongs to a lecture but a lecture could have more than one gem, and I will want to pull reports on the gems later based on the attributes of their parent lecture (who gave the lecture, what language, what category the lecture is in). Correct me if I'm wrong but I think you are right that it is a question of me learning how to use queries properly...

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Yes, I think you need to learn how to use queries. Post back if you get stuck.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 2
    Last Post: 12-19-2011, 10:51 AM
  2. automatically assign numbers
    By gvh in forum Access
    Replies: 3
    Last Post: 12-08-2011, 01:53 PM
  3. Replies: 1
    Last Post: 09-21-2010, 09:49 AM
  4. Replies: 1
    Last Post: 06-24-2010, 08:08 AM
  5. Replies: 2
    Last Post: 03-27-2010, 10:52 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