Do I have to set the primary key to include primary key fields
in [Tables]
|
Prev: What to do with 1.5 million records...
Next: Using Lookup Query with Calculated Value in Table ComboBox
From: boooney on 14 Apr 2010 11:43 John, Thanks...actually I did originally define both fields as the primary key. Basically, what I have is an invoice table, an invoice-source junction table, and a source table. What I found was that when I added more than one source to an invoice, I would get the error message. As you suggested, I do have a a form based on the "invoice" table, and a subform based on the invoice-source junction table. Here is the SQL query on which the form-subform is based: SELECT jnct_Invoice_Source.Invoice_ID, jnct_Invoice_Source.Source_ID, jnct_Invoice_Source.Amount, Source.Source_Name, jnct_Invoice_Source.Initial_Source, jnct_Invoice_Source.Init_Source_Repaid, jnct_Invoice_Source.jnct_Invoice_Source_ID FROM Source INNER JOIN (Invoices INNER JOIN jnct_Invoice_Source ON Invoices.Invoice_ID = jnct_Invoice_Source.Invoice_ID) ON Source.Source_ID = jnct_Invoice_Source.Source_ID; Thanks again for your help. "John W. Vinson" wrote: > On Tue, 13 Apr 2010 17:46:01 -0700, boooney > <boooney(a)discussions.microsoft.com> wrote: > > >I have read that "in a junction table, you need to set the primary key to > >include the primary key fields from the other two tables. " > > Well, it's a good idea, but it's not in fact obligatory. > > >If I do this, I keep getting the error message "the change you requested to > >the table were not successful because they would create duplicate values in > >the index, primary key, or relationship. > > Sounds like you defined just one of the fields as the primary key, rather than > ctrl-clicking both fields and clicking the Key icon. Both foreign keys should > have the key icon next to them in table design view - not just one of them. > > >The only way I'm entering data is through a form based on a query which > >automatically writes to all 3 tables (the junction and 2 parents) at once. > > Generally a Very Bad Idea. Why not use the tools that Access provides - a form > based on one of the "one" side tables, and a subform based on the junction > table? > > >If I remove the primary keys in the junction tables, I no longer get the > >error message. Am I going to regret this later? > > Junction tables, plural? Are there more than one? > > -- > > John W. Vinson [MVP] > . >
From: John W. Vinson on 14 Apr 2010 19:17
On Wed, 14 Apr 2010 08:43:02 -0700, boooney <boooney(a)discussions.microsoft.com> wrote: >John, > >Thanks...actually I did originally define both fields as the primary key. > >Basically, what I have is an invoice table, an invoice-source junction >table, and a source table. What I found was that when I added more than one >source to an invoice, I would get the error message. As you suggested, I do >have a a form >based on the "invoice" table, and a subform based on the invoice-source >junction >table. Here is the SQL query on which the form-subform is based: > >SELECT jnct_Invoice_Source.Invoice_ID, jnct_Invoice_Source.Source_ID, >jnct_Invoice_Source.Amount, Source.Source_Name, >jnct_Invoice_Source.Initial_Source, jnct_Invoice_Source.Init_Source_Repaid, >jnct_Invoice_Source.jnct_Invoice_Source_ID >FROM Source INNER JOIN (Invoices INNER JOIN jnct_Invoice_Source ON >Invoices.Invoice_ID = jnct_Invoice_Source.Invoice_ID) ON Source.Source_ID = >jnct_Invoice_Source.Source_ID; > >Thanks again for your help. No. Your subform is NOT "based on the invoice-source table". It is based on a Query joining all three tables. That's why you're having the difficulty! Change the Form's Recordsource to just the Invoice table - or a query sorting the invoice table by date, or invoice number, or whatever's convenient; and change the Subform's Recordsource to just jnct_Invoice_Source. Use InvoiceID as the master/child link field, and use a combo box based on Source bound to Source_ID so you can display the source name rather than the ID. -- John W. Vinson [MVP] |