Linking/Bridge Table/ Or Surrogate Keys?

• yrstruly

  Hall of Fame

  Points: 3477

  More actions

  March 7, 2025 at 1:03 pm

  #4553360

  The system I'm working on is MS Access, but the principles of dimensional modeling are relevant.

  Please see the attached document for how my data is structured.

  Access does not allow me to create a column that was defined as an Auto Number as a drop-down option. Drop down displays, but the number 1 is static and does not list the entire range of Permit IDs. Do I create a linking table? please advice?

   

  CREATE TABLE tblPermitInformation (
      PermitID AUTOINCREMENT PRIMARY KEY,
      PlantID TEXT(50),
      Import_Requestor TEXT(50),
      Permit_No TEXT(50),
      Permit_appl_date DATETIME,
      Permit_valid_until DATETIME,
      Authorization_application_date DATETIME,
      Authorization_no TEXT(50),
      Authorization_valid_until DATETIME,
      Date_shipped DATETIME,
      Arrival_date DATETIME,
      Export_country TEXT(50),
      Material_type TEXT(50),
      Quantity_of_material NUMBER
  );
  
  CREATE TABLE tblExportInformation (
      ExportID AUTOINCREMENT PRIMARY KEY,
      PermitID NUMBER NOT NULL,
      Export_Requestor TEXT(50),
      Permit_No TEXT(50),
      Permit_valid_until DATETIME,
      Date_shipped DATETIME,
      Destination_country TEXT(50),
      Material_type TEXT(50),
      Quantity NUMBER,
      Export_comments LONG TEXT
  );
  

  • This topic was modified 3 months ago by yrstruly.

  Attachments:

  You must be logged in to view attached files.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738112

  More actions

  March 7, 2025 at 5:34 pm

  #4553547

  This sounds like more of an Access question than modeling. I assume you want tblExportInformation .PermitID to link to values in the permit table. Have you tried a FK?