Linking list for enemys

P

Praetorian269

I work in a correctional facility and I am trying to make a database that
when I type a name it will show enemys in a subform.

I have created this however I would like the search to be backwards
compatable. If I type a name from the subform list it will pull all the
people linked to his table.

Can anyone tell me how to do this??
 
P

Phil Stanton

Try a table containing all the names, and another many to many table linking
each individual in the names table to as many other individuals as you like
in that table.

eg ChapsTable
ChapsID Autonumber
ChapsName Text

JoinChapChaptable
ChapID1 Long
ChapID2 Long
Retationship Byte

The Main form would be related to ChapsID and the subform would pick up all
the related records via the JoinChapChap table. DoubleClick on the record in
the subform, and that would become the record in the main form

Have fun

Phil
 
J

John Vinson

I work in a correctional facility and I am trying to make a database that
when I type a name it will show enemys in a subform.

I have created this however I would like the search to be backwards
compatable. If I type a name from the subform list it will pull all the
people linked to his table.

Can anyone tell me how to do this??

You'll have to give us some help here. What's your table structure?
How are you recording enmity between inmates? And what do you mean by
"type a name from the subform list"?

Just FWIW, here's how I'd structure this: you have a table of Inmates
(I presume!). Each inmate may have zero, one, or more enemies, who are
also inmates. I'll assume that if Joe is an enemy of Fred, that Fred
is also an enemy of Joe. This is a many to many relationship from the
Inmates table *back to the Inmates table*; the simplest way to model
this is to have a table, Enmity, with two fields InmateID1 and
InmateID2 (and perhaps a comments field describing the nature of their
displeasure with one another). It would be simplest for form design if
you enter each enmity relationship twice, with the ID's swapped.

You can then create a Form based on Inmates with a Subform based on
Enmity, or on a query joining Enmity to Inmates by InmateID2,
displaying the name and other info about the enemy from the Inmates
table.

<Newsgroups trimmed since my ISP doesn't carry c.d.m.a, therefore
posted and emailed; please reply in microsoft.public.access>
 
T

tina

you can't have a many-to-many relationship between tables.
but you can build an Enemies table with a combo primary
key:

InmateID (foreign key from tblInmates)
EnemyID (same foreign key from tblInmates)

i built a simple db, to see if my concept would work: two
tables and two forms, with the necessary coding to support
looking at the inmates list, clicking a specific inmate
and opening a list of enemies for that inmate only. once
that table is open, clicking a specific enemy will change
the list to show all the enemies of that inmate.

i can't really explain the details without writing a book
here. if you want a copy of the prototype i built to see
how i set it up, email me and i'll send it to you. it's
written in Access 2000 (i can convert down to Access97 if
need be) and is 424 KB in size.

email is ttaccess1 at yahoo dot com.
 
T

tina

Just FWIW, here's how I'd structure this: you have a
table of Inmates
(I presume!). Each inmate may have zero, one, or more enemies, who are
also inmates. I'll assume that if Joe is an enemy of Fred, that Fred
is also an enemy of Joe. This is a many to many relationship from the
Inmates table *back to the Inmates table*; the simplest way to model
this is to have a table, Enmity, with two fields InmateID1 and
InmateID2 (and perhaps a comments field describing the nature of their
displeasure with one another). It would be simplest for form design if
you enter each enmity relationship twice, with the ID's
swapped.


that's just the concept i came up with; it encourages me
to have my design idea validated by one of the experts. i
assumed the original poster wanted to see the list of
inmates, select a specific inmate and see his enemies on a
subform - easily doable. but then it sounded like he
wanted to select an enemy on the subform and see a list of
that person's enemies. i couldn't figure out how to do
that easily in a mainform/subform setup, so i used two
separate forms. a selection on the inmates form opens the
enemies form, filtered; a selection on the enemies form
refilters the enemies form. (also set up data entry on the
enemies form to automatically enter an additional record
with IDs swapped, as you described.)
your feedback helped me, too, john, thanks! :)
 
F

fp

Can anyone tell me how to do this??
I feel uncomfortable with the concept of backwards compatibility in a
correctional facility...

I feel uncomfortable that someone doing programming work in the correctional
facility has to ask this question. I hope he isn't maintaining the program
that controls the cell doors and gates.

--
******************************
Fred Parker
Lynn Consulting Group, L.L.C.
http://www.lynnconsultinggroup.com
******************************
 
B

Betsy

It sounds to me like this is an inmate, not a guard, who is posting. I
teach in my local community college, and they used to offer computer skills
training at our a max security prison that happens to be in our town. The
inmates were able to get a lot of training while incarcerated.
 
A

Arvin Meyer

tina said:
that's just the concept i came up with; it encourages me
to have my design idea validated by one of the experts. i
assumed the original poster wanted to see the list of
inmates, select a specific inmate and see his enemies on a
subform - easily doable. but then it sounded like he
wanted to select an enemy on the subform and see a list of
that person's enemies. i couldn't figure out how to do
that easily in a mainform/subform setup, so i used two
separate forms. a selection on the inmates form opens the
enemies form, filtered; a selection on the enemies form
refilters the enemies form. (also set up data entry on the
enemies form to automatically enter an additional record
with IDs swapped, as you described.)
your feedback helped me, too, john, thanks! :)

Hi Tina,

It is very easy to use a subform for this. As John mentioned use 1 table for
the inmates and a second with 2 fields, InmateID and EnemyID for the
enemies. Both InmateID and EnemyID draw their source from the Inmates table.
The InmateID is a hidden textbox on the subform, linked to the main form.
The EnemyID on the subform is a combobox with a select statement choosing
the EnemyID from the list of inmates in tblInmate. Presumably an inmate can
be his/her own enemy if they are suicidal. Now using the double-click event
of the combo on the subform, you can just reopen the main form to that ID
with code like:

Sub cboEnemyID_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmInmates", , , "InmateID=" & Me.cboEnemyID
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
C

Cheryl Fischer

Joe,

Don't let it bother you. I sometimes think that the responders in the
newsgroups (myself included) see so many posts regarding orders and employee
databases, mailing labels, etc. (you know, same old stuff) that when a post
about something novel (a database about hogs, for example) or exciting
(prisoners and enemies) comes along some of us can't resist "running with
it".

You got responses from Arvin Meyer and John Vinson - both MVPs and two of
the best at this business. If you need more detail from them or have
questions, don't hesitate to reply to their posts.

hth,
 
J

John Vinson

I have setup 2 tables

InmateInfotbl:
AutoNumber
ControlID
InmateName

EnemyDatatbl:
ControlID
EnemyName

I have it working. In the main form I have InmateInfotbl and in the subform
I have the EnemyDatatbl. Works great however I was hoping that there was a
way that when I add the information in the subform it would automatically
add it to the InmateInfotbl as a seperate record. This way I could do a
search in the InmateInfotbl and it would pull all the records relating to
that inmate.

Look at my suggestion, and Phil's and Arvin's again. Given that an
"Enemy" is also an "Inmate" (at least I assume so) there should NOT BE
a name in the EnemyDatatbl, just a link back to the InmateInfotbl.
Would it be easier if I posted it so others could see what I was working on?

No. Posting binaries in these text newsgroups is strongly discouraged.
 
L

Larry Linson

If you'll give us a little more detail of what you have and what you want to
do, maybe we can get past the off-topic stuff and get on with trying to help
you, Joe.

Form with subform, but you have to _do this_ for each inmate you add? Do
_what_? If you are creating a separate form for each inmate, then that is
clearly unnecessary work -- please clarify.

Larry Linson
Microsoft Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top