CODEWITHSUNDEEP
sqlms-accessdatabase-designrelational-databasedatabase-schema
user6336899
user6336899

Reputation:

Structure of a relational database for comparing multiple dates

We have a Microsoft Access Database at work to track an ongoing list of customers. Each customer has to sign a contract with several departments - totally 13 (!) departments - for which we want to keep track about the current progress for each customer when a contract is sent and received. This structure looks similar to something like this:

    Table 1
    -------------------------------------------------------------------------------------------------------------------
    CUSTOMER_ID | ... | DEP_A_SENT | DEP_A_RECEIVED | DEP_B_SENT | DEP_B_RECEIVED | DEP_C_SENT | DEP_C_RECEIVED | ... |
    -------------------------------------------------------------------------------------------------------------------
         1      | ... | 2015-05-01 |   2015-05-03   | 2015-05-04 |   2015-05-09   | 2015-05-01 |   2015-05-05   | ... |
         2      | ... | 2015-05-01 |   2015-05-05   | 2015-05-01 |   2015-05-03   | 2015-05-13 |       ---      | ... |
         ...

In MySQL I can work with functions such GREATEST and LEAST to compare values between different columns, but in Access I have to rely for now on VBA and I think it is considered bad practice. How can I normalize and restructure my table for archieving my goals with rather simple MAX, MIN and AVGoperations? Many thanks!

Upvotes: 1

Views: 61

Answers (1)

Pieter Geerkens
Pieter Geerkens

Reputation: 11893

Simply fold your existing table into this structure:

create table TABLE_1 (
    CUSTOMER_ID     int,
    DEPARTMENT_ID   int, -- foreign key reference to DEPARTMENT table
    SENT            date,
    RECEIVED        date
);

Now you can perform the required analysis simply, and retrieve the original layout as either a Pivot report or LEFT OUTER JOIN from the DEPARTMENT table to the new TABLE_1.

Upvotes: 2

Related Questions