If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
duplicating info into multiple tables (one form)
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! |
#2
|
|||
|
|||
duplicating info into multiple tables (one form)
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. "Jeff Boyce" wrote: 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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Codel" wrote in message ... 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! |
#3
|
|||
|
|||
duplicating info into multiple tables (one form)
On Thu, 31 Jul 2008 17:32:01 -0700, Codel
wrote: 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 -- John W. Vinson/MVP |
#4
|
|||
|
|||
duplicating info into multiple tables (one form)
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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Codel" wrote in message ... 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! |
#5
|
|||
|
|||
duplicating info into multiple tables (one form)
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" wrote: On Thu, 31 Jul 2008 17:32:01 -0700, Codel wrote: 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 -- John W. Vinson/MVP |
#6
|
|||
|
|||
duplicating info into multiple tables (one form)
On Thu, 31 Jul 2008 19:29:00 -0700, Codel
wrote: 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. -- John W. Vinson/MVP |
#7
|
|||
|
|||
duplicating info into multiple tables (one form)
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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Codel" wrote in message ... 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. "Jeff Boyce" wrote: 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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Codel" wrote in message ... 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! |
#8
|
|||
|
|||
duplicating info into multiple tables (one form)
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 On Jul 31, 5:32*pm, Codel wrote: 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! * |
Thread Tools | |
Display Modes | |
|
|