Table/Form advice please

[Black Knight]

This is a follow on from my earlier post 'Best way ....?'

The database was set up by somebody else and I'm trying to streamline it
with my limited knowledge.

There's a table called DCS Data with lots of fields.
Two of them I've described below.

There's a field called 'MASTERLOOP'
It's not unique

There's another field called 'LOOPTAG'
This is unique

The way the data is recorded is as follows (example only) ........ you will
see how the two fields are related

MASTERLOOP | LOOP TAG |

12-X-123 12-XA-123
12-X-123 12-XB-123
12-X-123 12-XC-123
19-P-345 19-PA-123
19-P-345 19-PB-123
99-T-789 99-TA-789
99-T-789 99-TB-789
99-T-789 99-TC-789

and so on .....

So what I'm saying is that there are duplicate records on the MASTERLOOP
field.

The 'MASTERLOOP' field is used by other tables - I'm unsure why yet - Its
not been set up by me.

I think it might be best to have the MASTERLOOP field in a table of its own
so as not to have duplicates in the DCS Data table.

How do I set up my tables and forms so that when I enter a new record in the
DCS Data table, it puts a new record in the MASTERLOOP field in another
related table.

Sorry if I'm not clear in describing what I want to do.

Thanks for reading.

 

[Jeff Boyce]

In a well-normalized relational database, the tables represent entities
(person, place, event) about which you store data (fields, columns, AKA
"attributes"), and between which you describe relationships (one Order
record can have one or more Order Detail records).

I'm not getting a very clear sense of the underlying (table) structure of
your data, so I'm having trouble imagining why you'd need to add a (?blank)
record in a second table when you created a new record in a first table.

--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Black Knight]

In a well-normalized relational database, the tables represent entities
(person, place, event) about which you store data (fields, columns, AKA
"attributes"), and between which you describe relationships (one Order
record can have one or more Order Detail records).

I'm not getting a very clear sense of the underlying (table) structure of
your data, so I'm having trouble imagining why you'd need to add a
(?blank)
record in a second table when you created a new record in a first table.

Not easy to explain unless I showed you really.

Here goes again..........

If I have two related tables, related by a field called MASTERLOOP, how do I
create a form that when I enter a new record, both fields in both tables get
updated at the same time.

One table is called DCS Data which is like a main information table. There
an another table called LOOP Data.

Both tables contain the field MASTERLOOP and are related by such.

If I add a record to the main DCS Table and consequently put information in
the MASTERLOOP field, I want the new data in the MASTERLOOP field to also go
in the LOOP Data table.

Phew !

Now can you see what I'm trying to achieve ?

What I can't get my head around is that if the two MASTERLOOP fields in the
two tables don't get updated at the same time then the MASTERLOOP fields
will no longer contain the same information. Surely I don''t have to do this
manually for each table ?

Thanks for reading.

 

[Ed Warren]

Your problem is in the use of the 'masterloop' field. It is not necessary
and is causing confusion.

You need to state clearly: Each DCS row can be related to (1, many) LOOP
rows and each LOOP rows can be related to (1, many) DCS rows.
The possible combinations are
1:1
1:Many
Many:Many (See below)

Your table DCS should have a primary key say DCSID which uniquely identifies
that row
Your table LOOP should have parimary key say LOOPID which uniquely identifes
that row.

If each DCS row can be related to 0 to many LOOP row but each LOOP row and
each LOOP row can be related to only one DCS row then you have a 1 to Many
relationship and need to add DCSID to the LOOP table structure and link on
that value (foreign key).

e.g.
DCSID(key) DCSDescription

1 DCS1
2 DCS2
3 DCS3

LOOPID(key) DCSID(foreign key) LoopDescription
1 1(dcsID) Loop1 +other stuff about Loop
2 1(dscid) Loop2 + other stuff about Loop
3 2(dcsID) Loop3 + other stuff about Loop

If each DCS row can be related to 0 to many LOOP rows but each LOOP row can
be related to 0 to many DCS rows you have a Many:Many realtionship and need
a "connector" table containing the DCSID and LOOPID fields. With one row for
each 'relationship'.

e.g.
DCSID DCSDescription

1 DCS1
2 DCS2
3 DCS3

LOOPID(key) LoopDescription
1 Loop1 +other stuff about Loop
2 Loop2 + other stuff about Loop
3 Loop3 + other stuff about Loop

DCS_LOOP

DCSID LOOPID (combined key DCSID and LOOPID)
1 2
1 2
2 2
2 1


If each DCS row can be related to 0 to 1 LOOP rows (infrequent case) then
you would use DCSID as the key for both tables and have a 1:1 relationship.

e.g.
DCSID(key) DCSDescription

1 DCS1
2 DCS2
3 DCS3

DCSID (key) LoopDescription
1(dcsID) Loop1 +other stuff about Loop
2(dcsID) Loop3 + other stuff about Loop

To answer your basic question:
Assume you have a 1:M relation (the most common)
You would build a form Based on the DCS table, it should be columnar the
properties set to show one record at a time.
You would build a second form based on the LOOP table, it should be
'tabular' and set to show continious records.
Then you add the LOOP form to the DCS form as a Subform linked on the DCSID
field.

Now when you add records to the LOOP table via the form access keeps track
of the DCSID field and adds it 'automatically' to the LOOP table as you add
LOOP records related to the DCS Record in the parent form.

IF your data is related Many to Many you have to build at least three forms.
Form1 based on the DCS table to maintain the DCS data
Form2 based on the LOOP table to maintain the LOOP data
Form 3 based on the DCS_LOOP table to maintain the relationships. (I would
use a drop downbox for the field I want to "lookup".

Then you could add Form3 to either Form1 or Form2 as appropriate as a
subform linked to the appropriate ID field from the parent table.

Now if your field MASTERLOOP has some importance and or meaning that must be
captured, all that is needed is to decide if it is an attribute of the DCS
or LOOP entity and put it there and it is available to any reports, forms
etc you need to build.

Hope this helps more than it hurts.

Ed Warren.

 

[Ed Warren]

You are a victum of poor relational database design and of someone using
"intelligent keys".
Responders are having problems getting their heads around the unique idea of
using 'keys' that are not either 'primary' or 'foreign' keys (primary --
unique to the row, foreign -- relates to another table's primary key.

Your database is using the key (number1-X(letter)-number2) to connect the
records !!! egads! and in a text field to boot.

Intelligent Keys: (see current thread:: Need validation rule to pevent
duplicating a customer number)

It would appear (and this is a real leap of faith) that each LoopTag can be
related to one and only one Masterloop and each MasterLoop can be related to
many loopTags ( MasterLoop 1 --> Many LoopTags).

My first thought is to make a new table with two fields (MassterLOOP, and
LoopTag) an set LOOPTag as the Primary Key (using a "make table query")
Then you could use the new table as a source to maintain your join. But we
really need to know more about your data before anyone could make a good
suggestion.

MASTERLOOP | LOOP TAG |

12-X-123 12-XA-123
12-X-123 12-XB-123
12-X-123 12-XC-123
19-P-345 19-PA-123
19-P-345 19-PB-123
99-T-789 99-TA-789
99-T-789 99-TB-789
99-T-789 99-TC-789


If possible could you provide us a little more information (the Primary keys
for the DCS table and Loop table, along with the sample you have already
provided). Maybe then someone can help you out of this data morass!

Ed Warren.