[AccessD] Table of Names

The query looks ok from what I can tell.

I went back to look at what we had done, you do not need to store the IDs in
the main record if you store the ID Type in the intermediate table.  That
is, type=0 may be the contact type, type=1 may be the released by type, etc.

So the intermediate table may be something like:
IMID, PermitID, TypeID, PermitNamesID.

As for populating the types on the form, you could either have a combo box
for each type that contains the ID and names from tbl_PermitNames.  If you
go this route, I would think that the combo boxes would be unbound.  When
you load up the form, you would set the combo boxes to the proper
information.  When the record is saved, you would have to update the
intermediate table.

Or, as I have done, you would call another form that you pass the type into,
and allow the user to select a name from a list.  If the user does not
cancel out of the form, then you would create the intermediate table entry
for that type, and then update the original forms type field (which would
not be editable by the user and would be unbound).  When you show the form
for a given permit, you would have to query the intermediate table to get
the data to display in the types fields.

On plus to having the intermediate table is that if you need to add a new
type, it is easy, you just define a new type.

Sorry for the confusion, it has been a while since I looked at any of that
setup.

Bobby

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hollis,Virginia
Sent: Thursday, April 28, 2005 8:26 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Table of Names


This is what I did in the query, is this correct?

SELECT tbl_Type.NameType, tbl_PermitNames.PermitNames

FROM tbl_PermitNames INNER JOIN ((tbl_NameType INNER JOIN tbl_Permit ON
tbl_NameType.NameTypeID = tbl_Permit.ContactID) INNER JOIN tbl_Type ON
tbl_NameType.TypeID = tbl_Type.TypeID) ON tbl_PermitNames.PermitNamesID =
tbl_NameType.NameTypeID;

 

If the tables are set up correctly (as above), when I add a combo box on the
form what do I put in the source to have it add the data for the field into
the tables? If I want to select a Contact person, how does it add the data
to the tables? What about adding a new contact to the list?

 

Virginia

 

Ok, I think I understand your issue now.  Sorry that my first try was so far
off.  I did something similar with an address book thingy for this
application.  A given entry could be primary contact on one item, payroll
contact on another, etc.  So that I did not have to duplicate the people
data, I used an intermediate table to store links to the people data.
 
There is a table of contact information for each person.  There is another
table that contains the ID of the record that will be associated with the
person.  This other table also contains the ID of the person in the people
data table,
 
An example:
 
Table Main
MainID   Contact    ClosedBy     ReleasedBy
3            43           49           49
 
 
Intermediate table
IMID    MainID   PersonID
 43       3         754
 49       3         29
 
People Table
PersonID       FirstName      LastName
 754           Joe            Schmoe
 29            Suzy           Chapstick
 
So you would store the IMID into the Main table for the various contacts.
The intermediate table would contain a link to the Main table and the People
table.  The People table just contains information on the individual people.
 
So to get the Contact person, you would link the ContactID to the
Intermediate table IMID and then link the PersonID in the Intermediate table
to the PersonID in the People table.
 
In this way, you are not duplicating the People data.
 
Bobby

 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com