Linking list for enemys

  • Thread starter Thread starter Praetorian269
  • Start date Start date
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??
 
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
 
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??
Click to expand...

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>
 
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.
 
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
Click to expand...
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! :)
 
Praetorian269 said:
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??
Click to expand...


I feel uncomfortable with the concept of backwards compatibility in a
correctional facility...

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

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
******************************
 
With all the classes that I attended, MS-ACCESS wasn't part of the Law
Enforcement curriculum.
 
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.
 
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! :)
Click to expand...

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
 
I work at a County Sheriff Department simply asking for assistance in
creating a classification
program for our classification officer.

Since you teach at your local community college, you should know that the
budgets of County
Departments do not provide enough funds to have programs written for them in
every aspect of
the job that they need.

I thought that this was a place to ask your questions, instead I get
ridiculed for asking a question outside of the realm of my expertise.

Now Betsy, what makes you think that I am an inmate verses a guard?

Joe Costa
 
At this time funds do not allow for hireing professionals to write the
database. I have written a database that allows you to enter a name on the
main form and all the enemies are displayed in the subform. Only problem is
that I have do this for each of the inmates that I want to add.

We have found the cardfile program by Microsoft will do this. Maybe we can
stick with that.
 
Thanks for the message. Ill take what I see here and give it a try when I
get back to work tomorrow morning.. :)

Deputy Joe Costa
Calhoun County Sheriff Department
Michigan
 
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,
 
John Vinson said:
On Sat, 8 Nov 2003 11:35:24 -0500, Praetorian269
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"?
Click to expand...

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.

Would it be easier if I posted it so others could see what I was working on?
 
That is exactly what Im looking for. Ill try the methods described here
tomorrow at work. Thanks for the information. The only thing I was trying
to avoid is having to enter the information twice for each person I add.
 
You made it sound so easy Marvin.... I'm only a beginner but I will try what
you said in the previous message.

Thanks...
 
I sent you a message. Cant wait to see what it looks like.
This is exactly what I have been trying to do and what I was looking for...
 
Back
Top