referential integrity and tables

I have several tables.
Let's say that Table1 is Inventory - it is a superclass table and contains 
all inventory ids as its primary key and some other general info that applies 
to each inventory item.
Table 2 is Car that has primary key that is a foreign key from Inventory 
table.
Table 3 is Container that has primary key that is a foreign key from 
Inventory table.
Table 2 and Table 3 are subclasses, they are disjoint and total in my design.
I linked primary keys in Table 2 and 3 to the primary key in Table 1, and MS 
Access placed 1:1 relationship.
I need it to be 0:1 or 1:1 relationship, so that record for Table 1 is 
always there for either table 2 or 3. 
As of now, when I'm typing information with primary key "1" in Inventory, it 
asks for the records with primary key "1" in both tables Table 2 and 3.

How do I make it, so for example, Table 1 has keys 1,2,3 where key "1" and 
"2" are primary keys in Table 2, but key "3" refers to Table 3?

Is there anyway to do it just by leaving primary keys in Table 1 be a 
foreign key which is at the same time a primary key in Table 2 and 3 without 
creating a new attribute in Tables 2 and 3 which will be a separate foreign 
key for Table 1???

Thanks 


0
Utf
11/12/2009 10:26:02 PM
access.tablesdbdesign 510 articles. 0 followers. Follow

4 Replies
633 Views

Similar Articles

[PageSpeed] 15

I'm a little confused by your description, but it sounds like you want to be 
able to use the primary key from your table #1 as a primary key for your 
tables #2 and #3.  In this instance, even though those (table2,3) primary 
keys could be considered to "point back" at table1, they are table 2 & 3 
primary keys (not foreign keys).

The one-to-one relationship implies the possibility of a one-to-zero (i.e., 
table 1 might record an inventory item that is NOT a Car, and hence, the ID 
doesn't show up in table 2).

Is there a chance you are experiencing technical difficulties with the ID 
fields in tables 2 & 3?  Do you have them defined as Long Int fields, or are 
you trying to have Access generate Autonumbers for them?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"nikka" <nikka@discussions.microsoft.com> wrote in message 
news:EA88DBFE-29F4-4158-BD76-2D058EAA8BF7@microsoft.com...
>I have several tables.
> Let's say that Table1 is Inventory - it is a superclass table and contains
> all inventory ids as its primary key and some other general info that 
> applies
> to each inventory item.
> Table 2 is Car that has primary key that is a foreign key from Inventory
> table.
> Table 3 is Container that has primary key that is a foreign key from
> Inventory table.
> Table 2 and Table 3 are subclasses, they are disjoint and total in my 
> design.
> I linked primary keys in Table 2 and 3 to the primary key in Table 1, and 
> MS
> Access placed 1:1 relationship.
> I need it to be 0:1 or 1:1 relationship, so that record for Table 1 is
> always there for either table 2 or 3.
> As of now, when I'm typing information with primary key "1" in Inventory, 
> it
> asks for the records with primary key "1" in both tables Table 2 and 3.
>
> How do I make it, so for example, Table 1 has keys 1,2,3 where key "1" and
> "2" are primary keys in Table 2, but key "3" refers to Table 3?
>
> Is there anyway to do it just by leaving primary keys in Table 1 be a
> foreign key which is at the same time a primary key in Table 2 and 3 
> without
> creating a new attribute in Tables 2 and 3 which will be a separate 
> foreign
> key for Table 1???
>
> Thanks
>
> 


0
Jeff
11/13/2009 1:02:31 AM
Yes, you understood my main idea correctly. 
I guess, I also want the table #1 to be automatically populated with partial 
information and especially keys from both tables #2 and #3.

All three primary keys in each table are of type Number and they are also 
LongInteger by the field size. They are indexed and no duplicates are allowed.
They are NOT Autonumbers.

So, when I am inputting record in table #2, because of the defined 
relationship, "+" sign appears on the right; when I click the sign, I can 
populate rows with that partial information that is supposed to be stored in 
table #1 under the same primary key. When I try to save, I get error saying: 
"You cannot change a record because related record is required in table #3". 
When I do the same thing in table #3, I get another error: "Index or primary 
key cannot contain Null value".
However, I can input attributes for table #2 and #3 and save them, but not 
the partial information which is supposed to be stored in table #1. 

Then, if I go into table #1 and try to manually input that partial 
information using, for example, primary key from table #3, there is a message 
that tells me that I "cannot add or change a record because a related record 
is required in table #2"

If you need more information, give me a hint at which one, I will provide 
more.
Sorry for confusion it's my first database experience.
Thank you.

"Jeff Boyce" wrote:

> I'm a little confused by your description, but it sounds like you want to be 
> able to use the primary key from your table #1 as a primary key for your 
> tables #2 and #3.  In this instance, even though those (table2,3) primary 
> keys could be considered to "point back" at table1, they are table 2 & 3 
> primary keys (not foreign keys).
> 
> The one-to-one relationship implies the possibility of a one-to-zero (i.e., 
> table 1 might record an inventory item that is NOT a Car, and hence, the ID 
> doesn't show up in table 2).
> 
> Is there a chance you are experiencing technical difficulties with the ID 
> fields in tables 2 & 3?  Do you have them defined as Long Int fields, or are 
> you trying to have Access generate Autonumbers for them?
> 
> More info, please...
> 
> Regards
> 
> Jeff Boyce
> Microsoft Access MVP
> 
> -- 
> Disclaimer: This author may have received products and services mentioned
> in this post. Mention and/or description of a product or service herein
> does not constitute endorsement thereof.
> 
> Any code or pseudocode included in this post is offered "as is", with no
> guarantee as to suitability.
> 
> You can thank the FTC of the USA for making this disclaimer
> possible/necessary.
> 
> "nikka" <nikka@discussions.microsoft.com> wrote in message 
> news:EA88DBFE-29F4-4158-BD76-2D058EAA8BF7@microsoft.com...
> >I have several tables.
> > Let's say that Table1 is Inventory - it is a superclass table and contains
> > all inventory ids as its primary key and some other general info that 
> > applies
> > to each inventory item.
> > Table 2 is Car that has primary key that is a foreign key from Inventory
> > table.
> > Table 3 is Container that has primary key that is a foreign key from
> > Inventory table.
> > Table 2 and Table 3 are subclasses, they are disjoint and total in my 
> > design.
> > I linked primary keys in Table 2 and 3 to the primary key in Table 1, and 
> > MS
> > Access placed 1:1 relationship.
> > I need it to be 0:1 or 1:1 relationship, so that record for Table 1 is
> > always there for either table 2 or 3.
> > As of now, when I'm typing information with primary key "1" in Inventory, 
> > it
> > asks for the records with primary key "1" in both tables Table 2 and 3.
> >
> > How do I make it, so for example, Table 1 has keys 1,2,3 where key "1" and
> > "2" are primary keys in Table 2, but key "3" refers to Table 3?
> >
> > Is there anyway to do it just by leaving primary keys in Table 1 be a
> > foreign key which is at the same time a primary key in Table 2 and 3 
> > without
> > creating a new attribute in Tables 2 and 3 which will be a separate 
> > foreign
> > key for Table 1???
> >
> > Thanks
> >
> > 
> 
> 
> .
> 
0
Utf
11/13/2009 8:44:02 AM
On Fri, 13 Nov 2009 00:44:02 -0800, nikka <nikka@discussions.microsoft.com>
wrote:

>Yes, you understood my main idea correctly. 
>I guess, I also want the table #1 to be automatically populated with partial 
>information and especially keys from both tables #2 and #3.
>
>All three primary keys in each table are of type Number and they are also 
>LongInteger by the field size. They are indexed and no duplicates are allowed.
>They are NOT Autonumbers.
>
>So, when I am inputting record in table #2, because of the defined 
>relationship, "+" sign appears on the right; when I click the sign, I can 
>populate rows with that partial information that is supposed to be stored in 
>table #1 under the same primary key. When I try to save, I get error saying: 
>"You cannot change a record because related record is required in table #3". 
>When I do the same thing in table #3, I get another error: "Index or primary 
>key cannot contain Null value".
>However, I can input attributes for table #2 and #3 and save them, but not 
>the partial information which is supposed to be stored in table #1. 
>
>Then, if I go into table #1 and try to manually input that partial 
>information using, for example, primary key from table #3, there is a message 
>that tells me that I "cannot add or change a record because a related record 
>is required in table #2"
>
>If you need more information, give me a hint at which one, I will provide 
>more.
>Sorry for confusion it's my first database experience.
>Thank you.

Relationships - even one to one relationships - have directionality. In the
more common one-to-many you must fill in a record in the "One" table before
you can create records in the "Many".

The same applies with one to one relationships: you must fill in a record in
the "parent" table before that parent can have a child.

One other concern - it appears that you may be trying to store "partial
information" redundantly in both Table1 and Table2. The ONLY field that should
exist in both tables is the Primary Key of each; the Primary Key of table2
will also be functioning as a foreign key. If you're trying to store *other*
fields in both tables... don't!!!! Redundancy is redundant, and redundancy is
redundantly BAD.
-- 

             John W. Vinson [MVP]
0
John
11/13/2009 5:33:33 PM
Thank you so much!
Directionality was the issue.
Now it works! =)


"John W. Vinson" wrote:

> On Fri, 13 Nov 2009 00:44:02 -0800, nikka <nikka@discussions.microsoft.com>
> wrote:
> 
> >Yes, you understood my main idea correctly. 
> >I guess, I also want the table #1 to be automatically populated with partial 
> >information and especially keys from both tables #2 and #3.
> >
> >All three primary keys in each table are of type Number and they are also 
> >LongInteger by the field size. They are indexed and no duplicates are allowed.
> >They are NOT Autonumbers.
> >
> >So, when I am inputting record in table #2, because of the defined 
> >relationship, "+" sign appears on the right; when I click the sign, I can 
> >populate rows with that partial information that is supposed to be stored in 
> >table #1 under the same primary key. When I try to save, I get error saying: 
> >"You cannot change a record because related record is required in table #3". 
> >When I do the same thing in table #3, I get another error: "Index or primary 
> >key cannot contain Null value".
> >However, I can input attributes for table #2 and #3 and save them, but not 
> >the partial information which is supposed to be stored in table #1. 
> >
> >Then, if I go into table #1 and try to manually input that partial 
> >information using, for example, primary key from table #3, there is a message 
> >that tells me that I "cannot add or change a record because a related record 
> >is required in table #2"
> >
> >If you need more information, give me a hint at which one, I will provide 
> >more.
> >Sorry for confusion it's my first database experience.
> >Thank you.
> 
> Relationships - even one to one relationships - have directionality. In the
> more common one-to-many you must fill in a record in the "One" table before
> you can create records in the "Many".
> 
> The same applies with one to one relationships: you must fill in a record in
> the "parent" table before that parent can have a child.
> 
> One other concern - it appears that you may be trying to store "partial
> information" redundantly in both Table1 and Table2. The ONLY field that should
> exist in both tables is the Primary Key of each; the Primary Key of table2
> will also be functioning as a foreign key. If you're trying to store *other*
> fields in both tables... don't!!!! Redundancy is redundant, and redundancy is
> redundantly BAD.
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
11/13/2009 6:36:01 PM
Reply:

Similar Artilces:

Allow Integration manager to integrate with Analytical accounting
Cuurently integration manager does not work with the Analyticval Accounting module ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=3cfe89d7-f858-4791-8bc4-e51b84cf7853&...

Prevent users from changing a pivot table
Hi everyone I use Excel 2003 and have created a pivot table that I want to share with colleagues. I know that I can lock the worksheet and hide the sheet which contains the raw data but I want my colleagues to be able to use the drop down boxes within the pivot table. Is it possible for me to lock the format of the table (and, for instance, prevent them from seeing the field list) but still allow them to use the drop down features to display various data within the table? Thank you in advance. ...

Using Date Grouping in Pivot Tables
I've tried (to a maddening end) to figure out how to do this. According to the Excel help, once I've created a pivot table, all I need to do is select the column of dates, select group, and then a sub window will open up to allow me to subdivide by week, month, etc. However, despite every and any known attempt to alter the structure of the dates, I can never get the option to appear and all that Excel (using 2003) will do is put it in a generic Group1. The file I'm working with is a CSV output from an SQL table. Excel opens it without any problem, and easily recognizes the...

Pivot Table Grand Totals
I have a pivot table I use to summarize my company's sales force performance. The rows are data for each date. The columns are in groups of 4 which include $, % of $ to daily total, quantity, and % of qty to daily total. There are about 20 of these groups. All the way to the right, there are 4 grand total columns. I want to show the grand total $ and qty, but I would like to hide the % to total columns because they have 100% in every row. Can this be done? Thanks. Click the letter of the column you want to hide to select the entire column, then right click and select "h...

Fields in tables
Hi, Can you explain what is the field table SOP10200: LNITMSEQ (Line Imte Sequence)? How it calculated? And what dows it mean? The same for example SEQNUMBE (Sequence Number) for table RM30301 Thank you Vitali Hey Vitali V: It is a unique identifier for each row. It is the addition of 16384. It works in conjuction with CMPNTSEQ. Regards "Vitali V" wrote: > Hi, > > Can you explain what is the field table SOP10200: LNITMSEQ (Line Imte > Sequence)? How it calculated? And what dows it mean? > > The same for example SEQNUMBE (Sequence Number) for table R...

Integration with Great Plains 02-08-07
Hi, I am unable to move forward from the screen where we have to provide the Integration User Group Name when we run the set up for the CRM + GP connector... The wizard returns an error "The Specified Integration User group is not a domain group" My set up is on the CRM may edition VPC...i have setup GP as well... regards Okay ...resolved that ...i had not used the "domainname\group name" When will the integration be supported on SQL server 2005? Regards Sree "S" wrote: > Hi, > > I am unable to move forward from the screen where we have to pro...

Updating two identical formatted tables with one form
At present I have a membership database with all members held in one table. I have been asked to split this table into two tables - family and individual members. The user still wants to view all the records in both tables using the same form as at present. However if I use a UNION query on the two tables and link the form to this query, I have found that although I can view all the records from both tables, I now cannot update them. Any advice on a better way to design and link the tables would be appreciated. Cynw, Let's start from the beginning why did *whoever* think f...

Report customization and linking tables
Hi, I'm currently working on customizing a manufacturing report with the GP report customization tool. I'm trying to find a way to link the item master in the request resolution report to the purchase line history, pop30110. I tried to open a new relationship through the item master while customizing the mfg report but don't see that table as an option. Is there a way to do this. -- Jim Bourque Jim, This is a constraint with Report Writer. You cannot have more than one one-to-many relationship in this report for the same table group, this is the top table and the c...

How To Access From VBA A Tables Row Lookup Row Source
Hello: I created a table field with a Lookup Row Source Property Type = Value List and a hand-coded list of values. The user can right click this field in a form and add additional values to the hand-coded Row Source (i.e. Joe, Mary, John, and then they add Mark). How can I access the Row Source in VBA because I need to use it in a drop-down UNBOUND Combo Box. Is that possible? I searched everywhere including the Object Browser. -- Rich Locus Logicwurks, LLC You should not be using table lookup fields: http://www.mvps.org/access/lookupfields.htm -- Arvin Meyer, MCP, ...

Integrating Receipts
Customer tracks their receipts via Positouch. At the end of each day, they integrate a journal entry for the total funds desposited and sales. (Debit Cash/Credit sales, revenue, etc) They also need information to update bank reconcilation in order to reconcile bank statement. I have suggested in the journal entry above debiting a cash suspense account, and the using Increase Adjustments to update the checkbook - avoiding the deposit w/receipts step. The process i outlined allows them to reconcile easier the in/out This customer also have electronic reconcile - will having the deposits...

Adding a table to a report
Should be really simple - so I am missing something equally simple? I need to add a tracking number (UPS/FedEx) to the printed blank invoice. I know this information is kept in table SOP10107. So what precisely do I need to do to create a relationship that will let me see this table in my list of tables already used by the invoice (SOP Blank invoice). I have (in report writer/tables) created relationships, but they do not "come over" when I get into the report layout. My blood pressure would be greatly appreciative if anyopne can tell me what my bonehead mistake is. thanks Y...

Mercury (FTD) Integration
We have a client that has purchaced RMS from us and has said there is a software called Mercury that FTD sells to florists for the nationwide flower arrangement network. Does anyone know about this, or where it can be found? Thank you, Brian brian@tmssupply.com Is it mercury payment for credit cards? "Brian TMS" <Brian TMS@discussions.microsoft.com> wrote in message news:DF65ADBE-D4E3-46C8-9E85-0D8049AE4B63@microsoft.com... > We have a client that has purchaced RMS from us and has said there is a > software called Mercury that FTD sells to florists for the nation...

GP Table Structure
I too am learning Crystal Report. Its clear that understanding GP tables is key to writing Crystal Reports. It is also clear that comprehensive information regarding GP Table Structure is very hard to come by. Is there a textbook available that defines table names and inter relationships. Ed, Your best bet is the Microsoft Dynamics GP Software Development Kit (SDK) documentation. Accolade Publications also publishes a set of useful titles exclusively dedicated to explain in detail the flow of information and posting ("Information Flow & Posting") and other tricks of th...

CISCO Call Manager integration
Does anyone have experience in integrating CRM with CISCO's VOIP Call Manager solution? We are looking for people who have successfully implemented the two systems and can assist us in network design, implementation and management. We are a CISCO partner, where are you located? "IPnewdevdude" wrote: > Does anyone have experience in integrating CRM with CISCO's VOIP Call Manager > solution? We are looking for people who have successfully implemented the > two systems and can assist us in network design, implementation and > management. ...

Referential Integrity?
I have a foreclosure database. I have a foreclosure table and it had a relationship to a bankruptcy table. I wanted to change the relationship to B_Judges instead of B_District field in the bankruptcy table. I was successful but now I am unable to select Referential Integrity because of something in the Primary table. What's wrong? Heids2265, You probably have some records in one, or both the tables that won't allow for referential integrity. Start by setting up query using the Bankrupt table related to your Foreclose table using a Left Outer Join on B_Judges. Use t...

Issues with Integrating Multiple Spreadsheets for Reports or Queries?
We are keen to discuss and help with issues arising when needing to query, or produce reports using multiple Excel spreadsheets (Multiple Excel Spreadsheet Integration issues): Issues like: - We can=92t easily compare a choice of values in one spreadsheet with a choice in another worksheet - So we cannot easily answer the question =93what do all these rows have in common with those rows=94 Because: - We have to do a weekly report integrating multiple Project Plans, Financial Analyses =85 - We spend tons of time cutting and pasting, sorting and merging =96 the same process repeated every week...

Changing table border color
I am working in excel 2003. I have various sheets with a number of tables in each one. I need to change the color of the table borders without doing anything to the border style. When I try using the Format/Cells option, and then in the borders tab, I can see the table lines divided with a gray (generic) style because my selection has different types of styles. When I change the color, it will not apply the changes because I don't click on the grayed borders (if I did, it would reset my styles, which is an undesired effect) I don't really want to use macros (because I think thi...

Nested Pivot Table
Sorry about the subject title but only way I could think to describe my question, I mainly work with clinical data so frame my question with that. If i have a worksheet containing case notes which had colum headings of diagnosis gender town I want to summarise the data by creating a pivot table with diagnosis lables as rows town labels as colums each town colum divided by male/female gender with a count of diagnosis in the table. I can create a simple table with either town or gender as colum hedings but not nesting one under the other. Any suggestions? TIA Trevor Diagnosis In a pivot...

identify the rows with a matched or unmatched field in multiple tables
At various times, I need to compare two or more EXCEL worksheets to identify the rows with a designated field that matches as well as those that does not match the fields in rows of multiple worksheets. I have been importing the EXCEL worksheets into ACCESS to use the query function to identify such rows. However, in creating the relationships between the tables, the options offered are only to match fields of all tables or a single table. The option to identify those rows with a field that does not match the designated field of the other tables is not available. To work around this ...

Integration Manager
Really new to GP and Integration Manager Can someone explain to me how the files need to be set up in order for them to be imported into GP. I have the following fields that I think are what need to be imported, but would love some assistance. vendor ID Account DESCRIPTION amount dr_cr amt doc date document number doc type On Sep 23, 2:14=A0pm, LSM <L...@discussions.microsoft.com> wrote: > Really new to GP and Integration Manager > > Can someone explain to me how the files need to be set up in order for th= em > to be imported into GP. I have the following fields...

Table Lookup Limitations?
Table Lookup Limitations ? ? ? My object is to automate a form report of pre worded statements generated from a code that selects (YOU HAVE statements) # 1-27 and (WE RECOMMEND statements) # 28-62 from -Table1- and displayed in cells to be merged to word in a table named -Data- I am using excel as the data merge source document and every thing seems to work until statement # 30 then excel repeats statement #31 over and over regardless of the code number above #27 entered. Excel sheet -Table1- column 1 is numbered consecutively 1 to 62 with column 2 containing the statements that I wa...

Pivot Table (vlookup 2 column text values, return 1 value)
Data Headers: Period ResoureName ProjectName ProjectID SubTeam Work ActualWork I have used a pivot table to get the value needed for breaking down project hours by Team. I need to perform a vlookup that looks for the ProjectName and SubTeam text values and return ActualWork value. HEADER VALUE A1, Period A2, 12 B1, ResoureName B2, Bob Thomas C1, ProjectName C2, Project X D1, ProjectID D2, 11292005 E1, SubTeam E2, Network Support F1, Work F2, 20 G1, ActualWork ...

Remote support VNC integrated into CRM
Hi, does anyone know of a CRM which can launch a vnc type remote support tool to connect to someone in existing address book. For a technology support company. Thanks. add a coloum called remotehost that field should havethe hostname/ipaddress of the endpoint that you want to connect to. create another coloumn call connection type. use list type with pairs. add in name|value remote desktop|rdp:// vnc|vnc:// ssh2|ssh:// telnet|telnet:// then custimize the the form. you will have to do some coding onto the form to add them togther. so you will get vnc://123.123.123.123 rdp://564.564.565....

Fixed Assets integration error in Integration Manager
Would anyone know what would be causing this error when trying to run a fixed assets tax book integration in GP10? DOC 1 ERROR: System.Data.SqlClient.SqlError: Cannot insert the value NULL into column 'LUXELAUTO', table 'TEST.dbo.FA00200'; column does not allow nulls. INSERT fails. That column refers to the Luxury Auto field with either a yes or no answer. This error repeats until it reaches the error limit. The preview on this integration works fine, so it's finding the data. Any help would be greatly appreciated - I've wasted enough time on this! ...

CT Tables
I've several of tables loaded into my company database. I am looking at these tables and they have some interesting fields. Can anyone tell me what modules these tables are for? I cannot find any information on these tables in GP tables description. They all start with CT. CT00001 CT00002 CT00003 CT00004 CT00005 CT00102 CT00200 CT40401 Any help will be highly appreciated. Thanks, VJ These tables do not appear to be native to Great Plains, unless they are new to version 8. What third party products do you have installed along with Great Plains? Any developer worth his salt would ...