Share via

Table + sub table.

Anonymous
2015-02-17T16:30:23+00:00

I just started to create Access tables and now I have come to a problem.  I have a few table that some fields repeat and I believe that this is not "efficient."

Example:

               ID

               Customer

               Visit_Date

               Salesman

               Action_1

               Action_1_Date

               Action_1_Complete_Flag

               Action_2

               Action_2_Date

               Action_2_Complete_Flag

               ............

I believe that it would be better to have this:

               ID

               Customer_Code

               Visit_Date

               Salesman

                                                  ID

                                                  Customer_Code

                                                  Visit_Date

                                                  Action_Number

                                                  Action_Details

                                                  Action_Date

                                                  Action_Complete_Flag

The link should be:  Customer_Code  + Visit_Date

                         and:   Action_Number would be a sepecial "counter" (Number of existing record +1) that is not Autonumber

How would I find info on how to do this?

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ScottGem 68,830 Reputation points Volunteer Moderator
    2015-03-04T17:22:31+00:00

    How are you entering data? You should have a main form bound to tblVisit and a subform bound to tblAction and linked on VisitID.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2015-03-04T16:58:53+00:00

    I have successfully create my two tables.  The FK works only when I create the first VisitID record.  After this first record, I get:

    Was this answer helpful?

    0 comments
  3. ScottGem 68,830 Reputation points Volunteer Moderator
    2015-02-17T18:34:37+00:00

    Yes, you are correct that repeating fields are not normalized design. Your proposed design is good, but needs a bit of tweaking.

                   tblVisit

                   VisitID (PK Autonumber)

                   Customer_Code

                   Visit_Date

                   Salesman

                                                      tblAction

                                                      ActionID (PK Autonumber)

                                                      VisitID (FK)

                                                      Action_Number

                                                      Action_Details

                                                      Action_Date

                                                      Action_Complete_Flag

    PK = Primary Key, FK = Foreign Key

    See my blog on Sequential Numbering for techniques to add a sequential Number (Action_Number). 

    If you want the combination of Customer_Code and Visit_date to be unique, you can set a multi-field Unique index on the combination.

    Was this answer helpful?

    0 comments
  4. HansV 462.6K Reputation points
    2015-02-17T16:55:27+00:00

    If ID is the primary key of the first table (for example an AutoNumber field), there is no need to include Customer_Code and Visit_Date in the second table, that would be redundant. The ID field is sufficient to link a record in the second table to the first one.

    Was this answer helpful?

    0 comments