none
Primary Key

    Question

  • Hello to all,

    I may start working on in a company, they want me to create a database.

    The company is a healthcare company,

    I want to use member ID as a primary key, but the point is that spouses have the same ID number.

    If I want to use a composite primary key (ID + first Name + Last Name + DOB), then I have read that the primary key is better to be made of one field rather than the combination of several fields.

    Do you think what is the best approach here?

    Your help is greatly appreciated.

    GGGGGNNNNN


    GGGGGNNNNN

    Tuesday, February 05, 2013 4:23 AM

Answers

  • I would suggest to use a composite primary key (ID + member code) where member code is a 2 digit text.  

    The Head Of Household designated as 01, spouse/partner as 02, with offspring/children as 11, 12, 13.  Grandparents maybe 91, 92, 93, an 94.  Aunts and uncles 51, 52, 53, ...

    • Marked as answer by GGGGGNNNNN Thursday, February 07, 2013 1:01 AM
    Tuesday, February 05, 2013 4:52 AM
  • An other option is to create two tables. Table 1 is Membership (MemberID, Startdate, Enddate, whatever........), and a second table for Members. Key could be autonumber and MemberID as foreign key.


    Groeten, Peter http://access.xps350.com/

    • Marked as answer by GGGGGNNNNN Thursday, February 07, 2013 4:14 AM
    Tuesday, February 05, 2013 7:11 AM
  • FirstTable --

    ClientID – autonumber – primary key

    Policy – text (This defines the household that are the clients)

    StartDate –

    EndDate –

    Annual – number – currency – (Premium)

    Payment – text – Wkly, BiWkly, Montly, … Yearly)

    Addr1

    Addr2

    City

    ST

    ZIP

    Cmt – memo

    SecondTable –

    MemberID - autonumber – primary key

    ClientID – number – long integer - foreign key

    FamilyID – text – (Head Of Household designated as 01, spouse/partner as 02, with offspring/children as 11, 12, 13.  Grandparents maybe 91, 92, 93, an 94.  Aunts and uncles 51, 52, 53, ...)

    LName –

    FName –

    MI –

    Suffix – Jr, Sr, II, III, etc.

    Gender

    DOB – DateTime

    PhoneRes – text

    PhoneOfc –

    PhoneMbl

    Fax

    ClientID and FamilyID as composite unique index.

    • Marked as answer by GGGGGNNNNN Thursday, February 07, 2013 4:12 AM
    Wednesday, February 06, 2013 2:55 AM
  • Sorry for jumping in.

    GGGGGNNNNN, I think we can treat the Member ID as the ID for each family. Please have a look at the following structure. 

    <tfoot></tfoot>
    tbHealthCenter
    ID  MemberIDForEachFamily  Name of the Family
    1 10002  Smith
    2 98000  Green

    Here MemberIDForEachFamily is a unique number field. Actually, it is Member ID. I just named it in this way to help understand. Name of the Family is only a text field to help remember the family name. ID is autonumber. 

    <tfoot></tfoot>
    tbPersonsInEachFamily
    ID  PersonIDInEachFamily  MemberIDForEachFamily  FirstName  LastName DOB
    1 1 10002  Tom  Smith 2/6/2013
    2 1 98000  Tim  Green 2/6/2013
    3 2 98000  Lucy  Green 2/6/2013

    Here each person belongs to a family. In the table for each person, we add the MemberIDForEachFamily. 

    ID -- AutoNumber. PersonIDInEachFamily-- Number. 

    When we add a record for a person, we first check if there is the same MemberIDForEachFamily in the tbHealthCenter. If there is one, we added 1 with the latest record's PersonIDInEachFamily for the new record.

    Hope it helps.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 06, 2013 7:20 AM
    Moderator
  • I now start to understand.

    If I'm coorect then you want to ensure that, if a new file is delivered you only add the new records and update the existing ones.

    In this case you don't need a primary key (or if you have one you shouldn't care about this). What you need is a compound Unique Key over the fields that make the record unique. This could be the MemberID and the DOB (as far as I've seen). Of course, if you want, you can create this compound Unique Key as compund primary key. Also a primary key can have more than one field. You don't have to concatenate something into a artificial primary key that is based on a single field.

    So now import your records of the first file in a table, let's it call TEMP_IMPORT. Don't define any indexes for this table at all. This is only a temporary table.

    Then fill your target table Members with all records.

    Now you create a UNIQUE INDEX on the fields MemberID and DOB.

    When the new file is coming you first delete all records from TEMP_IMPORT and import the file into this TEMP_IMPORT

    Now you INSERT the records into Members that aren't in there yet:

    INSERT INTO Members (MemberID, FirstName, LastName, DOB, DispositionText)
    SELECT MemberID, FirstName, LastName, DOB, DispositionText
    FROM TEMP_IMPORT T
    WHERE NOT EXISTS (SELECT * FROM Members M WHERE M.MemberID = T.MemberID AND M.DOB = T.DOB)

    Now you have all new records inside. You may now also want to update the existing records with the new data in the received file. You can do this also with an UPDATE statement to the table Members

    UPDATE Members INNER JOIN
    TEMP_IMPORT AS T ON
    Members.DOB = T.DOB AND Members.MemberID = T.MemberID 
    SET Members.FirstName = T.FirstName,
    Members.LastName = T.LastName, 
    Members.DispositionText = T.DispositionText;

    As a result you now have a consolidated table Members where you can do your analysis of the DispositionText.

    If you now want for example see all disposition texts that you received for the Member 10002 then just use a query like

    SELECT MemberID, DOB, DispositionText
    FROM Members
    WHERE MemberID = '10002'

    and you get a list of all disposition texts of this MemberID.

    HTH

    Henry





    Wednesday, February 06, 2013 8:52 AM
  • Use Ranking in a group.   Instead of Points use DOB --

    SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM YourTable Q1
          WHERE Q1.[Group] = Q.[Group]
            AND Q1.Points >= Q.Points) AS Rank
    FROM YourTable AS Q
    WHERE ((((SELECT COUNT(*) FROM YourTable Q1
          WHERE Q1.[Group] = Q.[Group]
            AND Q1.Points >= Q.Points)+1)<=5))
    ORDER BY Q.Group, Q.Points DESC;

    • Marked as answer by GGGGGNNNNN Tuesday, February 12, 2013 4:10 AM
    Thursday, February 07, 2013 1:51 AM
  • M is an alias for Members. You could also write:

    FROM Members As M WHERE

    Same for T. This Alias is for lazy people like me that don't want to write the whole table name again and again. You then use the Alias instead of the table name like in:

    M.MemberID = T.MemberID

    Without Alias you would have to write:

    Members.MemberID = TEMP_IMPORT.MemberID

    Henry

    • Marked as answer by GGGGGNNNNN Sunday, February 10, 2013 1:59 AM
    Thursday, February 07, 2013 5:06 AM
  • Did you develop your 2nd table?   Post SQL of a select query of it.
    • Marked as answer by GGGGGNNNNN Tuesday, February 12, 2013 4:21 AM
    Monday, February 11, 2013 4:49 AM

All replies

  • I would suggest to use a composite primary key (ID + member code) where member code is a 2 digit text.  

    The Head Of Household designated as 01, spouse/partner as 02, with offspring/children as 11, 12, 13.  Grandparents maybe 91, 92, 93, an 94.  Aunts and uncles 51, 52, 53, ...

    • Marked as answer by GGGGGNNNNN Thursday, February 07, 2013 1:01 AM
    Tuesday, February 05, 2013 4:52 AM
  • Thank you for the help.

    How can come to that point or what are the steps?

    Should I count MemberID and if it is more than 2, then I need to concatenate 01, 02 and so on?

    Please let me know.

    Regards,

    GGGGGNNNNN


    GGGGGNNNNN

    Tuesday, February 05, 2013 5:36 AM
  • An other option is to create two tables. Table 1 is Membership (MemberID, Startdate, Enddate, whatever........), and a second table for Members. Key could be autonumber and MemberID as foreign key.


    Groeten, Peter http://access.xps350.com/

    • Marked as answer by GGGGGNNNNN Thursday, February 07, 2013 4:14 AM
    Tuesday, February 05, 2013 7:11 AM
  • Hi GGGGGNNNNN

    "GGGGGNNNNN" schrieb im Newsbeitrag news:2ee03e2a-2592-421b-8ee6-61bf3e0ecca5@communitybridge.codeplex.com...

    I want to use member ID as a primary key, but the point is that spouses
    have the same ID number.

    Your approach is IMNSHO wrong.
    You need a table Members with a MemberID as primary key. This is actually not the Members as persons, but the legal member that has a contract with you and part of this legal member can be several humans (or even objects). Then you need a table InsuredPerson with an ID (Autonumber prefered) as primary key. Finally the table InsuredPerson need to have a ForeignKey MemberID that is referencing the table Members and in there the primary key MemberID. The table Members probably also has other stuff inside like Invoice Date, PlanID (referencing to the plan the Member has booked), the Member address (where to send the policy and the bills) and so on. The table InsuredPerson may have the name, birthday, phonenumber, address, gender, insuredSince, and so on in it.

    BTW: I named it Insure

    HTH
    Henry

    Tuesday, February 05, 2013 10:22 AM
  • I like your idea but mixing mine with it.   The table1 to have information pertaining to company/client such as policy number, start & end dates, riders, etc.   Then table2 to use my 01, 02, 11, 12, 13,91, 92, .. 
    Tuesday, February 05, 2013 8:24 PM
  • This doesn't work, because all the member of the family should be in one table. An agent in a call center calls members (members might be the members of a family) and each one gives a different response. I need to do my analysis on all the responses from all family members.

    If I wanted to keep track of members, having two tables would work, but I need to have all members in one place and file.

    GGGGGNNNNN


    GGGGGNNNNN

    Tuesday, February 05, 2013 8:51 PM
  • I need to do my analysis on all the members in one place, I don't want to keep track of members, that way two tables was the solution, but I need all the members to be in one file/table.

    Thanks for the reply,

    GGGGGNNNNN


    GGGGGNNNNN

    Tuesday, February 05, 2013 8:53 PM
  • I like this idea as marking 01 and 02 and 03, but I need to keep all the members in one table as 1222-01, 1222-02, 1222-03.

    If the count of members more than 1, then I have to update that member ID to 01, 02 and so on...

    How can I reach to this point? If you give the steps along with SQL code I really appreciate it.

    Regards,

    Guity


    GGGGGNNNNN

    Tuesday, February 05, 2013 8:58 PM
  • The 2nd table would have autonumber primary key, foreign key related to table1, and field form family member number (01, 02, 03, ).   Create a compound index of the foreign key and member number set to unique.
    Tuesday, February 05, 2013 9:46 PM
  • Are you saying that the first table should have the member Id of the first insure person and that should serve as the Primary Key for this table is and the second table should have auto number as primary key for the spouse and offspring and children?

    GGGGGNNNNN


    GGGGGNNNNN

    Tuesday, February 05, 2013 10:49 PM
  • Does this mean that I have to combine MemberID and 01 and 02 from the second table?

    I join two tables through Member ID which is a primary key for the first table and foreign key for the second table, then how does this create member ID when there is only one member ID and when the member ID has member families?

    This is my SQL without having to create another table:

    SELECT Query1.ID, Query1.CountOfName, IIf([CountOfName]=2,[ID]+"-1",[ID]) AS Expr1
    FROM Query1;
    GGGGGNNNNN


    GGGGGNNNNN

    Tuesday, February 05, 2013 10:58 PM
  • Hi GGGGGNNNNN

    No you don't. This is a relational Database and you should accept it's rules else you will get in troubles later. If you need all the persons for a member in one file then you simply write a query that delivers this or create a form, that lists all the persons in a subform. That's how relational databases work.

    Henry

    Wednesday, February 06, 2013 2:10 AM
  • FirstTable --

    ClientID – autonumber – primary key

    Policy – text (This defines the household that are the clients)

    StartDate –

    EndDate –

    Annual – number – currency – (Premium)

    Payment – text – Wkly, BiWkly, Montly, … Yearly)

    Addr1

    Addr2

    City

    ST

    ZIP

    Cmt – memo

    SecondTable –

    MemberID - autonumber – primary key

    ClientID – number – long integer - foreign key

    FamilyID – text – (Head Of Household designated as 01, spouse/partner as 02, with offspring/children as 11, 12, 13.  Grandparents maybe 91, 92, 93, an 94.  Aunts and uncles 51, 52, 53, ...)

    LName –

    FName –

    MI –

    Suffix – Jr, Sr, II, III, etc.

    Gender

    DOB – DateTime

    PhoneRes – text

    PhoneOfc –

    PhoneMbl

    Fax

    ClientID and FamilyID as composite unique index.

    • Marked as answer by GGGGGNNNNN Thursday, February 07, 2013 4:12 AM
    Wednesday, February 06, 2013 2:55 AM
  • Thank you very much  for the help,

    Member ID 10002

    Member ID 98000

    Member ID 98000

    Member ID 20046

    Please tell me what the query is? I don't need a form. I need to be in a table.

    GGGGGNNNNN


    GGGGGNNNNN

    Wednesday, February 06, 2013 4:18 AM
  • Please tell me what the query is? I don't need a form. I need to be in a table.

    I do not understand what query you want.  You would have a form/subform for Policy/Clients using Master/Child links set.  Clients of a household consist of all those with the same MemberID.
    Wednesday, February 06, 2013 4:29 AM
  • Hello Karl,

    I have only MemberID, I don't have ClientID. I don't want to create an extra field. It is simple: I have received a file that have MemberID, FirstName, LastName, DOB, Disposition from a Healthcare client.

    Now, I want to set the MemberID as Primary Key, but MemberID is duplicated. Now I want to make it unique.

    One method is to create a composite key by MemberID+DOB

    I like your idea that we can add 01, 02 , 03 and so on to duplicate values

    If first table has these fields

    MemberID, FirstName, LastName, DOB, Disposition

    and Member ID is:

    Member ID 10002

    Member ID 98000

    Member ID 98000

    Member ID 20046

    How can I add 01, 02, 03, 04 when the MemberID is duplicate?


    GGGGGNNNNN

    Wednesday, February 06, 2013 4:32 AM
  • You will need to use the table you received to create 2 tables.

    See the table structure I posted.  The first table does not have name-DOB-Sex-etc.

    Wednesday, February 06, 2013 4:43 AM
  • Hello Karl,

    MemberID can't be Autonumber, because It comes as 5 character text from the healthcare, I have no control over it.

    The table doesn't have a field as a policy. If I want to add another field, Access may not allow me to do, Access may display a message as the system is out of resources.

     you have brought in MemberID and ClientID into discussion.

    The table should have only these fields:

    MemberID, FirstName, LastName, DOB, Disposition. This is business ruled. In this context I have to find MemberIDs that are more than one and attach 01, 02, and .....

    Thank you for all your effort.


    GGGGGNNNNN

    Wednesday, February 06, 2013 6:49 AM
  • I think this reply was meant to somebody else in this discussion. I have to resubmit to someone else.

    Regards,

    Guity


    GGGGGNNNNN

    Wednesday, February 06, 2013 6:52 AM
  • Hello Henry,

    What query can I write? Form only shows one record at a time. I need the tables to place my queries on it for analysis.

    These are my fields:

    Member ID 10002

    Member ID 98000

    Member ID 98000

    Member ID 20046

    What query can I do on these to change 98000 to a uniquer Value? Such as concatenating 01, 02 to member ID when there is more than one memberID.

    I appreciate your help,

    GGGGGNNNNN


    GGGGGNNNNN

    Wednesday, February 06, 2013 6:58 AM
  • We will not do any progress as long as you don't tell us exactly what you have and what you want to do with it.

    What I know so far is that you get a file with records that contain a MemberID that is not unique and that you want to create a Primary Key to this imported records.

    What I know from my experience is that this doesn't make sense. You have to split up your received files into a table Members containing the memberIDs and a primary key about this member IDs and then a second child table containing the persons that are referencing this memberID.

    But now first of all tell us, what you want to do. What is the desired output that makes you believe you have to use MemberID in a primary key and that you can't split up your table in to a stanrard normalized data model and build a query that runs a join over these two tables.

    Finally: Of course your form will only show one member id record. But in the Subform you will have every person that has the same member id as the record in the main form. That's the way it works in relational databases.

    Henry

    Wednesday, February 06, 2013 7:19 AM
  • Sorry for jumping in.

    GGGGGNNNNN, I think we can treat the Member ID as the ID for each family. Please have a look at the following structure. 

    <tfoot></tfoot>
    tbHealthCenter
    ID  MemberIDForEachFamily  Name of the Family
    1 10002  Smith
    2 98000  Green

    Here MemberIDForEachFamily is a unique number field. Actually, it is Member ID. I just named it in this way to help understand. Name of the Family is only a text field to help remember the family name. ID is autonumber. 

    <tfoot></tfoot>
    tbPersonsInEachFamily
    ID  PersonIDInEachFamily  MemberIDForEachFamily  FirstName  LastName DOB
    1 1 10002  Tom  Smith 2/6/2013
    2 1 98000  Tim  Green 2/6/2013
    3 2 98000  Lucy  Green 2/6/2013

    Here each person belongs to a family. In the table for each person, we add the MemberIDForEachFamily. 

    ID -- AutoNumber. PersonIDInEachFamily-- Number. 

    When we add a record for a person, we first check if there is the same MemberIDForEachFamily in the tbHealthCenter. If there is one, we added 1 with the latest record's PersonIDInEachFamily for the new record.

    Hope it helps.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 06, 2013 7:20 AM
    Moderator
  • Hello Henry,

    We will not do any progress as long as you don't tell us exactly what you have and what you want to do with it.

    I receive a file from a healthcare and It has a lot of fields, the fields that are important in this discussion are:

    Member ID ( 5 character text)

    FirstName

    LastName

    DOB

    Disposition Text

    I need to do analysis with the answers I have in Disposition field.


    What I know so far is that you get a file with records that contain a MemberID that is not unique and that you want to create a Primary Key to this imported records. Correct

    What I know from my experience is that this doesn't make sense. You have to split up your received files into a table Members containing the memberIDs and a primary key about this member IDs and then a second child table containing the persons that are referencing this memberID

    Are you saying for example for a memberID such as 10002, I have to assign an auto number primary key and does auto number will start at 1?

    How about if I receive another file from the healthcare, should I start assign auto number as primary key to this list? what number should I start my autonumber? will the autonumber start at 1? what happens if I want to append these two tables?

    and then a second child table containing the persons that are referencing this memberID

    My first table has these fields:

    MemberID 10002

    MemberID 98000

    MemberID 98000

    MemberID 20046

    Do You want to assign an auto number as primary key to each field?

    . What is the desired output that makes you believe you have to use MemberID in a primary key and that you can't split up your table in to a stanrard normalized data model and build a query that runs a join over these two tables.

    I think If I receive two list from healthcare and if I use auto number as a primary key, how can I appends those table in together?

    Access will start auto numbering the second list from 1? This is one reason, There are more reasons.

    Also, there is another person that has responded to this forum. Let me try what he says and I will get back to you and to him. I don't know if you see his in this tread.

    Thank you very much for all, GGGGGNNNNN




    GGGGGNNNNN

    Wednesday, February 06, 2013 8:15 AM
  • I now start to understand.

    If I'm coorect then you want to ensure that, if a new file is delivered you only add the new records and update the existing ones.

    In this case you don't need a primary key (or if you have one you shouldn't care about this). What you need is a compound Unique Key over the fields that make the record unique. This could be the MemberID and the DOB (as far as I've seen). Of course, if you want, you can create this compound Unique Key as compund primary key. Also a primary key can have more than one field. You don't have to concatenate something into a artificial primary key that is based on a single field.

    So now import your records of the first file in a table, let's it call TEMP_IMPORT. Don't define any indexes for this table at all. This is only a temporary table.

    Then fill your target table Members with all records.

    Now you create a UNIQUE INDEX on the fields MemberID and DOB.

    When the new file is coming you first delete all records from TEMP_IMPORT and import the file into this TEMP_IMPORT

    Now you INSERT the records into Members that aren't in there yet:

    INSERT INTO Members (MemberID, FirstName, LastName, DOB, DispositionText)
    SELECT MemberID, FirstName, LastName, DOB, DispositionText
    FROM TEMP_IMPORT T
    WHERE NOT EXISTS (SELECT * FROM Members M WHERE M.MemberID = T.MemberID AND M.DOB = T.DOB)

    Now you have all new records inside. You may now also want to update the existing records with the new data in the received file. You can do this also with an UPDATE statement to the table Members

    UPDATE Members INNER JOIN
    TEMP_IMPORT AS T ON
    Members.DOB = T.DOB AND Members.MemberID = T.MemberID 
    SET Members.FirstName = T.FirstName,
    Members.LastName = T.LastName, 
    Members.DispositionText = T.DispositionText;

    As a result you now have a consolidated table Members where you can do your analysis of the DispositionText.

    If you now want for example see all disposition texts that you received for the Member 10002 then just use a query like

    SELECT MemberID, DOB, DispositionText
    FROM Members
    WHERE MemberID = '10002'

    and you get a list of all disposition texts of this MemberID.

    HTH

    Henry





    Wednesday, February 06, 2013 8:52 AM
  • I created the tables and with a query I could get MemberID and Family ID, so the primary key will MemberID+FamilyID.

    If I have 3000 duplicated MemberID, should I put them in a second table?

    without doing data entry, how can I assign 1, 2, 3 to family member in the second table?

    For example:

    I need my second table look like this

    First field         Second filed

    MemberID        FamilyID

    98000                  1

    98000                  2

    Thank you very much for your help.

    GGGGGNNNNN

    GGGGGNNNNN

    Thursday, February 07, 2013 1:01 AM
  • Use Ranking in a group.   Instead of Points use DOB --

    SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM YourTable Q1
          WHERE Q1.[Group] = Q.[Group]
            AND Q1.Points >= Q.Points) AS Rank
    FROM YourTable AS Q
    WHERE ((((SELECT COUNT(*) FROM YourTable Q1
          WHERE Q1.[Group] = Q.[Group]
            AND Q1.Points >= Q.Points)+1)<=5))
    ORDER BY Q.Group, Q.Points DESC;

    • Marked as answer by GGGGGNNNNN Tuesday, February 12, 2013 4:10 AM
    Thursday, February 07, 2013 1:51 AM
  • Henry,

    I do agree with you that I don't need a primary key or I can make a composite primary key,

    I am trying to learn your SQL,

    WHERE NOT EXISTS (SELECT * FROM Members M WHERE M.MemberID = T.MemberID AND M.DOB = T.DOB)

    What does M stand for in the top SQL.

    I will work on the update statement and I will get back to you.

    I will start a new assignment Monday morning and I need help.

    Thank you for your all effort that you did on this question. I don't have enough words to thank you....

    GGGGGNNNNN


    GGGGGNNNNN

    Thursday, February 07, 2013 4:11 AM
  • Karl,

    I want to thank you for all the help you gave me through this tread. I really don't have the words. I will start a new assignment on this coming Monday and I definitely need more help.

    GGGGGNNNNN


    GGGGGNNNNN

    Thursday, February 07, 2013 4:14 AM
  • Hello Karl,

    What is Q, Group, points and rank in this example?

    Regards,

    GGGGGNNNNN


    GGGGGNNNNN

    Thursday, February 07, 2013 4:20 AM
  • When you use a table more than once in a query you have to use an alias, in this case 'Q' is the alias.  

    I posted an example of a 'Ranking in a group' query.  Group would be your MemberID, instead of Points use DOB, and Rank would be the individual numbering output.

    You can join this query to the people table and update the field for each individual.   

    Thursday, February 07, 2013 4:28 AM
  • M is an alias for Members. You could also write:

    FROM Members As M WHERE

    Same for T. This Alias is for lazy people like me that don't want to write the whole table name again and again. You then use the Alias instead of the table name like in:

    M.MemberID = T.MemberID

    Without Alias you would have to write:

    Members.MemberID = TEMP_IMPORT.MemberID

    Henry

    • Marked as answer by GGGGGNNNNN Sunday, February 10, 2013 1:59 AM
    Thursday, February 07, 2013 5:06 AM
  • Hello,

    Can you please tell me what this part do?

    FROM YourTable AS Q
    WHERE ((((SELECT COUNT(*) FROM YourTable Q1
          WHERE Q1.[Group] = Q.[Group]
            AND Q1.Points >= Q.Points)+1)<=5))

    Is this a self join?

    What I need is that I need to insert a field, when MemberID is one, the field should show 1 and when MemberID is duplicated the first member is 1 and the second member is 2, and so on...

    I don't want to take the MemberID as a primary key, but I want to have a column populated by 1, 2, or 3 based on the count of MemberID?

    I may have to be able to insert a field and do the data entry, but I need some more advance way.

    GGGGGNNNNN


    GGGGGNNNNN

    Sunday, February 10, 2013 2:05 AM
  • Henry,

    I need the new data to be added to the old data. I need to keep the old data.

    Regards,

    GGGGGNNNNN


    GGGGGNNNNN

    Sunday, February 10, 2013 2:07 AM

  •  FROM YourTable AS Q
    WHERE ((((SELECT COUNT(*) FROM YourTable Q1
          WHERE Q1.[Group] = Q.[Group]
            AND Q1.Points >= Q.Points)+1)<=5))

    Is this a self join?

    What I need is that I need to insert a field, when MemberID is one, the field should show 1 and when MemberID is duplicated the first member is 1 and the second member is 2, and so on...

    I don't want to take the MemberID as a primary key, but I want to have a column populated by 1, 2, or 3 based on the count of MemberID?

    I may have to be able to insert a field and do the data entry, but I need some more advance way.

    Omit the part in bold as it was to limit the number to equal or less than 5.

    You add the field and then run an update.

    Sunday, February 10, 2013 7:05 PM
  • can you be more clear. No data is deleted, only updated.

    What do you mean by data? Do you mean you have to add the DispositionText to the already existing disposition text? If so just use:

    UPDATE Members INNER JOIN
    TEMP_IMPORT AS T ON
    Members.DOB = T.DOB AND Members.MemberID = T.MemberID 
    SET Members.FirstName = T.FirstName,
    Members.LastName = T.LastName, 
    Members.DispositionText = (Members.DispositionText + Chr(13) + Chr(10)) & T.DispositionText;

    instead

    Henry

    Monday, February 11, 2013 2:19 AM
  • Did you develop your 2nd table?   Post SQL of a select query of it.
    • Marked as answer by GGGGGNNNNN Tuesday, February 12, 2013 4:21 AM
    Monday, February 11, 2013 4:49 AM
  • Karl,

    I couldn't believe that, I mean it is beyond belief. ....It worked.

    However I didn't understand some of its part.

    I switched between SQL view and design view, I try to understand it.

    Thank you so very much,

    GGGGGNNNNN


    GGGGGNNNNN

    Tuesday, February 12, 2013 4:21 AM