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 |
#11
|
|||
|
|||
Allow Duplicates
Hello John,
I tried using your recommendation yet in trying the join in the relationship window i continue to get inderterminate error. I set up the two field composite key using the fields InvID, InvCtry by holding the shift key and highlighting both fields. The same was done for details table yet no sucess. Any further direction is greatly appreciated. "John W. Vinson" wrote: On Tue, 4 Aug 2009 18:34:01 -0700, acss wrote: Apologies for the confusion Graham, I would like for the primary key to be unique in the invoice table and as a foreign key in the details Table however i have the challenge that duplicate invoices need to be entered and should be accepted only if it is from a different country. InvTable InvID---PK InvCtry Invdate etc InvDetails InvID-FK ProductID--FK---COMPOSITE KEY consisting of InvID and ProductID Price Qty ProductTable ProductID-----PK-Autonumber ProductName Category Etc I am stuck since i need a one to many relationship between Inv and details(one invoice can have many details).The problem occurs as the same invoice number needs to be entered 3 times once for china another for ecuador and another for brazil..that is why i need acess to accept same invoice number only if by different countries. Can you suggest some direction please? It sounds like you need a composite, two-field primary key (InvoiceNo and CountryCode), which will also require a composite, two-field foreign key in the details table. You can use InvCtry;InvID as the Master Link Field and Child Link Field property of your subform. You will not be able to use an Autonumber for InvID, but then it's inappropriate to use an autonumber for that purpose in any case. -- John W. Vinson [MVP] |
#12
|
|||
|
|||
Allow Duplicates
On Sun, 9 Aug 2009 22:29:01 -0700, acss wrote:
Hello John, I tried using your recommendation yet in trying the join in the relationship window i continue to get inderterminate error. I set up the two field composite key using the fields InvID, InvCtry by holding the shift key and highlighting both fields. The same was done for details table yet no sucess. Any further direction is greatly appreciated. If you look at the parent table in design view are there key icons on InvID and on InvCtry? When you're creating the join, are you dragging InvID to InvID, InvCtry to InvCtry, so that there are two lines, prior to setting the relationship's properties? -- John W. Vinson [MVP] |
#13
|
|||
|
|||
Allow Duplicates
Yes. In the Invoice Table and Details table there are two key icons. Not sure
as your description of two lines since trying to drag more than one field onto another table will bring an error of there can only be one relationship. The previous autonumber as primary was working in that it created a new record yet it did not allow for a control to be in effect. That control is to allow duplicates of the invoice number entered as long as it is from a different country. If from same country then invoice number cannot be entered. Apologies for causing you this grief...any other suggestions for me to follow...really appreciate your support. "John W. Vinson" wrote: On Sun, 9 Aug 2009 22:29:01 -0700, acss wrote: Hello John, I tried using your recommendation yet in trying the join in the relationship window i continue to get inderterminate error. I set up the two field composite key using the fields InvID, InvCtry by holding the shift key and highlighting both fields. The same was done for details table yet no sucess. Any further direction is greatly appreciated. If you look at the parent table in design view are there key icons on InvID and on InvCtry? When you're creating the join, are you dragging InvID to InvID, InvCtry to InvCtry, so that there are two lines, prior to setting the relationship's properties? -- John W. Vinson [MVP] |
#14
|
|||
|
|||
Allow Duplicates
On Mon, 10 Aug 2009 19:11:26 -0700, acss wrote:
Yes. In the Invoice Table and Details table there are two key icons. Then you will - MUST!!! - have a *one to one* relationship. The two fields should be the Primary Key of the Invoice table. Since the Invoice Table is related one-to-many to the Details table, the Details table must NOT have these two fields as its primary key. They must be foreign keys, non-unique. Not sure as your description of two lines since trying to drag more than one field onto another table will bring an error of there can only be one relationship. Incorrect. There is only one relationship... but it can consist of up to TEN fields in each table, which would be displayed onscree as TEN lines. Did you try it? The previous autonumber as primary was working in that it created a new record yet it did not allow for a control to be in effect. That control is to allow duplicates of the invoice number entered as long as it is from a different country. If from same country then invoice number cannot be entered. Apologies for causing you this grief...any other suggestions for me to follow...really appreciate your support. Try what I suggested, for starters. It will work. -- John W. Vinson [MVP] |
#15
|
|||
|
|||
Allow Duplicates
Hello John,
I created a field called PRIME and assigned that as primary key then I assigned both the InvID and InvCntry as composite keys. When clicking the indexs icon it displays Prime as unique while the other two fields allows duplicates. The same set up applys with the Details Table. I went into the relationship window to drag and drop muliple fields to the details table and this resulted in 3 relationship lines of one to many. I cannot set the (enforce referential integrity on the relationship) yet in testing data entry within the invoices table it accomplishes what i needed...it accepts duplicate invoices as long as a different country is entered and rejects if same invoice and same country is entered. INV-TABLE Prime----PK-text InvID---Composite-text InvCntry--Composite-text INV-DETAILS TABLE Prime----PK-text InvID---FK-text InvCntry-FK-text Is this what you were referring to or at the very least a hint of progress? "John W. Vinson" wrote: On Mon, 10 Aug 2009 19:11:26 -0700, acss wrote: Yes. In the Invoice Table and Details table there are two key icons. Then you will - MUST!!! - have a *one to one* relationship. The two fields should be the Primary Key of the Invoice table. Since the Invoice Table is related one-to-many to the Details table, the Details table must NOT have these two fields as its primary key. They must be foreign keys, non-unique. Not sure as your description of two lines since trying to drag more than one field onto another table will bring an error of there can only be one relationship. Incorrect. There is only one relationship... but it can consist of up to TEN fields in each table, which would be displayed onscree as TEN lines. Did you try it? The previous autonumber as primary was working in that it created a new record yet it did not allow for a control to be in effect. That control is to allow duplicates of the invoice number entered as long as it is from a different country. If from same country then invoice number cannot be entered. Apologies for causing you this grief...any other suggestions for me to follow...really appreciate your support. Try what I suggested, for starters. It will work. -- John W. Vinson [MVP] |
#16
|
|||
|
|||
Allow Duplicates
On Tue, 11 Aug 2009 10:09:01 -0700, acss wrote:
Hello John, I created a field called PRIME and assigned that as primary key then I assigned both the InvID and InvCntry as composite keys. When clicking the indexs icon it displays Prime as unique while the other two fields allows duplicates. The same set up applys with the Details Table. I went into the relationship window to drag and drop muliple fields to the details table and this resulted in 3 relationship lines of one to many. I cannot set the (enforce referential integrity on the relationship) yet in testing data entry within the invoices table it accomplishes what i needed...it accepts duplicate invoices as long as a different country is entered and rejects if same invoice and same country is entered. INV-TABLE Prime----PK-text InvID---Composite-text InvCntry--Composite-text INV-DETAILS TABLE Prime----PK-text InvID---FK-text InvCntry-FK-text Is this what you were referring to or at the very least a hint of progress? Again... *you are misunderstanding what a primary key is*. It's PRIMARY! It is - must be - *unique* within the table. It can occur once, and only once! Therefore, it's appropriate to have it as the primary key of the INV-TABLE but it is *WRONG* to have it as the primary key of the INV-DETAILS table. The INV-DETAILS table should have its *own*, separate, independent, unrelated primary key; it should have the PRIME field as a non-unique foreign key, related to the Prime field in a one-to-many relationship. What I had suggested is a multifield primary key: INV-TABLE InvID Text, Primary Key InvCntry Text, Primary Key other fields INV-DETAILS TABLE DetailID Autonumber, Primary Key InvID Text, matching INV-TABLE.InvID in size InvCntry Text, matching INV-TABLE.InvCntry in size other detail fields If you then open the Relationships window you can join InvID to InvID, and join InvCNtry to InvCntry; enforce relational integrity and you should get a one to many relationship. Creating a separate Prime field is an alternative; it could be an Autonumber in INV-TABLE, with a separate unique two-field index on InvID and InvCntry. In this approach you would have a Long Integer Prime field as a foreign key (NOT a primary key) in INV-DETAILS, and the InvID and InvCntry fields would not exist in the details table at all (since that would be redundant, and they can be found by linking to INV-TABLE). How are you assigning a value to the Prime field anyway? -- John W. Vinson [MVP] |
#17
|
|||
|
|||
Allow Duplicates
Hello John,
I still could not get the results as using the fields you displayed: INV-TABLE InvID Text, Primary Key InvCntry Text, Primary Key other fields INV-DETAILS TABLE DetailID Autonumber, Primary Key InvID Text, matching INV-TABLE.InvID in size InvCntry Text, matching INV-TABLE.InvCntry in size other detail fields My understanding : INV-TABLE InvID----IS TEXT PRIMARY KEY InvCntry---is part of a composite key consisting of InvID & InvCntry (set InvCntry as unique-index-no duplicates INV-DETAILS DetailID --AUTONUMBER -If this is the autonumber should i have FK field as number long integer in the INV-TABLE. ( Have tried this and it creates a one to many from the details table. I need a one to many from the INV-TABLE to the INV DETAILS TABLE. InvID--set as index-allow duplicates--FK InvCntry--set as index-allow duplicates--FK I have tried yet not able to obtain referential integrity or the one to many from Invoice table to the Invoice Details table. Is it imperative to have referential Integrity for what i need? Thank you for th support "John W. Vinson" wrote: On Tue, 11 Aug 2009 10:09:01 -0700, acss wrote: Hello John, I created a field called PRIME and assigned that as primary key then I assigned both the InvID and InvCntry as composite keys. When clicking the indexs icon it displays Prime as unique while the other two fields allows duplicates. The same set up applys with the Details Table. I went into the relationship window to drag and drop muliple fields to the details table and this resulted in 3 relationship lines of one to many. I cannot set the (enforce referential integrity on the relationship) yet in testing data entry within the invoices table it accomplishes what i needed...it accepts duplicate invoices as long as a different country is entered and rejects if same invoice and same country is entered. INV-TABLE Prime----PK-text InvID---Composite-text InvCntry--Composite-text INV-DETAILS TABLE Prime----PK-text InvID---FK-text InvCntry-FK-text Is this what you were referring to or at the very least a hint of progress? Again... *you are misunderstanding what a primary key is*. It's PRIMARY! It is - must be - *unique* within the table. It can occur once, and only once! Therefore, it's appropriate to have it as the primary key of the INV-TABLE but it is *WRONG* to have it as the primary key of the INV-DETAILS table. The INV-DETAILS table should have its *own*, separate, independent, unrelated primary key; it should have the PRIME field as a non-unique foreign key, related to the Prime field in a one-to-many relationship. What I had suggested is a multifield primary key: INV-TABLE InvID Text, Primary Key InvCntry Text, Primary Key other fields INV-DETAILS TABLE DetailID Autonumber, Primary Key InvID Text, matching INV-TABLE.InvID in size InvCntry Text, matching INV-TABLE.InvCntry in size other detail fields If you then open the Relationships window you can join InvID to InvID, and join InvCNtry to InvCntry; enforce relational integrity and you should get a one to many relationship. Creating a separate Prime field is an alternative; it could be an Autonumber in INV-TABLE, with a separate unique two-field index on InvID and InvCntry. In this approach you would have a Long Integer Prime field as a foreign key (NOT a primary key) in INV-DETAILS, and the InvID and InvCntry fields would not exist in the details table at all (since that would be redundant, and they can be found by linking to INV-TABLE). How are you assigning a value to the Prime field anyway? -- John W. Vinson [MVP] |
#18
|
|||
|
|||
Allow Duplicates
InvCntry---is part of a composite key consisting of InvID & InvCntry (set
InvCntry as unique-index-no duplicates No, if you make InvCntry a unique index then you will not be able to add two invoices fron the same country! Have you seen my posts from more than two weeks ago where I suggested using a surrogate (autonumber) primary key and having the InvNum/InvCntry as a secondary unique index? As I've explained before, I believe this is by far the best approach. -- Graham Mandeno [Access MVP] Auckland, New Zealand "acss" wrote in message ... Hello John, I still could not get the results as using the fields you displayed: INV-TABLE InvID Text, Primary Key InvCntry Text, Primary Key other fields INV-DETAILS TABLE DetailID Autonumber, Primary Key InvID Text, matching INV-TABLE.InvID in size InvCntry Text, matching INV-TABLE.InvCntry in size other detail fields My understanding : INV-TABLE InvID----IS TEXT PRIMARY KEY InvCntry---is part of a composite key consisting of InvID & InvCntry (set InvCntry as unique-index-no duplicates INV-DETAILS DetailID --AUTONUMBER -If this is the autonumber should i have FK field as number long integer in the INV-TABLE. ( Have tried this and it creates a one to many from the details table. I need a one to many from the INV-TABLE to the INV DETAILS TABLE. InvID--set as index-allow duplicates--FK InvCntry--set as index-allow duplicates--FK I have tried yet not able to obtain referential integrity or the one to many from Invoice table to the Invoice Details table. Is it imperative to have referential Integrity for what i need? Thank you for th support "John W. Vinson" wrote: On Tue, 11 Aug 2009 10:09:01 -0700, acss wrote: Hello John, I created a field called PRIME and assigned that as primary key then I assigned both the InvID and InvCntry as composite keys. When clicking the indexs icon it displays Prime as unique while the other two fields allows duplicates. The same set up applys with the Details Table. I went into the relationship window to drag and drop muliple fields to the details table and this resulted in 3 relationship lines of one to many. I cannot set the (enforce referential integrity on the relationship) yet in testing data entry within the invoices table it accomplishes what i needed...it accepts duplicate invoices as long as a different country is entered and rejects if same invoice and same country is entered. INV-TABLE Prime----PK-text InvID---Composite-text InvCntry--Composite-text INV-DETAILS TABLE Prime----PK-text InvID---FK-text InvCntry-FK-text Is this what you were referring to or at the very least a hint of progress? Again... *you are misunderstanding what a primary key is*. It's PRIMARY! It is - must be - *unique* within the table. It can occur once, and only once! Therefore, it's appropriate to have it as the primary key of the INV-TABLE but it is *WRONG* to have it as the primary key of the INV-DETAILS table. The INV-DETAILS table should have its *own*, separate, independent, unrelated primary key; it should have the PRIME field as a non-unique foreign key, related to the Prime field in a one-to-many relationship. What I had suggested is a multifield primary key: INV-TABLE InvID Text, Primary Key InvCntry Text, Primary Key other fields INV-DETAILS TABLE DetailID Autonumber, Primary Key InvID Text, matching INV-TABLE.InvID in size InvCntry Text, matching INV-TABLE.InvCntry in size other detail fields If you then open the Relationships window you can join InvID to InvID, and join InvCNtry to InvCntry; enforce relational integrity and you should get a one to many relationship. Creating a separate Prime field is an alternative; it could be an Autonumber in INV-TABLE, with a separate unique two-field index on InvID and InvCntry. In this approach you would have a Long Integer Prime field as a foreign key (NOT a primary key) in INV-DETAILS, and the InvID and InvCntry fields would not exist in the details table at all (since that would be redundant, and they can be found by linking to INV-TABLE). How are you assigning a value to the Prime field anyway? -- John W. Vinson [MVP] |
#19
|
|||
|
|||
Allow Duplicates
Hello - what would the criteria for deleting a duplicate record, e.g
PO#1, LINE 1, RECEIVED1 PO#1, LINE 1, SHIPPED1 I am looking to delete whatever is the Same PO#, Same LINE but SHIPPED(instead of Received) I only need one record or RECEIVED. thank you "Graham Mandeno" wrote: Hi Joe (?) I'm confused. You say "The primary key is the invoice number as an autonumber" but you also said that the invoice number was in the imported data, so it can't be an autonumber! I suggest you add a new autonumber field named InvoiceID and make that your primary key. Then, make InvoiceNumber and Country both required fields and make them a composite unique key. To do this, open the Indexes window (ViewIndexes) and in the first blank row, enter "InvoiceNumberCountry" as the index name and select InvoiceNumber as the field name. Then, in the index properties below, change Unique to "Yes". Now, in the next blank line, choose Country as the field name (but leave index name blank). -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "acss" wrote in message ... Hello, I am still working on this and i am curious as to how you create a unique index on a combination of invoice and country field? The primary key is the invoice number as an autonumber so the table can not have more than one primary key....can you eloborate for me please? "Noëlla Gabriël" wrote: Hi, I always take an autonumber field as PK field. You can then create another unique index on the combination of the invoice number field and the country field -- Kind regards Noëlla "acss" wrote: i have a table for invoices and at times i receive a duplicate invoice number from another country. How do i set up a primary key (InvoiceID) and a secondary key (country) that would allow a duplicate entry only if from a different country? Thanks |
#20
|
|||
|
|||
Allow Duplicates
Hi JUNEBUG
You will need a query something like this: Delete from tblOrders as O WHERE O.Status"SHIPPED" and Exists (Select PO from tblOrders as X where X.PO=O.PO and X.Line=O.Line and X.Status="SHIPPED") In plain English: Delete from the table any record whose status is not "SHIPPED" if there is a "SHIPPED" record in the table for the same PO and Line number. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "JUNEBUG" wrote in message ... Hello - what would the criteria for deleting a duplicate record, e.g PO#1, LINE 1, RECEIVED1 PO#1, LINE 1, SHIPPED1 I am looking to delete whatever is the Same PO#, Same LINE but SHIPPED(instead of Received) I only need one record or RECEIVED. thank you "Graham Mandeno" wrote: Hi Joe (?) I'm confused. You say "The primary key is the invoice number as an autonumber" but you also said that the invoice number was in the imported data, so it can't be an autonumber! I suggest you add a new autonumber field named InvoiceID and make that your primary key. Then, make InvoiceNumber and Country both required fields and make them a composite unique key. To do this, open the Indexes window (ViewIndexes) and in the first blank row, enter "InvoiceNumberCountry" as the index name and select InvoiceNumber as the field name. Then, in the index properties below, change Unique to "Yes". Now, in the next blank line, choose Country as the field name (but leave index name blank). -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "acss" wrote in message ... Hello, I am still working on this and i am curious as to how you create a unique index on a combination of invoice and country field? The primary key is the invoice number as an autonumber so the table can not have more than one primary key....can you eloborate for me please? "Noëlla Gabriël" wrote: Hi, I always take an autonumber field as PK field. You can then create another unique index on the combination of the invoice number field and the country field -- Kind regards Noëlla "acss" wrote: i have a table for invoices and at times i receive a duplicate invoice number from another country. How do i set up a primary key (InvoiceID) and a secondary key (country) that would allow a duplicate entry only if from a different country? Thanks |
|
Thread Tools | |
Display Modes | |
|
|