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
|
|||
|
|||
table joins
I have 4 tables (Quality, Safety, Process, Maintenance) each with the primary
key set as "Report#" (no dupliactes allowed). I would like to relate these 4 tables to one table (Corrective Actions) that would store common information about the 4 tables by Report#. I am able to set up the one-to-many-relationship and enforce referential integrity. I have created a Corrective Action subform in each of the 4 forms but when I go to enter data it tells me "I cannot add or change a record because a related record is required in table "Safety". I had 4 tables in one large table (7,500 records) but was taking to long to filter data. Can I relate 4 tables to one? If so, what am I missing? Intermediate user of Acess 2003 SP1. |
#2
|
|||
|
|||
table joins
You've described "how" you are trying to do something (your table structure,
your form design, ...). Would you please describe "what" you want to accomplish, and a bit more about the data itself (an example would help)? In describing "what", turn off your computer and use terms an 80 year old grandmother might relate to... -- Regards Jeff Boyce Office/Access MVP "tlynn" wrote in message ... I have 4 tables (Quality, Safety, Process, Maintenance) each with the primary key set as "Report#" (no dupliactes allowed). I would like to relate these 4 tables to one table (Corrective Actions) that would store common information about the 4 tables by Report#. I am able to set up the one-to-many-relationship and enforce referential integrity. I have created a Corrective Action subform in each of the 4 forms but when I go to enter data it tells me "I cannot add or change a record because a related record is required in table "Safety". I had 4 tables in one large table (7,500 records) but was taking to long to filter data. Can I relate 4 tables to one? If so, what am I missing? Intermediate user of Acess 2003 SP1. |
#3
|
|||
|
|||
table joins
Hi Jeff,
I want the one Corrective Action table to act as the common table to store corrective action data that relates to each of the 4 tables by Report# or some common ID. I am trying to avoid one BIG table which would consist of the Quality, Safety, Maintenance, Process data and one joined smaller Corrective Action table. Corrective Action Table: Report# Team Members Root Cause Corrective Action Preventative Action Quality, Safety, Maintenance, Process Tables: Report# Part# Description of Problem Etc. (many other fileds) I Hope this helps Thank You, Tim. "Jeff Boyce" wrote: You've described "how" you are trying to do something (your table structure, your form design, ...). Would you please describe "what" you want to accomplish, and a bit more about the data itself (an example would help)? In describing "what", turn off your computer and use terms an 80 year old grandmother might relate to... -- Regards Jeff Boyce Office/Access MVP "tlynn" wrote in message ... I have 4 tables (Quality, Safety, Process, Maintenance) each with the primary key set as "Report#" (no dupliactes allowed). I would like to relate these 4 tables to one table (Corrective Actions) that would store common information about the 4 tables by Report#. I am able to set up the one-to-many-relationship and enforce referential integrity. I have created a Corrective Action subform in each of the 4 forms but when I go to enter data it tells me "I cannot add or change a record because a related record is required in table "Safety". I had 4 tables in one large table (7,500 records) but was taking to long to filter data. Can I relate 4 tables to one? If so, what am I missing? Intermediate user of Acess 2003 SP1. |
#4
|
|||
|
|||
table joins
Tim
An 80 year old grandmother doesn't know from "tables"... you're still describing what you want to accomplish (a business need) in terms of "how" (table structure, etc.). And on what basis have you decided that a "BIG table" is undesirable? Is this a personal issue, or are there relational database design considerations? And define "BIG" -- are you concerned with how many rows, how many bytes, how many fields, ...? If it doesn't infringe on proprietary information, please provide an example .... not of the table structure you are already using, but of the data you wish to do something with (hint, hint, hint!). -- Regards Jeff Boyce Office/Access MVP "tlynn" wrote in message ... Hi Jeff, I want the one Corrective Action table to act as the common table to store corrective action data that relates to each of the 4 tables by Report# or some common ID. I am trying to avoid one BIG table which would consist of the Quality, Safety, Maintenance, Process data and one joined smaller Corrective Action table. Corrective Action Table: Report# Team Members Root Cause Corrective Action Preventative Action Quality, Safety, Maintenance, Process Tables: Report# Part# Description of Problem Etc. (many other fileds) I Hope this helps Thank You, Tim. "Jeff Boyce" wrote: You've described "how" you are trying to do something (your table structure, your form design, ...). Would you please describe "what" you want to accomplish, and a bit more about the data itself (an example would help)? In describing "what", turn off your computer and use terms an 80 year old grandmother might relate to... -- Regards Jeff Boyce Office/Access MVP "tlynn" wrote in message ... I have 4 tables (Quality, Safety, Process, Maintenance) each with the primary key set as "Report#" (no dupliactes allowed). I would like to relate these 4 tables to one table (Corrective Actions) that would store common information about the 4 tables by Report#. I am able to set up the one-to-many-relationship and enforce referential integrity. I have created a Corrective Action subform in each of the 4 forms but when I go to enter data it tells me "I cannot add or change a record because a related record is required in table "Safety". I had 4 tables in one large table (7,500 records) but was taking to long to filter data. Can I relate 4 tables to one? If so, what am I missing? Intermediate user of Acess 2003 SP1. |
#5
|
|||
|
|||
table joins
Jeff,
Sorry, I am trying my best to describe my situation with the limited knowledge I have with Access. I originally started out with four seperate databases (Quality Safety, Process and Maintenance), each seperate projects/files. These databases were used to enter data that related to part quality problems, equipment failues, accidents, etc.). Just recently, I wanted to create a fifth database (common to the other four) to enter data that described what we did to correct the problem. Only specific records in the other four databases would required a record in the corrective action database. I made these databases common in structure but still maintaned them as four separate databases, and then placed them into one project/file. I tried to join the four databases to the corrective action database, with a One-to-Many join enforcing R/I, but then could not enter data without the error. I proceded to combine the four databases into one database (Database1) and joined this one common database to the corrective action database (Database2). Database1 (Quality, Safety, Process Maintanance) data currenly has 7800 records (rows of data) with about 25 fileds per row. Data is entered into this database at a rate of 10 records per workday. Joined to this database is the corrective action database (Database2). Database1 - Quality, Safety, Process Maintenance Data: Type (Quality, Safety, Process, Manitenance) PART# (1234) Report# (5678) Description (1.500 length u/s to 1.200) Etc Data (about 15 more data/number/date fields). Database2 - Corrective Action Data: Report# (1234) Due Date (12/16/05) Car Team (Names) Root Cause (255 characters) Corrective Action (255 characters) Preventative Action (255 characters) Date Closed (12/16/05) I am seeking help because my current configuration with Database1 and Database2 seems to have slowed down my filter/query time (5-7 seconds per search/query). So I was again trying to split the table back into four sperate tables with a fifth joined table for corrective action data. I have no problem using my current configuration (Database1 and Database2) except for the slow filter/query times. I was hoping that the four separate databases linked to the fifth common database would resolve my search time. Thanks, Tim "Jeff Boyce" wrote: Tim An 80 year old grandmother doesn't know from "tables"... you're still describing what you want to accomplish (a business need) in terms of "how" (table structure, etc.). And on what basis have you decided that a "BIG table" is undesirable? Is this a personal issue, or are there relational database design considerations? And define "BIG" -- are you concerned with how many rows, how many bytes, how many fields, ...? If it doesn't infringe on proprietary information, please provide an example .... not of the table structure you are already using, but of the data you wish to do something with (hint, hint, hint!). -- Regards Jeff Boyce Office/Access MVP "tlynn" wrote in message ... Hi Jeff, I want the one Corrective Action table to act as the common table to store corrective action data that relates to each of the 4 tables by Report# or some common ID. I am trying to avoid one BIG table which would consist of the Quality, Safety, Maintenance, Process data and one joined smaller Corrective Action table. Corrective Action Table: Report# Team Members Root Cause Corrective Action Preventative Action Quality, Safety, Maintenance, Process Tables: Report# Part# Description of Problem Etc. (many other fileds) I Hope this helps Thank You, Tim. "Jeff Boyce" wrote: You've described "how" you are trying to do something (your table structure, your form design, ...). Would you please describe "what" you want to accomplish, and a bit more about the data itself (an example would help)? In describing "what", turn off your computer and use terms an 80 year old grandmother might relate to... -- Regards Jeff Boyce Office/Access MVP "tlynn" wrote in message ... I have 4 tables (Quality, Safety, Process, Maintenance) each with the primary key set as "Report#" (no dupliactes allowed). I would like to relate these 4 tables to one table (Corrective Actions) that would store common information about the 4 tables by Report#. I am able to set up the one-to-many-relationship and enforce referential integrity. I have created a Corrective Action subform in each of the 4 forms but when I go to enter data it tells me "I cannot add or change a record because a related record is required in table "Safety". I had 4 tables in one large table (7,500 records) but was taking to long to filter data. Can I relate 4 tables to one? If so, what am I missing? Intermediate user of Acess 2003 SP1. |
#6
|
|||
|
|||
table joins
Tim
Your knowledge of/experience with Access is not the issue ... I assume that's one reason you're posting in the newsgroups. What I've been trying to get you to do is to step (totally) away from the way you've approached your situation and explain it in non-Access, non-database, non-technical terminology. It may be that what you want to accomplish can be done a different way than you've used ... if only it were clearer what it is you are trying to accomplish! Let me try this... I'll paraphrase what I suspect you are trying to do. You respond with corrections where I don't understand. Once there's a clearer picture, perhaps other folks will chime in with suggestions... You are recording information about incidents (you are calling them other things, and categorizing them). Some incidents relate to Quality issues, some to Safety issues, some to Process issues and some to Maintenance issues. I'm not clear if what you need to know about Quality issue incidents is appreciably different that what you need to know about Safety (or ...) issue incidents. However, if most of the information about the incident has the same characteristics (e.g., ReportedBy, DateReported, ...) and it is only the "category" (Quality, Safety, ...) and a description that are different, you could use a single table (oops! sorry, I jumped ahead). You also seem to want to record information about how the issues get handled. Based on what you've said so far, it sounds like you would keep largely similar information about "Corrective Action", regardless of the category of incident. Perhaps you are keeping something like: DateResolved, ActionTaken, ResolvedBy, ... (oops again!). Does this accurately reflect your situation? You have incidents, with characteristics, and you have resolutions ("Corrective Actions"), with characteristics. You want to connect one/more Corrective Action Taken to the appropriate incident. Let me know where I read too much into your descriptions, and we'll see what threads develop. Good luck Jeff Boyce Office/Access MVP "tlynn" wrote in message ... Jeff, Sorry, I am trying my best to describe my situation with the limited knowledge I have with Access. I originally started out with four seperate databases (Quality Safety, Process and Maintenance), each seperate projects/files. These databases were used to enter data that related to part quality problems, equipment failues, accidents, etc.). Just recently, I wanted to create a fifth database (common to the other four) to enter data that described what we did to correct the problem. Only specific records in the other four databases would required a record in the corrective action database. I made these databases common in structure but still maintaned them as four separate databases, and then placed them into one project/file. I tried to join the four databases to the corrective action database, with a One-to-Many join enforcing R/I, but then could not enter data without the error. I proceded to combine the four databases into one database (Database1) and joined this one common database to the corrective action database (Database2). Database1 (Quality, Safety, Process Maintanance) data currenly has 7800 records (rows of data) with about 25 fileds per row. Data is entered into this database at a rate of 10 records per workday. Joined to this database is the corrective action database (Database2). Database1 - Quality, Safety, Process Maintenance Data: Type (Quality, Safety, Process, Manitenance) PART# (1234) Report# (5678) Description (1.500 length u/s to 1.200) Etc Data (about 15 more data/number/date fields). Database2 - Corrective Action Data: Report# (1234) Due Date (12/16/05) Car Team (Names) Root Cause (255 characters) Corrective Action (255 characters) Preventative Action (255 characters) Date Closed (12/16/05) I am seeking help because my current configuration with Database1 and Database2 seems to have slowed down my filter/query time (5-7 seconds per search/query). So I was again trying to split the table back into four sperate tables with a fifth joined table for corrective action data. I have no problem using my current configuration (Database1 and Database2) except for the slow filter/query times. I was hoping that the four separate databases linked to the fifth common database would resolve my search time. Thanks, Tim "Jeff Boyce" wrote: Tim An 80 year old grandmother doesn't know from "tables"... you're still describing what you want to accomplish (a business need) in terms of "how" (table structure, etc.). And on what basis have you decided that a "BIG table" is undesirable? Is this a personal issue, or are there relational database design considerations? And define "BIG" -- are you concerned with how many rows, how many bytes, how many fields, ...? If it doesn't infringe on proprietary information, please provide an example .... not of the table structure you are already using, but of the data you wish to do something with (hint, hint, hint!). -- Regards Jeff Boyce Office/Access MVP "tlynn" wrote in message ... Hi Jeff, I want the one Corrective Action table to act as the common table to store corrective action data that relates to each of the 4 tables by Report# or some common ID. I am trying to avoid one BIG table which would consist of the Quality, Safety, Maintenance, Process data and one joined smaller Corrective Action table. Corrective Action Table: Report# Team Members Root Cause Corrective Action Preventative Action Quality, Safety, Maintenance, Process Tables: Report# Part# Description of Problem Etc. (many other fileds) I Hope this helps Thank You, Tim. "Jeff Boyce" wrote: You've described "how" you are trying to do something (your table structure, your form design, ...). Would you please describe "what" you want to accomplish, and a bit more about the data itself (an example would help)? In describing "what", turn off your computer and use terms an 80 year old grandmother might relate to... -- Regards Jeff Boyce Office/Access MVP "tlynn" wrote in message ... I have 4 tables (Quality, Safety, Process, Maintenance) each with the primary key set as "Report#" (no dupliactes allowed). I would like to relate these 4 tables to one table (Corrective Actions) that would store common information about the 4 tables by Report#. I am able to set up the one-to-many-relationship and enforce referential integrity. I have created a Corrective Action subform in each of the 4 forms but when I go to enter data it tells me "I cannot add or change a record because a related record is required in table "Safety". I had 4 tables in one large table (7,500 records) but was taking to long to filter data. Can I relate 4 tables to one? If so, what am I missing? Intermediate user of Acess 2003 SP1. |
#7
|
|||
|
|||
table joins
Jeff,
You are absolutely correct in your description. No corections needed. "Jeff Boyce" wrote: Tim Your knowledge of/experience with Access is not the issue ... I assume that's one reason you're posting in the newsgroups. What I've been trying to get you to do is to step (totally) away from the way you've approached your situation and explain it in non-Access, non-database, non-technical terminology. It may be that what you want to accomplish can be done a different way than you've used ... if only it were clearer what it is you are trying to accomplish! Let me try this... I'll paraphrase what I suspect you are trying to do. You respond with corrections where I don't understand. Once there's a clearer picture, perhaps other folks will chime in with suggestions... You are recording information about incidents (you are calling them other things, and categorizing them). Some incidents relate to Quality issues, some to Safety issues, some to Process issues and some to Maintenance issues. I'm not clear if what you need to know about Quality issue incidents is appreciably different that what you need to know about Safety (or ...) issue incidents. However, if most of the information about the incident has the same characteristics (e.g., ReportedBy, DateReported, ...) and it is only the "category" (Quality, Safety, ...) and a description that are different, you could use a single table (oops! sorry, I jumped ahead). You also seem to want to record information about how the issues get handled. Based on what you've said so far, it sounds like you would keep largely similar information about "Corrective Action", regardless of the category of incident. Perhaps you are keeping something like: DateResolved, ActionTaken, ResolvedBy, ... (oops again!). Does this accurately reflect your situation? You have incidents, with characteristics, and you have resolutions ("Corrective Actions"), with characteristics. You want to connect one/more Corrective Action Taken to the appropriate incident. Let me know where I read too much into your descriptions, and we'll see what threads develop. Good luck Jeff Boyce Office/Access MVP "tlynn" wrote in message ... Jeff, Sorry, I am trying my best to describe my situation with the limited knowledge I have with Access. I originally started out with four seperate databases (Quality Safety, Process and Maintenance), each seperate projects/files. These databases were used to enter data that related to part quality problems, equipment failues, accidents, etc.). Just recently, I wanted to create a fifth database (common to the other four) to enter data that described what we did to correct the problem. Only specific records in the other four databases would required a record in the corrective action database. I made these databases common in structure but still maintaned them as four separate databases, and then placed them into one project/file. I tried to join the four databases to the corrective action database, with a One-to-Many join enforcing R/I, but then could not enter data without the error. I proceded to combine the four databases into one database (Database1) and joined this one common database to the corrective action database (Database2). Database1 (Quality, Safety, Process Maintanance) data currenly has 7800 records (rows of data) with about 25 fileds per row. Data is entered into this database at a rate of 10 records per workday. Joined to this database is the corrective action database (Database2). Database1 - Quality, Safety, Process Maintenance Data: Type (Quality, Safety, Process, Manitenance) PART# (1234) Report# (5678) Description (1.500 length u/s to 1.200) Etc Data (about 15 more data/number/date fields). Database2 - Corrective Action Data: Report# (1234) Due Date (12/16/05) Car Team (Names) Root Cause (255 characters) Corrective Action (255 characters) Preventative Action (255 characters) Date Closed (12/16/05) I am seeking help because my current configuration with Database1 and Database2 seems to have slowed down my filter/query time (5-7 seconds per search/query). So I was again trying to split the table back into four sperate tables with a fifth joined table for corrective action data. I have no problem using my current configuration (Database1 and Database2) except for the slow filter/query times. I was hoping that the four separate databases linked to the fifth common database would resolve my search time. Thanks, Tim "Jeff Boyce" wrote: Tim An 80 year old grandmother doesn't know from "tables"... you're still describing what you want to accomplish (a business need) in terms of "how" (table structure, etc.). And on what basis have you decided that a "BIG table" is undesirable? Is this a personal issue, or are there relational database design considerations? And define "BIG" -- are you concerned with how many rows, how many bytes, how many fields, ...? If it doesn't infringe on proprietary information, please provide an example .... not of the table structure you are already using, but of the data you wish to do something with (hint, hint, hint!). -- Regards Jeff Boyce Office/Access MVP "tlynn" wrote in message ... Hi Jeff, I want the one Corrective Action table to act as the common table to store corrective action data that relates to each of the 4 tables by Report# or some common ID. I am trying to avoid one BIG table which would consist of the Quality, Safety, Maintenance, Process data and one joined smaller Corrective Action table. Corrective Action Table: Report# Team Members Root Cause Corrective Action Preventative Action Quality, Safety, Maintenance, Process Tables: Report# Part# Description of Problem Etc. (many other fileds) I Hope this helps Thank You, Tim. "Jeff Boyce" wrote: You've described "how" you are trying to do something (your table structure, your form design, ...). Would you please describe "what" you want to accomplish, and a bit more about the data itself (an example would help)? In describing "what", turn off your computer and use terms an 80 year old grandmother might relate to... -- Regards Jeff Boyce Office/Access MVP "tlynn" wrote in message ... I have 4 tables (Quality, Safety, Process, Maintenance) each with the primary key set as "Report#" (no dupliactes allowed). I would like to relate these 4 tables to one table (Corrective Actions) that would store common information about the 4 tables by Report#. I am able to set up the one-to-many-relationship and enforce referential integrity. I have created a Corrective Action subform in each of the 4 forms but when I go to enter data it tells me "I cannot add or change a record because a related record is required in table "Safety". I had 4 tables in one large table (7,500 records) but was taking to long to filter data. Can I relate 4 tables to one? If so, what am I missing? Intermediate user of Acess 2003 SP1. |
#8
|
|||
|
|||
table joins
Jeff,
This may be a duplicate post, not sure if 1st went through. You are absolutely correct in how you describe my situation. Continue. "Jeff Boyce" wrote: Tim Your knowledge of/experience with Access is not the issue ... I assume that's one reason you're posting in the newsgroups. What I've been trying to get you to do is to step (totally) away from the way you've approached your situation and explain it in non-Access, non-database, non-technical terminology. It may be that what you want to accomplish can be done a different way than you've used ... if only it were clearer what it is you are trying to accomplish! Let me try this... I'll paraphrase what I suspect you are trying to do. You respond with corrections where I don't understand. Once there's a clearer picture, perhaps other folks will chime in with suggestions... You are recording information about incidents (you are calling them other things, and categorizing them). Some incidents relate to Quality issues, some to Safety issues, some to Process issues and some to Maintenance issues. I'm not clear if what you need to know about Quality issue incidents is appreciably different that what you need to know about Safety (or ...) issue incidents. However, if most of the information about the incident has the same characteristics (e.g., ReportedBy, DateReported, ...) and it is only the "category" (Quality, Safety, ...) and a description that are different, you could use a single table (oops! sorry, I jumped ahead). You also seem to want to record information about how the issues get handled. Based on what you've said so far, it sounds like you would keep largely similar information about "Corrective Action", regardless of the category of incident. Perhaps you are keeping something like: DateResolved, ActionTaken, ResolvedBy, ... (oops again!). Does this accurately reflect your situation? You have incidents, with characteristics, and you have resolutions ("Corrective Actions"), with characteristics. You want to connect one/more Corrective Action Taken to the appropriate incident. Let me know where I read too much into your descriptions, and we'll see what threads develop. Good luck Jeff Boyce Office/Access MVP "tlynn" wrote in message ... Jeff, Sorry, I am trying my best to describe my situation with the limited knowledge I have with Access. I originally started out with four seperate databases (Quality Safety, Process and Maintenance), each seperate projects/files. These databases were used to enter data that related to part quality problems, equipment failues, accidents, etc.). Just recently, I wanted to create a fifth database (common to the other four) to enter data that described what we did to correct the problem. Only specific records in the other four databases would required a record in the corrective action database. I made these databases common in structure but still maintaned them as four separate databases, and then placed them into one project/file. I tried to join the four databases to the corrective action database, with a One-to-Many join enforcing R/I, but then could not enter data without the error. I proceded to combine the four databases into one database (Database1) and joined this one common database to the corrective action database (Database2). Database1 (Quality, Safety, Process Maintanance) data currenly has 7800 records (rows of data) with about 25 fileds per row. Data is entered into this database at a rate of 10 records per workday. Joined to this database is the corrective action database (Database2). Database1 - Quality, Safety, Process Maintenance Data: Type (Quality, Safety, Process, Manitenance) PART# (1234) Report# (5678) Description (1.500 length u/s to 1.200) Etc Data (about 15 more data/number/date fields). Database2 - Corrective Action Data: Report# (1234) Due Date (12/16/05) Car Team (Names) Root Cause (255 characters) Corrective Action (255 characters) Preventative Action (255 characters) Date Closed (12/16/05) I am seeking help because my current configuration with Database1 and Database2 seems to have slowed down my filter/query time (5-7 seconds per search/query). So I was again trying to split the table back into four sperate tables with a fifth joined table for corrective action data. I have no problem using my current configuration (Database1 and Database2) except for the slow filter/query times. I was hoping that the four separate databases linked to the fifth common database would resolve my search time. Thanks, Tim "Jeff Boyce" wrote: Tim An 80 year old grandmother doesn't know from "tables"... you're still describing what you want to accomplish (a business need) in terms of "how" (table structure, etc.). And on what basis have you decided that a "BIG table" is undesirable? Is this a personal issue, or are there relational database design considerations? And define "BIG" -- are you concerned with how many rows, how many bytes, how many fields, ...? If it doesn't infringe on proprietary information, please provide an example .... not of the table structure you are already using, but of the data you wish to do something with (hint, hint, hint!). -- Regards Jeff Boyce Office/Access MVP "tlynn" wrote in message ... Hi Jeff, I want the one Corrective Action table to act as the common table to store corrective action data that relates to each of the 4 tables by Report# or some common ID. I am trying to avoid one BIG table which would consist of the Quality, Safety, Maintenance, Process data and one joined smaller Corrective Action table. Corrective Action Table: Report# Team Members Root Cause Corrective Action Preventative Action Quality, Safety, Maintenance, Process Tables: Report# Part# Description of Problem Etc. (many other fileds) I Hope this helps Thank You, Tim. "Jeff Boyce" wrote: You've described "how" you are trying to do something (your table structure, your form design, ...). Would you please describe "what" you want to accomplish, and a bit more about the data itself (an example would help)? In describing "what", turn off your computer and use terms an 80 year old grandmother might relate to... -- Regards Jeff Boyce Office/Access MVP "tlynn" wrote in message ... I have 4 tables (Quality, Safety, Process, Maintenance) each with the primary key set as "Report#" (no dupliactes allowed). I would like to relate these 4 tables to one table (Corrective Actions) that would store common information about the 4 tables by Report#. I am able to set up the one-to-many-relationship and enforce referential integrity. I have created a Corrective Action subform in each of the 4 forms but when I go to enter data it tells me "I cannot add or change a record because a related record is required in table "Safety". I had 4 tables in one large table (7,500 records) but was taking to long to filter data. Can I relate 4 tables to one? If so, what am I missing? Intermediate user of Acess 2003 SP1. |
#9
|
|||
|
|||
table joins
Tim
Again, perhaps because I'm not there and can't see what you're working with, this is just one person's opinion... tblIncident (this table holds information about incidents) -IncidentID (a Primary Key, an Access Autonumber, unless you have an incident reporting system that creates a unique identifier for each incident) -IncidentCategory (Quality, Safety, ... -- you could create another table to hold IncidentCategory, then use that here) -DateReported -ReportedBy (you could create another table to hold the folks who can report, referred, get referred to, carry out corrective action, etc., then use it everywhere you'd need a person) -IncidentDescription -DateReferredForAction -ReferredTo -ReferredBy -(... any other characteristics of the incident that you need to keep track of) trelCorrectiveAction (the table that tracks what gets done) -CorrectiveActionID (Primary Key, ... see above...) -ActionDate -ActionTakenBy -ActionDescription -OriginalReporterContacted (this was my own invention -- letting the original caller know what happened) -(... any other characteristics of the Corrective Action that you need to keep track of) Once you have your data in tables like these (and any other supporting/lookup tables, i.e., persons, incident categories, etc.), you can create queries to join corrective action(s) to incident. You can use forms for collecting both the incident and the related corrective action(s). You can use reports to print out detail or summary of incident and action. Hope that helps... Regards Jeff Boyce Office/Access MVP |
#10
|
|||
|
|||
table joins
Jeff, PMFJI, but...in tblCorrectiveAction, are you missing the foreign key
field IncidentID from tblIncidents? (or maybe i'm missing something...) "Jeff Boyce" wrote in message ... Tim Again, perhaps because I'm not there and can't see what you're working with, this is just one person's opinion... tblIncident (this table holds information about incidents) -IncidentID (a Primary Key, an Access Autonumber, unless you have an incident reporting system that creates a unique identifier for each incident) -IncidentCategory (Quality, Safety, ... -- you could create another table to hold IncidentCategory, then use that here) -DateReported -ReportedBy (you could create another table to hold the folks who can report, referred, get referred to, carry out corrective action, etc., then use it everywhere you'd need a person) -IncidentDescription -DateReferredForAction -ReferredTo -ReferredBy -(... any other characteristics of the incident that you need to keep track of) trelCorrectiveAction (the table that tracks what gets done) -CorrectiveActionID (Primary Key, ... see above...) -ActionDate -ActionTakenBy -ActionDescription -OriginalReporterContacted (this was my own invention -- letting the original caller know what happened) -(... any other characteristics of the Corrective Action that you need to keep track of) Once you have your data in tables like these (and any other supporting/lookup tables, i.e., persons, incident categories, etc.), you can create queries to join corrective action(s) to incident. You can use forms for collecting both the incident and the related corrective action(s). You can use reports to print out detail or summary of incident and action. Hope that helps... Regards Jeff Boyce Office/Access MVP |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help with relationship plase | Rock | General Discussion | 5 | July 4th, 2005 03:54 AM |
Help with relationship plase | Rock | Database Design | 5 | July 4th, 2005 03:54 AM |
unable to repair inobox | Sudheer Mumbai | General Discussion | 1 | February 20th, 2005 11:55 AM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |