Page 2 of 2 FirstFirst 12
Results 16 to 23 of 23
  1. #16
    MEMFBI is offline Novice
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    15


    Quote Originally Posted by ssanfu View Post

    First, my apologies. I spent a lot more time looking at your dB and realized the "Related Tables" appear to be multiple copies of table "PGASC02" (because of the repeating fields)and are Look up Tables.
    Only the table "Gas_Notes" (with its repeating fields - which should be redesigned) is a related table.

    Your design is not how I would design the tables. I shouldn't have said "Bad design".
    My tables (99.98% of the time) have a PK field of type Autonumber. See Microsoft Access Tables: Primary Key Tips and Techniques

    I have been working on your dB..... if/when I get time I'll post it. I have deleted all of the records just to make it easier to add/change fields/relationships.


    It looks, to me, like "PGAS000" is the main table. See if this helps any.
    There are 70 fields (if I counted correctly) in the table, so I had to do a little cutting and pasting to get all of the fields in the image.....
    Attachment 43463
    No apologies necessary. I am a novice with this . The data originally came from an IBM I5 system. I exported it and am trying to duplicate the process they used on the IBM I5 to calculate the costs/pricing. As far as the repeating fields, I presume that you are saying I should have a separate table that has the Timm_KEY (major gas code key)and the corresponding minor gas codes(MGC100 - MGC500) and the percentages (MGCP10 - MGCP50)?

    Thank you!

  2. #17
    MEMFBI is offline Novice
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    15
    I have created relationships. Screen shot below. Is it bad practice to store calculated fields in tables? As an example, is it bad design to store the "total gas cost" in the Gas Mater table?

    Click image for larger version. 

Name:	relationships.png 
Views:	17 
Size:	41.7 KB 
ID:	43477

  3. #18
    mike60smart is online now Expert
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    504
    Hi
    Can you upload a zipped copy of your database?

    You still have problems with the relationships between tables.

  4. #19
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,033
    Quote Originally Posted by MEMFBI View Post
    Is it bad practice to store calculated fields in tables? As an example, is it bad design to store the "total gas cost" in the Gas Mater table?
    IMHO, Tables are for storing data... period!
    Tables should never have calculated fields, look up fields and/or multi-value fields.
    Calculations should be, as much as possible, done in queries (or in a control on a form/report).
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #20
    MEMFBI is offline Novice
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    15
    Gas_Master_Test.zipHere is the database. I have made a few changes to the relationships.

    Thank you!

  6. #21
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,033
    What is "Minor_Gases_1"? You added 5 tables - does every record have an entry for "Minor_Gases_1" through "Minor_Gases_5"?
    What is "MGC300"?
    What is "MGCP30"?
    What is "Timm"? (as in "Timm_key"?

    Still needs work on your table designs (IMHO). See Primary Key Tips and Techniques
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  7. #22
    MEMFBI is offline Novice
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    15
    [ATTACH]43501[/ATTACH
    ]Read some articles on Normalization. Thought I should break up the tables.

    Timm_Key is the MASTER GAS CODE
    Each MASTER GAS CODE will contain 1 MAJOR GAS CODE and up to 5 MINOR GAS CODES that make up the MASTER GAS CODE
    All of the MAJOR GAS CODES and MINOR GAS CODES are contained in the PURE GAS TABLE
    The original database has table PGAS000 as the GAS MASTER Table. Within that table each MASTER GAS CODE contained 1 MAJOR GAS CODE AND 5 MINOR GAS CODES.
    I broke that table out into the GAS MASTER (stagnant data) and other tables....MAJOR_GAS which has TIMMKEY(MASTER GAS CODE) then the MAJOR GAS CODE that is in the MASTER GAS CODE
    Did the same for the 5 MINOR GAS CODES.
    Within the MAJOR/MINOR GAS CODE TABLES is the MASTER GAS CODE, MAJOR/MINOR GAS CODE , GAS_PCT of that MAJOR/MINOR GAS CODE contained in the MASTER GAS CODE.
    I have attached another zip file, with some data in the tables.
    Attached Files Attached Files

  8. #23
    mike60smart is online now Expert
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    504
    Hi

    Study the relationships between tblGasMaster, tblMajorGases & tblMinorGases

    You can now add as many records as you like for the Minor Gases in the 1 Table vice the Multiple Tables you had originally.
    Attached Files Attached Files

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 10-23-2017, 06:32 PM
  2. Perplexing Combo Box Dropdown issue
    By GraeagleBill in forum Programming
    Replies: 3
    Last Post: 10-29-2015, 06:35 PM
  3. Replies: 1
    Last Post: 07-09-2014, 03:36 PM
  4. form dropdown problem!
    By aoken1 in forum Forms
    Replies: 3
    Last Post: 02-22-2013, 06:43 PM
  5. Form dropdown selection
    By piper in forum Forms
    Replies: 2
    Last Post: 04-16-2011, 09:34 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums