1 Form Two Tables 1 ID

[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.

 

[Douglas J. Steele]

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.