Page 2 of 2 FirstFirst 12
Results 16 to 27 of 27
  1. #16
    MEMFBI is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    45
    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 Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    45
    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:	30 
Size:	41.7 KB 
ID:	43477

  3. #18
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi
    Can you upload a zipped copy of your database?

    You still have problems with the relationships between tables.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  4. #19
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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).

  5. #20
    MEMFBI is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    45
    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,664
    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

  7. #22
    MEMFBI is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    45
    [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
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    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
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  9. #24
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by MEMFBI View Post
    Each MASTER GAS CODE will contain 1 MAJOR GAS CODE and up to 5 MINOR GAS CODES that make up the MASTER GAS CODE
    Q) Each Major Gas Code has 1 to 5 Minor Gas Codes?

    Q) Are the minor gas codes assigned or does the user decide/enter the Minor Gas Codes for each record in the "GasMaster" table?

  10. #25
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    MEMFBI,

    You have talked about how you are using Access and have received advice on Normalization, relationships etc. But you really haven't described the "business" that is to be supported with this proposed database. It would be helpful to you and readers if you could tell us about the business and its processes in simple --non database jargon -- plain English.

    We need to understand what is involved before defining how it could be done.
    Good luck with your project.

  11. #26
    MEMFBI is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    45
    Sorry for late reply.
    This database will be used to maintain pricing for Master Gases.
    Users will be able to add and change Master Gases.
    Each Master Gas will contain a major Pure Gas and up to 5 minor Pure Gases.
    Users when selecting the major/minor Pure gases will also assign the percentage that this major /minor Pure gas will be contained in the Master Gas.
    In addition to the Pure gases, there are labor ,material and overhead costs associated .
    There is a Pure Gas table that contains the Pure Gases and their associated costs.
    When the "recipe" of a Master Gas is changed (percentage of a Pure Gas changed or a Pure Gas added) the cost of the Master Gas will change and needs to be reflected on the screen.

  12. #27
    MEMFBI is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    45
    The users select the major and minor gases that make up the Master gas. They select them from the Pure Gas table.
    It is basically like a "bill of material". The cost of the Master gas is driven by the Pure gases that make it up.

    Thanks

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