Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
melanieb
Starting Member
5 Posts |
 Posted - 2005-06-06 : 13:50:49
|
| I have a main tbl with the primary key as AssociateID (autonumber) this has my employee demographics. There are 15 additional tbls that have title information that will be associated with the employee:Tbl Main AssociateID Fname LnameTblDivPres DivPresID AssocID DivisionPres ReadinessTblEVP EVPID EVPFoodOps EVPFoodOpsReadinessEach employee will most likely have an association out of each one of these tables. I need to do a query that will pull up the employee demographic plus any information about them from the job title tables. I know the problem is setting up the primary/foreign keys to establish the relationships. I want the auto number from the main table to match up with the correct record from the related "sub" tables. Thanks |
|
|
jhermiz
3564 Posts |
Posted - 2005-06-06 : 14:32:35
|
[code]SELECT t.Fname, t.Lname, t2.DivisionPres, t2.Readiness FROM [Tbl Main] t INNER JOIN TblDivPres t2 ON t2.AssocID=t.AssociateID[/code]Just a matter of joining your tables by the proper ID's.Also the SQL part of this shouldn't be the problem, the GUI is what will make this happen. How is your current form setup?If you want the "auto number from the main table to match up with the correct record from the related sub tables" then you can't count on any magic here. You will need to build sub forms within your main form to populate that foreign key. Other method is to simply snatch the pk and insert it into your other tables as a foreign key using an INSERT statement.Make sense ?
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
 |
|
|
melanieb
Starting Member
5 Posts |
Posted - 2005-06-06 : 14:40:00
|
| Actually yes this sounds like what I'm looking for. I wanted to create a form which will have the employee information on it and then use command buttons to open up the subforms which will have the title (y/n) and readiness (drop down). Has been 9 months since I've created something like this and the pk sql sounds familiar. Would you elaborate for me? |
|
|
|
jhermiz
3564 Posts |
Posted - 2005-06-06 : 14:53:39
|
Well you could do it one of two ways...If you have a sub form within your main form, access allows you to tell it what you want to link by. In fact if i remember correctly (been some time) as soon as you drop a subform on your main form you will see the linkage of primary key and foreign key. So when you enter a "main" record and then the "sub" records, each sub record will receive the main records pk as a fk. That is the easiest access method to do this. The other method is to when you create a "main" record access assigns it that autonumber (as long as that is what you are using) value. Once you have this autonumber you simply need to take this number and insert it in your child (sub) tables using an INSERT statement.INSERT YourSubTable(ID, Field2...)SELECT ID, Field2... FROM YourMainTABLE WHERE....or INSERT INTO YourSubTable(ID, Field2...) VALUES(SomeIDParam, SomeFieldParam)
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
|
|
|
melanieb
Starting Member
5 Posts |
Posted - 2005-06-06 : 18:18:48
|
| Everything worked fine until I added my 3rd "sub" table. I get a syntax error (missing operator). Not sure what to put in there. I will have 12 more tables to with PK to link up to the PK on the main tbl.FROM (TblMain INNER JOIN TblEVP ON TblMain.AssociateID = TblEVP.EVPID)INNER JOIN TblDivPres ON TblMain.AssociateID = TblDivPres.DivPresID INNER JOIN Tblfinance ON TblMain.AssociateID = Tblfinance.financeID |
|
|
|
|
|
|
Relationships