I have a problem with two cross referenced databases

[drolfe]

I have two tables, modified by a single form. I have a main database
that holds the title information for the form, in that database i have
10 keys that point to the other database to pull that record and
present it on the form. My problem is that when I add a new record to
the first database, i need it to first populate the next 10 records in
my other database, then also update my main database with those record
numbers in the keys

Here is a mockup of the two tables

Main Table:

<pre>
UserID | File1 | File2 | File3 | File4
----------------------------------------------------
1 | 1 | 2 | 3 | 4
2 | 5 | 6 | 7 | 8

File Table:

FileID | UserID | Data.....
---------------------------------------
1 | 1 | blah blah blah
2 | 1 | ........
............
5 | 2 | etc...
6 | 2 | etc...

</pre>
now my problem again is when I add a new record to the main table DB, i
need it to cross reference both tables updating them simultaneously

any ideas??

 

[John Vinson]

I have two tables, modified by a single form. I have a main database
that holds the title information for the form, in that database i have
10 keys that point to the other database to pull that record and
present it on the form. My problem is that when I add a new record to
the first database, i need it to first populate the next 10 records in
my other database, then also update my main database with those record
numbers in the keys

Here is a mockup of the two tables

Main Table:

<pre>
UserID | File1 | File2 | File3 | File4
----------------------------------------------------
1 | 1 | 2 | 3 | 4
2 | 5 | 6 | 7 | 8

File Table:

FileID | UserID | Data.....
---------------------------------------
1 | 1 | blah blah blah
2 | 1 | ........
............
5 | 2 | etc...
6 | 2 | etc...

</pre>
now my problem again is when I add a new record to the main table DB, i
need it to cross reference both tables updating them simultaneously

It's almost NEVER either necessary or appropriate to put empty
"placeholder" records into a related table. Secondly, if you have
fields named File1, File2, ... , File10 then your table structure *is
wrong*. If you have a many to many relationship between files and
users, your second table is the proper structure; your main table is
not properly normalized. Why do you feel that you need to have the
data stored redundantly in this way?

John W. Vinson[MVP]

 

[drolfe]

Hi, John and thank you for your reply.

Let me get into more detail with this so maybe you'll see what I am
trying to do

I have one table: auditDB

AuditDB inclueds
CollectorID (Primary Key/Auto Number)
CollectorName
CollectorDate
CollectorSupervisor
Account1ID
Account2ID
Account3ID
.....
Account10ID

and Account DB

AccountDB:
AccountID (Primary Key/Auto Number)
AccountReference
AccountAA
AccountAU

This is what I am trying to do:
When I add a record to AuditDB, i also must add 10 records to the
AccountDB and then take the 10 keys that i just added to AccountDB and
add the AccountID to Account1ID, Account2ID, Account3ID etc..

The form that i have looks like this

CollectorNAME CollectorSUPER CollectorDATE [add button]
[Main Form]
[Subform for Account1]
[Subform for Account2]
[....]
[Subform for Account10]

All the data on all the subforms will be completed at the time they
were added.

But my problem is, when I add a new record now, It only adds a new
record to the AuditDB. As a work around I added Fields on the side of
the form for Account1ID....Account10ID so I can manually add the
records into the table.

I have the table working properly, I manually entered data how I want
to to be presented, but the onhly problem I am having now is when I add
a new record.​

 

[John Vinson]

Hi, John and thank you for your reply.

Let me get into more detail with this so maybe you'll see what I am
trying to do

Let's take a look..

I have one table: auditDB

AuditDB inclueds
CollectorID (Primary Key/Auto Number)
CollectorName
CollectorDate
CollectorSupervisor
Account1ID
Account2ID
Account3ID
....
Account10ID

This structure IS SIMPLY WRONG.

If you have a many (collectors) to many (accounts) relationship, you
need *THREE* tables:

AuditDB
CollectorID <Primary Key>
CollectorName <<< this maybe should be in a Collectors table
CollectorDate
CollectorSupervisor

AuditDetails
AuditDetailID <autonumber primary key>
CollectorID <foreign key to AuditDB>
AccountID <foreign key to Accounts>

"Fielda are expensive, records are cheap". Someday you'll have an
audit with *eleven* accounts... when you do, you're dead in the water!
And you're setting yourself up for all sorts of problems deciding
WHICH AccountID to link to or to search.

and Account DB

AccountDB:
AccountID (Primary Key/Auto Number)
AccountReference
AccountAA
AccountAU

What are AccountAA and AccountAU?

This is what I am trying to do:
When I add a record to AuditDB, i also must add 10 records to the
AccountDB and then take the 10 keys that i just added to AccountDB and
add the AccountID to Account1ID, Account2ID, Account3ID etc..

No.

You do NOT need to add them in this way.

Create a Form based on the normalized Audits table, and use a Subform
based on the AuditDetails table. Enter the AccountID's on the subform.

The form that i have looks like this

CollectorNAME CollectorSUPER CollectorDATE [add button]
[Main Form]
[Subform for Account1]
[Subform for Account2]
[....]
[Subform for Account10]

All the data on all the subforms will be completed at the time they
were added.
But my problem is, when I add a new record now, It only adds a new
record to the AuditDB. As a work around I added Fields on the side of
the form for Account1ID....Account10ID so I can manually add the
records into the table.

I have the table working properly, I manually entered data how I want
to to be presented, but the onhly problem I am having now is when I add
a new record.​

No. You do NOT have the table working properly. It's non-normalized
AND IT IS WRONG. Sorry, but you're just digging yourself a hole with
this incorrect table design!

Fix the normalization of your tables, and you'll find that it's much
easier than you're now making it!


John W. Vinson[MVP]​