duplicating info into multiple tables (one form)

[Codel]

I imagine the answer is here somewhere, but gosh--what to query???

I have an existing database in use by a specific department to track errors
made by individual employees. My department is tasked with entering the
employee info into this db, namely their employee name, id#, phone extension,
etc. My department also uses the error tracking data entered by the other
department. However, we have additional fields we want to track and enter at
the same time we set up their user info without using the existing table.
So, the long and short of it:

I have database X with table A containing fields: name, id, SSN, phone#
I want a form that will enter all of the above data into dbX table A whilst
also entering the same name, id, SSN, and phone# into database Y table B in
addition to fields: error, birthdate, etc. I have database x linked into y
without a problem. I just need to be able to query my records across both
tables with a unique id and enter the data at the same time. Make sense???
PROBABLY NOT. That's why I don't know where to search!

 

[Codel]

Correct, it would be much easier. In fact, I would like nothing more to just
add my fields to the existing table and be done with it. Unfortunately, I'm
dealing with A: A turf war and B: a security freak that doesn't trust our
database being 'back-linked' with theirs. So, unfortunately, I'm stuck
either entering the data once into their table and then re-entering it into
ours or coming up with a solution to 'double record' it.

And, truthfully, I don't need all the fields duplicated. I can just link
out most of the data once it's stored in their table. I only need one unique
field (like name) recorded in both at the same time so I can be sure they are
linked properly. From that I can add the tables for my specific data. right?

I can get by with a form that enters into dbX TblA: name, id, phone, SSN
and enters the same name value into dbY TblB with the different fields. I
could then pull queries out of TblB with the info I need. I just need one
matching field to link them together.

 

[John W. Vinson/MVP]

I imagine the answer is here somewhere, but gosh--what to query???

I have an existing database in use by a specific department to track errors
made by individual employees. My department is tasked with entering the
employee info into this db, namely their employee name, id#, phone extension,
etc. My department also uses the error tracking data entered by the other
department. However, we have additional fields we want to track and enter at
the same time we set up their user info without using the existing table.
So, the long and short of it:

I have database X with table A containing fields: name, id, SSN, phone#
I want a form that will enter all of the above data into dbX table A whilst
also entering the same name, id, SSN, and phone# into database Y table B in
addition to fields: error, birthdate, etc. I have database x linked into y
without a problem. I just need to be able to query my records across both
tables with a unique id and enter the data at the same time. Make sense???
PROBABLY NOT. That's why I don't know where to search!

No, it doesn't make sense.

Relational databases use the "Grandmother's Pantry Principle" - "A
place - ONE place! - for everything, everything in its place".

You should have a table - *one* table, stored in one database and
linked from the other, or stored in a shared backend database and
linked from both - with employee identification and biographical data.
It should certainly NOT have any fields for "error" (unless employees
are allowed one and only one error and then are immediately fired).

You may be trying to work in table datasheets and use only one table
for your application. Don't. That misses the point of how Access
works; you'll want to use a Form probably with subforms, and thereby
view multiple tables at the same time, without ever opening a table
datasheet.

I'd expect an error tracking application to have at least three
tables:

Employees
EmployeeID <perhaps the SSN If that's what you illegally use>
LastName
FirstName
DOB
<other personal biographical info>

Errors
ErrorID <primary key>
ErrorDescription <e.g. "Constructed non-normalized database"> <g>
<other info about the error as a thing in itself, maybe a severity
rating or consequences>

EmployeeErrors
EmployeeID <link to Employees>
ErrorID <link to Errors>
ErrorDate <when did it happen>
Comments
<other fields pertaining to this particular employee's commission of
this particular error>

 

[Jeff Boyce]

It sounds like you are trying to use two tables to hold (largely) identical
information. If so, why?

You could use a single table in one database and "link" to it from both of
your applications (i.e., front-ends).

If you included all fields in one front-end's form (form A, dbX), but only
some of the fields in the other front-end's form (form B, dbY), you could
both see the same group of employees but only see the information you each
were supposed to.

Or maybe I'm missing something...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


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

 

[Codel]

You are correct, and that is the basic structure of the existing db. I,
however, have a separate db that stores slightly different data, in addition
to some of the same data from that table which we also use. We are required
to enter certain 'biographical' info as you stated into each of the db. I
want a form that allows us to enter it once but fill both tables.

 

[John W. Vinson/MVP]

You are correct, and that is the basic structure of the existing db. I,
however, have a separate db that stores slightly different data, in addition
to some of the same data from that table which we also use. We are required
to enter certain 'biographical' info as you stated into each of the db. I
want a form that allows us to enter it once but fill both tables.

Then you'll need to link the tables (by some unique ID which simply
must exist in both tables, otherwise there's no way to do so) and run
an Update query to create the redundant field values.

 

[Jeff Boyce]

You may be able to use the INSERT INTO SQL statement in a procedure. ...
twice.

If you create unbound forms, you could add the code to a <Save> command
button and have the code handle all the dreary housekeeping details that
Access would normally handle if you used a bound form.


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


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

 

[aaron.kempf]

well.. if you were only using storedProcedures then it would be quite
easy to do this

create procedure spDelEmployeeChildren
(
@employeeID int
)
as

Delete From EmployeeImages Where EmployeeID = @EmployeeID
Delete From EmployeeNotes Where EmployeeID = @EmployeeID
Delete From EMployeeCertifications Where EmployeeID = @EmployeeID


Then-- this whole SQL Statement-- you could fire all of those
statements just by running this SQL Statement

spDelEmployeeChildren 12

Sounds to me like moving to stored procedures really might make this a
lot easier for you