Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

1 Form Two Tables 1 ID

Discussion in 'Information Technology' started by Sue Wilkes, Jul 28, 2006.

Tags:
  1. Sue Wilkes

    Sue Wilkes
    Expand Collapse
    Guest

    I have two tables, table 1-HYInReg contains the following fields
    RegisterNmber (long integer - Autonumber and key field)
    DeptCode
    DateReceived
    CompanyName(s)
    ForwardedTo
    Subject
    HP1
    HP2
    HP3
    Designation
    Reason(s)forEdit

    Table 2-audHYInReg has fields

    audID (Autonumber, long integer, Key field)
    audType
    audDate
    audUser
    RegisterNumber
    DateRecieved
    CompanyName(s)
    ForwardedTo
    Subject
    HP1
    HP2
    HP3
    Designation
    Reason(s)forEdit

    I have 1 form containing the fields from Table1 could you please tell me how
    I can display fields audType, auedDate, audUser on my form using the
    RegisterNumber as the common field. Any help would be greatly appreciated as
    you can tell I'm a complete novice at Access.
     
  2. Loading...


  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    No offense, but you've got almost all the fields from HYInReg in table
    audHYInReg. Part of the design principles of using relational databases is
    that no piece of data is repeated in multiple tables. You might want to take
    a look at some of the references on how to normalize tables that Jeff Conrad
    has at
    http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

    That having been said, if you need data from two different tables on a form,
    the normal approach is to create a query that joins the two tables, and use
    that query as the RecordSource for the form. In your case, create a new
    query and drag the two tables into the design area. If you haven't created
    a relationship between the two tables (so that no line appears connecting
    them), select the RegisterNmber field from table HYInReg and drag it onto
    the RegisterNumber field in table audHYInReg. Drag whichever fields you want
    from the two tables down into the grid, set the appropriate ORDER BY and
    save the query.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Sue Wilkes" <SueWilkes@discussions.microsoft.com> wrote in message
    news:BE9A1ABD-B35F-4941-A596-172EC5C75F4C@microsoft.com...
    >I have two tables, table 1-HYInReg contains the following fields
    > RegisterNmber (long integer - Autonumber and key field)
    > DeptCode
    > DateReceived
    > CompanyName(s)
    > ForwardedTo
    > Subject
    > HP1
    > HP2
    > HP3
    > Designation
    > Reason(s)forEdit
    >
    > Table 2-audHYInReg has fields
    >
    > audID (Autonumber, long integer, Key field)
    > audType
    > audDate
    > audUser
    > RegisterNumber
    > DateRecieved
    > CompanyName(s)
    > ForwardedTo
    > Subject
    > HP1
    > HP2
    > HP3
    > Designation
    > Reason(s)forEdit
    >
    > I have 1 form containing the fields from Table1 could you please tell me
    > how
    > I can display fields audType, auedDate, audUser on my form using the
    > RegisterNumber as the common field. Any help would be greatly appreciated
    > as
    > you can tell I'm a complete novice at Access.
    >
     

Share This Page