Cross reference ID numbers

[Stressed out.]

I think I have a unique situation... I have looked through lots of threads
and can't find anything on this.
I have a form which uses an autonumber. When a user creates a new record,
they input lots of information. There are five different tape number boxes,
each has their own place on the table. (Tape1, Tape2, etc.) There also is a
multiple incidents box where the user is to input the record numbers for
other records with matching tape numbers. I would like to automate the
multiple incidents box so that when a matching tape number is input into any
one of the five tape boxes, it automatically inputs the other record numbers
which have the same tape number in them. There is more complexities to this,
but I think if I got off to the right start, I could figure out the other
criteria I need.
Please let me know if this isn't making sense, and I will try to clarify it
further.

 

[John Vinson]

I think I have a unique situation... I have looked through lots of threads
and can't find anything on this.
I have a form which uses an autonumber. When a user creates a new record,
they input lots of information. There are five different tape number boxes,
each has their own place on the table. (Tape1, Tape2, etc.)

GREAT big red flag going up there....

Are you *absolutely certain* that there will never be, and can never
be, a sixth tape box?

It really, really sounds like you have a one to many relationship from
whatever this entity might be to boxes (or maybe a many to many
relationship, it's not clear to me what a tape box might be).

There also is a
multiple incidents box where the user is to input the record numbers for
other records with matching tape numbers. I would like to automate the
multiple incidents box so that when a matching tape number is input into any
one of the five tape boxes, it automatically inputs the other record numbers
which have the same tape number in them. There is more complexities to this,
but I think if I got off to the right start, I could figure out the other
criteria I need.
Please let me know if this isn't making sense, and I will try to clarify it
further.

It doesn't make sense to me, and it CERTAINLY does not make sense to
have five fields in your table - each of which can (apparently)
contain multiple values. Could you please explain a bit about the
real-world situation? What are these "incidents", what's a "multiple
incident box", and what's a "tape box"?


John W. Vinson[MVP]

 

[Landywednak]

I think you need to cut you Tape box Fields from 5 to 1 on your main table
Create combo box on your form associated with your Tape Box Field contining
the list of tape boxes 1-5

Then add a Tape Box Field to your multiple instances table and create a
relationship between the two

Create a query built from both tables that contain the tape box field, draw
the tape box record from one side(tape box table) and the required records
from the (Multiple Instances Table) if you wish you could create a parameter
query to filter records by text box number.

You could use this to make a subform and place this into your table based
upon all records from multiple instances and only records = to tape box number

 

[Stressed out.]

OK. I'm sorry. I do need to clarify. I am the systems admin for a
Surveillance department at a casino. The form I am referring to is what we
use to put a "hold" on video tapes when needed. when I say I have 5 tape
boxes, I mean text boxes that hold the control number of the tape to be held.
I made five of them so that we could hold multiple tapes for one incident. (I
would love to know how to just have one tape box and be able to add more only
when needed.) If we have to hold more than 5 tapes, we have to do a second
form with the additional tapes.
Sometimes the same tape will be held for two or more incidences. In that
case, the person filling out the "multiple incidents" box should reference
any other ID number that the same tape is held on. I would like to automate
that cross reference.
My database is quite large, and I am proud of it, however, I have only
learned by doing, so I know I am still a newbe. I appreciate your time and
effort to help me out.

 

[John Vinson]

OK. I'm sorry. I do need to clarify. I am the systems admin for a
Surveillance department at a casino. The form I am referring to is what we
use to put a "hold" on video tapes when needed. when I say I have 5 tape
boxes, I mean text boxes that hold the control number of the tape to be held.
I made five of them so that we could hold multiple tapes for one incident. (I
would love to know how to just have one tape box and be able to add more only
when needed.) If we have to hold more than 5 tapes, we have to do a second
form with the additional tapes.

You're still apparently thinking in terms of Forms holding data. They
don't. *TABLES* hold data; a Form is just a tool, a window onto the
data stored in a Table.

A better design would be to have a many-to-many relationship with
three tables: a table of Tapes (each tape being described by, I'd
guess, the location of the camera and the timerange taped); a table of
Incidents; and a third table of IncidentTapes. This would have links
to both the tape and incident tables. You'ld use a Subform on the
Incident form to view and enter data into this table - one row per
tape. If a given incident needed three, or five, or thirteen tapes,
you'ld just add that many rows.

Sometimes the same tape will be held for two or more incidences. In that
case, the person filling out the "multiple incidents" box should reference
any other ID number that the same tape is held on. I would like to automate
that cross reference.

The IncidentTapes table does that perfectly naturally. You can create
a Query joining Incidents, to IncidentTapes, to a second instance of
IncidentTapes (joining by the TapeID), to a second instance of
Incidents to find all incidents sharing a tape with an initial
incident.

My database is quite large, and I am proud of it, however, I have only
learned by doing, so I know I am still a newbe. I appreciate your time and
effort to help me out.

"quite large" in what sense? How many Tables? Forms? 10,000,000
records in the largest table is getting "quite large"...

John W. Vinson[MVP]