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
|
|||
|
|||
Linking Two Tables
I have two tables in Access 2003, Master (Parent) and Hours (Child).
Currently they share a One-To-One relationship. I need a record to be created in the Hours table every time there is a record created in the Master Table, but when users don't enter a vaule in the Hours table, it won't generate a record? When I run a query that includes fields from both tables, it only accounts for the records in the Hours table. For example, if I have 886 records in the Master Table, and 86 of those records were not filled out in the Hours table, well then I only get 800 records back in my query. So if I want to know how many people have entered thier hours, sure 800 is correct, but I want to know out of 886 only 800 have entered their hours. I fear that I should not have created two seperate tables, and that the Hours table and the Master table should be one big table. |
#2
|
|||
|
|||
Linking Two Tables
On Wed, 28 Oct 2009 16:35:01 -0700, Gabe
wrote: I have two tables in Access 2003, Master (Parent) and Hours (Child). Currently they share a One-To-One relationship. I need a record to be created in the Hours table every time there is a record created in the Master Table, but when users don't enter a vaule in the Hours table, it won't generate a record? One to one relationships are VERY RARE. Why do you feel that you need one? What is in [Hours]? Does each Master record have one, and only one, forever, value of this field? When I run a query that includes fields from both tables, it only accounts for the records in the Hours table. For example, if I have 886 records in the Master Table, and 86 of those records were not filled out in the Hours table, well then I only get 800 records back in my query. So if I want to know how many people have entered thier hours, sure 800 is correct, but I want to know out of 886 only 800 have entered their hours. I fear that I should not have created two seperate tables, and that the Hours table and the Master table should be one big table. If each Master has only one Hours, then you're correct; but you can just add an Hours field (or fields) to the Master table, join the two tables, and run an Update query. The field will be NULL if there is no corresponding record in Hours. That said... you certainly do NOT need to create a dummy "placeholder" record in Hours. instead, change the Join type in your query. Click on the join line in query design view and select Option 2: "Show all records in Master and matching records in Hours". My guess is that you in fact should have a one to MANY (not one to one) relationship; if this is a payroll or similar app, each employee will have many work-periods with hours in each of them. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Linking Two Tables
Thanks John, you are correct, I joined the two tables and the process wasn't
as bad as I thought it would be. ~Gabe "John W. Vinson" wrote: On Wed, 28 Oct 2009 16:35:01 -0700, Gabe wrote: I have two tables in Access 2003, Master (Parent) and Hours (Child). Currently they share a One-To-One relationship. I need a record to be created in the Hours table every time there is a record created in the Master Table, but when users don't enter a vaule in the Hours table, it won't generate a record? One to one relationships are VERY RARE. Why do you feel that you need one? What is in [Hours]? Does each Master record have one, and only one, forever, value of this field? When I run a query that includes fields from both tables, it only accounts for the records in the Hours table. For example, if I have 886 records in the Master Table, and 86 of those records were not filled out in the Hours table, well then I only get 800 records back in my query. So if I want to know how many people have entered thier hours, sure 800 is correct, but I want to know out of 886 only 800 have entered their hours. I fear that I should not have created two seperate tables, and that the Hours table and the Master table should be one big table. If each Master has only one Hours, then you're correct; but you can just add an Hours field (or fields) to the Master table, join the two tables, and run an Update query. The field will be NULL if there is no corresponding record in Hours. That said... you certainly do NOT need to create a dummy "placeholder" record in Hours. instead, change the Join type in your query. Click on the join line in query design view and select Option 2: "Show all records in Master and matching records in Hours". My guess is that you in fact should have a one to MANY (not one to one) relationship; if this is a payroll or similar app, each employee will have many work-periods with hours in each of them. -- John W. Vinson [MVP] . |
Thread Tools | |
Display Modes | |
|
|