none
Quick question about one primary key which needs more fields RRS feed

  • Question

  • Hello there,

    I am using Access 07-10, and unfortunately I am quite new to it. I have been setting up a simple database for small company which basically needs to records the quotations it sends out, with different products (materials) per quotation. 

    My issue is I have made a table called 'Quotations' where the primary key is the quotation number. The quotations all have different kinds of materials in different sizes, I want to put these all under one primary key but I am not sure how to proceed. The other tables will be a sales people list, and client list. The goal is to be able to query these tables with for example which materials are sold more often over different quotations.

    My apologies for probably a very rudimentary question, I am studying several books about Access but didn't manage to find this out yet.

    EDIT: I want to thank everyone for the replies, I am glad to find out this community is very helpful. One additional questions for readers, what literature would you recommend to learn about Access?


    • Edited by Juliandu Friday, October 30, 2015 2:08 AM
    Thursday, October 29, 2015 4:37 AM

All replies

  • Hello Juliandu,

    Instead of having only a "Quotations" table, I would suggest to create two other tables and create relationships with them. On this way, you can surely structure the query you want. Please look into the image shown below. On each table, I identified fields that you may need in your application.

    Sincerely,

    Ed


    Dr. Mindweb

    Thursday, October 29, 2015 6:11 AM
  • Hi,

    Create or modify a primary key

    Set the primary key

    For a primary key to work well, the field must uniquely identify each row, never contain an empty or null value, and rarely (ideally, never) change. To set the primary key:

    1. Open the database that you want to modify.

    2. In the Navigation Pane, right click the table in which you want to set the primary key and, on the shortcut menu, click Design View.

      <include item="tip" style="box-sizing:border-box;">TIP </include>  If you don't see the Navigation Pane, press F11 to display it.

    3. Select the field or fields that you want to use as the primary key.

      To select one field, click the row selector for the field you want.

      To select more than one field, hold down CTRL and then click the row selector for each field.

    4. On the Design tab, in the Tools group, click Primary Key.


    Michał

    Thursday, October 29, 2015 9:24 AM
  • Dear Ed,

    Thank you very much for your reply. 

    I will start building the database according to your advice. Will provide feedback as soon as I can.

    Once again, thanks a lot for the effort.


    Thursday, October 29, 2015 9:55 AM
  • In the above example model I would usually make the tblQuoteItems primary key be the combination of QuotationId and CatalogId, because you do not want any rows with duplicated values for those combinations. The PK doesn't need to be an ID, and in cases where there is a good 'natural' PK attribute or attributes I find it more effective to use that natural key. Similarly if your catalog items already have a fixed catalog number, that might be a good PK attribute instead of an ID. For quotations, maybe QuoteNumber is unique and stable (never changes), or maybe the ID can be used as the quote number.

    Paul

    Thursday, October 29, 2015 1:00 PM
  • In the above example model I would usually make the tblQuoteItems primary key be the combination of QuotationId and CatalogId, because you do not want any rows with duplicated values for those combinations. The PK doesn't need to be an ID, and in cases where there is a good 'natural' PK attribute or attributes I find it more effective to use that natural key. Similarly if your catalog items already have a fixed catalog number, that might be a good PK attribute instead of an ID. For quotations, maybe QuoteNumber is unique and stable (never changes), or maybe the ID can be used as the quote number.

    Hi,

    The shorter the PK in terms of bytes, the faster the retrieval goes. This is for me one of the reasons to use an artificial PK.

    An other reason for an artificial PK is that, in making joins between tables, you only have to account for one - systematic - field. In this way you can automate relations between tables to a very high degree.

    Just my way of working...

    Imb.

    Thursday, October 29, 2015 1:38 PM
  • I don't see how an Id PK gives faster retrieval. I would expect to retrieve QuoteItems by either quote or category. The Id would just be an additional attribute of no value, taking up space. I agree with the point about smaller PK being simpler for subsequent child table foreign keys, but I wouldn't see 2 attributes as a problem there and I don't know that the QuoteItem table would have any child tables.

    Paul

    Thursday, October 29, 2015 1:42 PM
  • I don't see how an Id PK gives faster retrieval. I would expect to retrieve QuoteItems by either quote or category. The Id would just be an additional attribute of no value, taking up space. I agree with the point about smaller PK being simpler for subsequent child table foreign keys, but I wouldn't see 2 attributes as a problem there and I don't know that the QuoteItem table would have any child tables.

    I agree.  A surrogate key in a table which models a many-to-many relationship type, and is not a referenced table in a one-to-many relationship type merely introduces an additional overhead.  A small one, but nevertheless an unnecessary one.


    Ken Sheridan, Stafford, England

    Thursday, October 29, 2015 2:05 PM
  • Dear Ed,

    I have been implementing the tables you suggested. However one question does come up now:

    I have the Table with Quotations with Fields:

    - Quotation NO (PK)
    - Date
    - Sales ID (refers to employee table, simple enough)
    - Client ID (refers to client table, simple enough)
    - Details ID

    Second Table : QuoteItems with Fields:

    - Details ID (PK, related with Details ID from Quotations tbl)
    - Price
    - Quantity
    - Unit
    - Product Catalog No.

    Third Table: Product Catalog

    - Product Catalog No. (PK related with Product Catalog No from Quoteitems tbl)
    - Item
    - Description
    - Size

    My problem now is, I have a quotation with 4 different products, prizes, quantities etc.
    How do I create multiple 'QuoteItems' entries which all relate to the same 'Quotation'?

    I think my mistake is making 'Details ID' as my primary key in 'QuoteItems tbl' so I can't duplicate it. But if I don't pick this as primary key then what do I take? I don't have any numbers or tags which would make sense as primary key expect for the 'Details ID' which lead from the 'Quotations' tab.

    Thanks again for the help, and excuse my ignorance.

    Friday, October 30, 2015 3:37 AM
  • Hello Juliandu,

    The tblQuoteItems on my example is the table where all the quote item transactions happen. It is the reason why tblQuotations has One to Many relationships with tblQuoteItems.

    I may suggest to follow the steps below to test how the relationships I presented on my sample will work.

    1. Create a main form (frmQuotations) and use tblQuotations as its record source.

    2. Create a sub-form (sfrmQuoteItems) in Datasheet format with tblQuoteItems as its record source.

    3. Place the sub-form into the main form and set its Link Master and Child into ID and QuotationID.

    4. View the form and start entering the values.

    "How do I create multiple 'QuoteItems' entries which all relate to the same 'Quotation'?"

    The steps above would surely give you an idea on how multiple quote items are entered into the database with its QuoteID taken from the ID of the main form.

    Note: You need to populate records first into tblCatalog so that you have values to look into when you enter values for the CatalogNo field in sfrmQuoteItems.


    Dr. Mindweb


    • Edited by Dr. Mindweb Friday, October 30, 2015 6:02 AM
    Friday, October 30, 2015 5:25 AM
  • Thank you very much!

    Going to try that first chance I get.


    Friday, October 30, 2015 6:49 AM
  • Since the PK of Quotations is QuotationNumber, that's the related FK attribute in QuoteItems. You can call it anything you want (although I would use the same name to keep the meaning clear), but it must be exactly the same data type and size in both the parent Quotations table and the child QuotationItems table. The one possible confusion with the data type rule is that if the parent table attribute is an Autonumber, the child table attribute must be a long integer, not an autonumber, so you can assign the appropriate values.

    There's no reason for DetailsId in the Quotation table. The relationship is that one quotation can have many items. Since every attribute can only have a single value in each data row, the relationship information is stored in the child QuotationItems table. The many items will exist as many rows in the child table, with each row identified with the QuotationNumber of it's parent quote.


    Paul

    Friday, October 30, 2015 10:12 AM
  • Note: You need to populate records first into tblCatalog so that you have values to look into when you enter values for the CatalogNo field in sfrmQuoteItems.
    A new row can of course be inserted into a referenced table by means of code in the NotInList event procedure of a combo box bound to the relevant foreign key column in a referencing table.  This can either be done transparently, where the only column to be inserted is that for which the value is entered into the combo box, or by opening a form in dialogue mode where values have to be inserted into other columns in the referenced table.

    The OP can find examples as NotInList.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    If difficulty is experienced opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates the use of this event in a variety of contexts, and also demonstrates alternative approaches where the use of this procedure is not possible e.g. with personal or geographical names such as cities, which can legitimately be duplicated.


    Ken Sheridan, Stafford, England

    Friday, October 30, 2015 12:09 PM
  • I don't see how an Id PK gives faster retrieval.

    Hi Paul,

    Assuming that keys are organized in a balanced tree model, or analogous, then the smaller the key the more record identifiers fit in a memory block, and the less hierarchical steps in the tree are necessary to come down to the right record. But to relativate, for "small" amounts of records and few relations you do not feel the difference.

    Another decisionmaking point is the use of natural PK's versus surrogate PK's. I can understand that the use of natural keys is easy to understand. In my development of an Rapid Application Development tool around Access, where the same form(s) can be populated by any recordset, it is very advantageous to use surrogate keys. But I must admit, not everyone is working on that. 

    Both are consequences of far-going generalization.

    Imb.

    Friday, October 30, 2015 6:54 PM