Duplicates
in [General]
|
From: tsquared1518 on 27 Jan 2010 10:42 I have a table with the PO as the primary key. The problem is there can be more than one item on a single PO. I inspect each item as it comes in, and record the results in the database. How do I set it up so that I can enter multiple items on the same PO? I tried changing the primary key to yes(duplicates ok), but it wont let me. Can someone please help me? Thanks in advance!!
From: Jerry Whittle on 27 Jan 2010 11:09 You need a second table for the Items and storing information about them. In this second table the PO would be the foreign key field. You would then link these two tables together. You want to have the two tables linked in the Relationship Window with Referiential Integrity enabled. Next create a form based on the PO table. On this form create a subform based on the Items table. Use this form for data input and updates. In other words, don't go directly to either table. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "tsquared1518" wrote: > I have a table with the PO as the primary key. The problem is there can be > more than one item on a single PO. I inspect each item as it comes in, and > record the results in the database. How do I set it up so that I can enter > multiple items on the same PO? I tried changing the primary key to > yes(duplicates ok), but it wont let me. Can someone please help me? Thanks in > advance!!
From: PvdG42 on 27 Jan 2010 11:14 "tsquared1518" <tsquared1518(a)discussions.microsoft.com> wrote in message news:5D2718E8-FF6E-4624-966E-A61568FE947C(a)microsoft.com... > I have a table with the PO as the primary key. The problem is there can be > more than one item on a single PO. I inspect each item as it comes in, and > record the results in the database. How do I set it up so that I can enter > multiple items on the same PO? I tried changing the primary key to > yes(duplicates ok), but it wont let me. Can someone please help me? Thanks > in > advance!! You need to learn about basic relational theory and design. I'm not trying to give you a hard time, but to put a product like Access to good use, you have to be able to set up tables and relationships correctly. There are many articles, tutorials, etc. available free (use your search engine) that will help you. Here are a few examples quickly found with Google: http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx http://www.geekgirls.com/databases_from_scratch_3.htm http://support.microsoft.com/kb/283698 For your situation, you'll probably need three tables. PO, with the Purchase Order number as primary key; Item, using a unique item number as primary key, and an intersection table to identify the item(s) that are part of a given PO. Typically, the primary key of the intersection table is the combination of a PO number and an item number. From here, I could continue typing to explain why three tables are needed and how the three tables are related to each other. Rather than that, you should use the tutorials to discover how to design your database correctly. If after studying the tutorials and attempting a design, ask specific questions here and I'm sure you'll get useful feedback.
|
Pages: 1 Prev: Vá Next: Using look-up wizard to create a mulivalued "checklist" |