Results 1 to 11 of 11
  1. #1
    Torinjr is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    May 2015
    Posts
    53

    Retain Selected Inputs on New Record

    Hi,

    Looking for some guidance on how I should set up this form and associated query.

    The form I am wanting to input: A DocketDate, DocketQty (Hours worked) and PlantNo which auto populates (PlantRate and PlantCapacity) based off PlantTbl.



    Now this Plant does a certain amount of differing Loads in a day.

    So the other input required is LoadType (which can be 0 entries or multiple entries) based off LoadTypeTbl, which also has a LoadCount attached to it.

    Now I want the following information to be stored based off the above entries:
    Record1: PlantNo, DocketDate, PlantRate, PlantCapacity, LoadType1, LoadCount1, TotalLoadCount, Productionqty (=PlantCapcity*LoadCount), ProductionCost (=DocketQty*PlantRate*LoadCount1/TotalLoadCount)
    Record2: PlantNo, DocketDate, PlantRate, PlantCapacity,LoadType2, LoadCount2, TotalLoadCount, Productionqty (=PlantCapcity*LoadCount), ProductionCost (=DocketQty*PlantRate*LoadCount2/TotalLoadCount)
    And so on Depending on how many LoadTypes are entered for the given PlantNo

    Basically each time i enter a new record for each subsequent LoadType I want the DocketDate, DocketQty, PlantNo and TotalLoadCount fields to be retained on the new record, which will reduce entries required.

    Note: ProductionQty and ProductionCost will probably be generated in a report based off the query rather than stored in the query and I know /TotalLoadCount will generate an error if this is a Zero value so I will include an ifstatement or something here. Just wanted to describe the outputs required.

    Hope this makes sense.
    Cheers

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Torinjr,
    Please post a description of your business that this proposed database is intended to support. You are telling us how you have some forms and queries. But readers only know what you tell us. It is important to provide some context of the business in simple, English --no jargon. Just tell us how dockets, loads and plants are related. Once we understand what you are trying to accomplish, you will get more focused responses.
    Good luck.

  3. #3
    Torinjr is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    May 2015
    Posts
    53
    Ok thanks orange, It is an earthworks/roading infrastructure business.

    We have a plant list (For earthworks machines). We capture costs through dockets inputted into the system. We capture earth moved through Load Sheets.

    One piece of plant does X amount of hours in a given day which generates a cost to us: X amount of hours*plant rate.
    That piece of plant does X amount of Loads for Load Types A, B, C. For example Dumptruck#12 does 20 Loads of Structural Fill from Cut#1 to Fill#5 AND 15 Loads of Rock from Cut#3 to Fill#4.
    The amount of earth moved is equal to No of Loads * Plant Capacity. i.e. Dumptruck#12 has a plant capacity of 15m3 therefore Strucutral Fill moved is 15m3*20Loads and Rock =15m3*15Loads.
    Therefore Origin: Cut#1 moved 300m3 to Fill#5 for $4 and Cut#3 moved 225m3 to Fill#4 for $5.60.

    I want to be able to determine how much of LoadType A or B or C or a selection of them has been shifted from origin to detstination in total for all plant.

    How productive that piece of plant has been ($/m3).

    Thats the crux of it. Theres a wide range of different reports and figures I need to spit out and I am currently using Excel to generate these figures. However with the extensive plant lists, load type lists, origin/destination lists, etc, etc excel is very slow with all the sumifs formulas im using, and the data entry is owenrus. So this database will be lot a more powerful/data validated and time saving.

    Hope this helps provides some more context.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Torinjr,

    You mention a background/familiarity with Excel--do you have experience with database? Often those with Excel skills feel Access is just a variation(big brother) of Excel. That is incorrect. I'm not suggesting that represents you, I am suggesting that Excel and Access are based on different concepts, different object models and are different products/tools. Sometimes the Excel skills have to be unlearned--a change in mindset.

    For clarity in your business description, I do not see Customer or Client; Order, Invoice---some of the traditional entities. It seems to me that you are assembling/reporting statistics from some other operational system.

    Here is my general understanding from your post: The term "plant" is confusing, but my summary is intended for revision/refinement.

    You are in an organization whose primary business is "moving earth from point A to point B". The organization has many "facilities"/plants. These plants have various pieces of equipment (dump trucks--but possibly others excavator, backhoe...). The dump trucks move various types of earth in a load(LoadTypes A,B,C ...). Each dump truck has a volume capacity (in cubic meters). Each truck works a number of hours/day. Each truck has a base cost. Each truck moves a number of loads.
    Each truck's revenue can be determined from number of loads and base cost.
    It does not seem that weight of load is involved in any calculation. Loads are determined/recorded via LoadSheets.
    Costs are determined via Dockets. What info is recorded on these is unclear. Also Cut and Fill seem to relate to /represent Origin and Destination.
    It isn't clear where or how LoadSheets originate.

    The above is meant to assist communications and refine the requirement for the proposed database and calculations.

  5. #5
    Torinjr is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    May 2015
    Posts
    53
    Yea you are right. I have produced one database before but I understand what you mean, I am relearning the basics as I am going and am quickly picking up on the concepts.
    Already at this stage I have the database where I can see that it is a lot more efficient than what I had excel doing.

    Yes your understanding of what I have explained is completely correct. Yes you are right in the sense that this not traditional entries, it is a bit more complicated. I guess you could think of "plant" as a type of product, this costs a certain $ amount, it belongs to a supplier. The docket could be considered as the invoice. The load sheet could be considered as an output from the product.
    FYI dockets record Plant or "Machine" hours, cost code, hours worked - and is basically a receipt of the work done. Load sheet originates from each dumptruck/truck moving earth, it shows what machine, moved what type of material from origin to destination and how many loads it did of that.

    As we are getting side tracked with how the business operations work - I have figured out in the interim that it is best to keep the "Plant" and "loads" as separate forms and queries. When I need to link this information I will create a report or joining query or something that will link the information and calculate meaningful outputs for us. i.e.productivity. I am pretty much at this stage and will be working on this tomorrow, I know it probably can be done as a single form but it is not mandatory. I appreciate your feedback, even just the questions you are asking me and the responses I am thinking of is helping me to piece this together and spark some thoughts around how it should be done better. I am not expecting to produce a flawless database I just need something will serve its purpose, that I can put together in the next week and will be robust enough not to break down.

    Thanks

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Torinjr,

    I am attaching a draft model for consideration. I am using Trucks, Loads etc. I hope it helps.
    Your table relationships are determined by your business rules, so relevant business process info is not a tangent.

    Trucks have base cost, and I'm sure that changes with time. So, you would record the current base cost for the truck carrying each load. You do not pick up the current base cost directly from the base cost table --record the current base cost with the load at the time of the load. That way, when you go back to older records you will not be using an incorrect base cost.

    The Locations table is intended to be a lookup table from which you can select Origin and Destination for each Load.
    Load Start and Finish Times would include Date and Time.

    I'm not sure where Supplier fits.

    As I said, getting your tables and relationships designed and tested to match your requirements is critical.
    You can test your model with some sample data and scenarios to validate the model. Your confirmed /validated model becomes a blueprint for your database structure. Test, adjust and confirm before getting into physical database. Physical databases can be extremely frustrating to change.

    I hope this is helpful.

    Click image for larger version. 

Name:	TrucksLoadsCostsDraft.png 
Views:	17 
Size:	31.9 KB 
ID:	34015

    You may find a more appropriate model at Barry Williams' site
    Here is basic customer product invoice

  7. #7
    Torinjr is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    May 2015
    Posts
    53
    Ok, Thank you. Yes it is critical to to save older records with the cost at that time. I assumed that old records would save and if the plantrate is updated it would not update all old records unless data integrity was enabled with the cascade option??? In saying this please check my image below of the relationships. The Plant rate I assumed would be carried across between RateLuPlant - which is bound to the primary key but has a column count 8. If the plant rate is updated will this change the records saved into "LoadDocketEntry"?

    I have attached an image of the relationships I have currently. There is a lot more going on than what I have been explaining. And this is only the Load side of dockets.

    Click image for larger version. 

Name:	DarabaseLoadRelationships.PNG 
Views:	17 
Size:	32.9 KB 
ID:	34026

    I have been testing with made up data before I try to transfer any across.

    Anyway further to this I am trying to speed up the form by having a "save a new load button" and a "save and exit button". The save and exit button I have working. The save and new load button i want to save the record and go to the new record, however I want the form to retain the previous entries in the docket date, product code and plantNo fields and focus onto the tab index for mass haul type. Form pictured below - its based off a query from the above relationships and the form populates the table LoadDocketEntry - with the orange sunken boxes autopopulating.

    Click image for larger version. 

Name:	LoadDocketEntryForm.PNG 
Views:	15 
Size:	25.4 KB 
ID:	34027

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Two things on naming:
    Only 2 fields with spaces in the names .... table "ListLUSector" fields "CH From" and "CH To". Consider "CHFrom" or "CH_From"........"CHTO" or "CH_TO"

    Every table has the PK field name of "ID"???




    I use the suffixes "_PK" and "_FK" to denote that the field is a primary key field or a foreign key field.

    So table "LoadDocketEntry" would be
    CurrentName Suggested Name
    ID LoadDocketEntryID_PK
    ProductCode ProductCodeID_FK
    LoadPlantNo LoadPlantNumID_FK
    MassHaulType MassHaulTypeID_FK
    LoadedMaterial LoadedMaterialID_FK
    LoadOrigin LoadOriginID_FK
    LoadDestination LoadDestinationID_FK
    LoadCount LoadCount
    LoadDocketDate LoadDocketDate

    To me, having a name like "LoadPlantNo", is this a Yes/No type field? I think it is getter to use "Num" instead of "No".
    Obviously, if a field has a suffix of "_FK", there is a corresponding field in a different table with a "_PK" suffix.


    Also, if the PK field is an Autonumber type, it should NEVER be displayed on a form.
    See Autonumbers--What they are NOT and What They Are and Microsoft Access Tables: Primary Key Tips and Techniques





    My $0.02 ..............


    PS.... any chance you would post your dB???

  9. #9
    Torinjr is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    May 2015
    Posts
    53
    Cheers,

    Attached, with confidential information taken off/altered.
    Now I have all the forms working and saving the info. I need some help producing the reports Im trying to create.
    Attached Files Attached Files

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm glad you understand your relationships and that this structure works for you.

    What do your RATE... tables mean exactly?
    What happens when a rate changes?
    We now see more scope to the project---labour, plant and load.
    Good luck with your project.

  11. #11
    Torinjr is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    May 2015
    Posts
    53
    Hi Orange,

    I am having a lot of difficulty creating meaningful reports by combining the data from the Labour, Material, Plant and Loads Queries.
    I want to be able to link these queries together to sum total costs and group the records by dates, costcodes, sectors, etc. Preferably this grouping could be accomplished through a form.

    Now I am thinking that the best way to do this would be one of the three things below:

    1) Either I link all this data into a single table by redirecting the control sources from the individual Labour, Mateial, Loads and Plant Forms that feed into their separate tables, into a "Master Table".
    2) Or I run an union query and combine these, however from reading up on this subject it appears that the queries needs matching field column counts and identical formats to be able to do this - which I'm not sure if I can do or not.
    3) I set up an export to excel button for each query type and combine the data using formula. However this seems to defeat the purpose of changing to access to avoid slow calculations due to large computing demand on sumifs formulas.

    But what would be your recommendation on this?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-06-2015, 01:18 PM
  2. Replies: 8
    Last Post: 06-18-2014, 02:51 PM
  3. Replies: 5
    Last Post: 03-26-2014, 11:09 AM
  4. How to retain values selected in a listbox
    By Cedarguy in forum Forms
    Replies: 4
    Last Post: 05-09-2012, 10:05 AM
  5. Data Entry Form: Retain Selected List Box Value
    By William McKinley in forum Forms
    Replies: 1
    Last Post: 11-05-2010, 10:14 AM

Tags for this Thread

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