Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Karaline's Avatar
    Karaline is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Location
    Just North Of Antartica
    Posts
    38

    Exclamation Data from Forms Tables Help Needed. AM I allowed to have a table to store all of the information?


    Hi, I have a question regarding how i should set my tables up after a team meeting telling me what they needed to add at their data entry point.

    I have a processing database started. learned on the run and it is working but i know i need help with database. the users enter multiple company dockets on daily basis to get the information on the hours worked or material supplied to allow the evidence to be caught for when payment claims are made at the end of each month.

    Now to begin with it was fine. i had a set of tables that worked. now with extras required i am confused. i have the end user entering a large amount of data from one docket. i wanted to know if i should create tables for each type of product used that day to allow the docket table not to be so large?
    At the moment the base table before normalisation is as follows. all of the information from the docket goes to this table. then i create queries to separate the information. i have a few forms for different types of dockets to allow it not to be too confusing for entry operator. the machine docket requires times of operator and machine also any attachments(Attach)GPS(Addon1),Water(Addon2),Fuel, tools,etc(Misc)

    docketNumber/DocketDate/CompanyID/Operator/ResourceID/CostCode/Location/StartTime/Breaks/FinishTime/TotalTime/VehicleStart/VehicleFinish/TotalVehicle/
    TotalQuantity/Attach/Addon1/Addon2/Misc/Comments.

    should i break this table down or is it fine to use as the shell that stores the base data input and then get the information required via queries?

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    the users enter multiple company dockets on daily basis
    What is a docket (or a company docket)?


    how i should set my tables up after a team meeting telling me what they needed to add at their data entry point.
    What needs to be added?


    i had a set of tables that worked.
    What are the tables, how are they designed (structure) and their relationships?
    Or maybe you would post your dB? Only need a few records, change any sensitive data.


    should i break this table down or is it fine to use as the shell that stores the base data input
    I would normalize the table......... I see at least 4 possible tables.
    TotalTime, TotalVehicle, TotalQuantity can be calculated so (normally) shouldn't be stored in a table.



    And welcome to the form!

  3. #3
    Karaline's Avatar
    Karaline is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Location
    Just North Of Antartica
    Posts
    38
    dockets are a daily receipt given by subcontractors to show what work they completed for the day to a main company. we have over 200 subcontractor companies under those companies will have 20 to 100 employees that will give a daily work receipt(docket) to show evidence of their work.

    This receipt must have the data entered to all a monthly accrual to be exported and every single company dockets are able to be queried to get their details for payment.

    Yes you are right, I don't have totals in my tables. just the base data that is entered on the forms. i only store base data in tables.

    the tables i use to store the information i retrieve to enter data into the forms which flows to Table DocketTransactions and then is used in queries is as follows
    Suppliers
    Resources
    Operators
    Employees
    CostCodes

    At the moment due to only having an hour to solve the problem i created smaller tables for the 3 add on boxes I had to place into forms. it was short term solution and works so not sure if i need to change it. which is why i need someone elses opinion on what to do.
    GPStable
    MiscellaneousTable
    AddonsTable
    Is this acceptable?



    the base tables are not the problem. normal stuff, suppliers. suppliers id's, employees, resources, costcodes, operators, dockettransactiondetails, dockettransactions, contacts,

    I was thinking instead of those 3 little tables i could create an extra field in resource table which allows each material/machine/accessory to be separated into categories. i then created a catergory query from this and a categoryID table.
    Please note each resource is not linked to ALL subcontractors, only to one subcontractor for that rate and type.
    eg: 18T Excavator. I can have 20 subcontractors who will have that machine but all will have different prices and different plant ID's. one subcontractor many resources.
    one resource one plant ID, one price rate.

    the addons that the dockets are now showing are as i stated in question. vehicle hours, miscellaneous add ons. due to the addons i have separated the forms to make sure they aren't too busy for the user. i now have 3 data entry forms. 1. material entry this enters anything that is by M^3, Kl, Litre, Tonne.
    Plant/Machine Entry Form. this stores all machine hours. I then have a labourhire data entry form which enters all daily temporary labour hire used.

    i send the information to a main data table and separate into extended queries for each area. labour, machine, material. i then create another query which allows me to search for different parameters.

  4. #4
    Karaline's Avatar
    Karaline is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Location
    Just North Of Antartica
    Posts
    38
    Thanks for the Welcome.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    At the moment due to only having an hour to solve the problem i created smaller tables for the 3 add on boxes I had to place into forms. it was short term solution and works so not sure if i need to change it. which is why i need someone elses opinion on what to do.
    GPStable
    MiscellaneousTable
    AddonsTable
    Is this acceptable?
    If it is working, OK....
    It sounds like the 3 tables are look up tables for 3 combo boxes... but not really sure.


    docketNumber/DocketDate/CompanyID/Operator/ResourceID/CostCode/Location/StartTime/Breaks/FinishTime/TotalTime/VehicleStart/VehicleFinish/TotalVehicle/TotalQuantity/Attach/Addon1/Addon2/Misc/Comments.
    If this is the structure of the main data table (dockets table?), it sort of looks like a spreadsheet design.


    Sorry, I still can't make heads or tails of the tables or relationships.


    I think you are (your company) a General Contractor with lots of sub contractors with many employees in many locations/plants. That is as far as I got from your description....



    the addons that the dockets are now showing are as i stated in question. vehicle hours, miscellaneous add ons. due to the addons i have separated the forms to make sure they aren't too busy for the user. i now have 3 data entry forms. 1. material entry this enters anything that is by M^3, Kl, Litre, Tonne.
    Plant/Machine Entry Form. this stores all machine hours. I then have a labourhire data entry form which enters all daily temporary labour hire used.
    As far as the entry forms, did you consider/ would it be possible to have a main form for the common docket info (ID, date) and a tab control with 3 tabs for 3 sub forms: material entry, Plant/Machine & labour/hire?


    Any chance of you posting your db (even if empty)? (won't bring it up again)

    Or a picture of your relationship?

  6. #6
    Karaline's Avatar
    Karaline is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Location
    Just North Of Antartica
    Posts
    38
    I did have a main form which allowed the user to click on the tab they required. (material, plant, labour) then they typed in company name and entered multiple dockets but it had a hiccup with users not liking the way it looked. they wanted it to look like a spreadsheet. so i made it look like a spreadsheet. give them what they want.

    I understand you would like to solve the whole problem and see the whole database but can we discuss one table and its fields and deal with that first?

    You said you could see 4 tables at least from the docket transaction table. can you tell me what tables you see please? that will help me eliminate what I have.

  7. #7
    Karaline's Avatar
    Karaline is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Location
    Just North Of Antartica
    Posts
    38
    i am trying to deal with one part at a time.

    I just can't understand what normalising this table would do.

    I get the suppliers, costcodes, employees, resource etc. but the data on this table all relates to one docket.

    if i separated the details to different tables, then isn't that just spreading out the mess the vehicle hours is dependent on the hours worked for that day, which is dependent on the date worked and that docket number. how do i create a 2nd or 3rd normalisation from that table?

    i need to send all of that informaiton plus the calculations to an export folder. I looked at a table that had the docketnumber, date, total and costcode, but that is not what is required. and I can get that from a query.

    this is why it isn't dependent on the database as a whole. this is where i am confused with the database.

  8. #8
    Karaline's Avatar
    Karaline is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Location
    Just North Of Antartica
    Posts
    38
    ok if i simplify it and look at the data the form requires.

    i need to input data that is required to be retrieved at a later date with added calculations and exported to an excel workbook for another department to use.
    the data that is kept in this database may be edited once it has been input due to errors in the docket when the time comes to pay that docket.

    if i leave the dockettransactions as is then it will always have empty pieces of information on each row due to each docket only needing certain parts of the table.

    Supplier table = 1 supplier many resources
    Resource Table = many resources 1 supplier. many resources one docketnumber
    docketTables many dockets to one supplier -
    many costcodes to many dockets? each costcode is an accounting indexnumber. one costcode can be used for multiple dockets and multiple dockets can use one costcode. and one docket can have more than one costcode.

    Extra tables to shorten the original table

    DocketsMaterials -
    docketNumber, DocketDate, ResourceID, Total Quantity, CostCode,Location, Area, Comments - i don't need the companyID because the ResourceID is connected and is related. Location is differentiates depending on who is writing the docket and changes dramatically cant create a table from it. Area is just West East Central and is a drop down box

    DocketsPlantWV (with VehicleHours)
    DocketNumber, DocketDate, ResourceID, VehicleStart,VehicleFinish, StartT,Breaks,FinishT, AddonType, AddonQuant, MiscType, MiscQuant, GPSType, GPSQuant, CostCode, Location, Area, Comments

    DocketPlant
    As above without Vehicle

    DocketLabour
    As above without Vehicle, Addon,Misc and GPS


    Should I keep CompanyID for each docket Table?

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In Post #1, it sounded like the table structure posted was the only table. So I suggested normalizing the table.

    In Post #3, I see that there are 5 other tables. But I still do not know the relationships between the tables or if they are normalized (they might be).


    Post #7
    if i separated the details to different tables, then isn't that just spreading out the mess the vehicle hours is dependent on the hours worked for that day, which is dependent on the date worked and that docket number. how do i create a 2nd or 3rd normalisation from that table?
    I don't know because I have never seen the table designs/relationships.

    Post #8
    if i leave the dockettransactions as is then it will always have empty pieces of information on each row due to each docket only needing certain parts of the table.
    This is where normalization comes in.
    Maybe the "main table" (dockets) is named "dockettransactions".....????
    That is why I suggest that the "dockettransactions" table is not normalized. A spreadsheet table design will have empty fields; a normalized table design would not.

    The table structure might be a dockets table (like an orders table), linked to 1 or more "docketdetails" (like order details) tables - a table for employee/time, a table for equipment (like Excavators) /hours, a table for materials and a table for Vehicle/hours.


    this is why it isn't dependent on the database as a whole. this is where i am confused with the database.
    I think it does... I am confused also.




    Everyone designs their tables differently. For example, all of my tables have a PK field that is an autonumber. that field has a "_PK" suffix. All foreign key fields are (must be) Long Integers and have a "_FK" suffix. The PK/FK fields are never displayed on forms because those field do not have any real world meaning.
    Other people use text fields as PK fields that have real world meaning. Or others use look up FIELDS (I never do). A lot of people use spaces in the field names (bad idea).


    At this point, I have not seen the dB, have not seen a picture of the relationship window and don't know what a "docket" looks like or what data it contains.
    I don't think I can be of any help, so I'll step away.

    I will ask a few other posters here to look at this post to see if they have any suggestions/help for you.


    Sorry.
    Good luck with your project.

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Not sure if I can help because your descriptions are confusing. But going back to the beginning

    i have the end user entering a large amount of data from one docket. i wanted to know if i should create tables for each type of product used that day to allow the docket table not to be so large?
    1. Please define large - do you mean lots or rows or do you mean lots of columns? or both?

    2. You say 'i have a few forms for different types of dockets to allow it not to be too confusing for entry operator' - Please provide a screenshot of the forms you are using.

  11. #11
    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

  12. #12
    Karaline's Avatar
    Karaline is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Location
    Just North Of Antartica
    Posts
    38
    i realised i can't explain what i require to others. i see what i mean but others can't. and due to this issue i have with explaining things, i thought my question was wasting peoples time. so i solved it as much as i could myself.

    I wanted to know how a person seeing the below information that was required to be entered would normalise that information down. does that make better sense?

    i have a database, it is all there hours spent on tables and relationships. they are not part of the question. if i get an answer and it contains stuff i have already done, i can skip it and use what i havent done. I had problems with normalising the data i retrieve from the dockets. i tried sending it to an query that was an extension of the 3 smaller tables but wifi doesn't like me doing that, pulling too much data for a simple storage issue

    reverse engineer.

    i have created 3 forms.
    plant
    material
    labour

    i have split the main resource(product) table to 3 smaller tables that are by category. this will keep as little going through the datalink as possible. crap wifi where my site office is. i have created extensive queries and i don't need reports, only the queries to export.


    I have a plantresource table. it stores all of the resources for companies who supply machines an operator hours. they also supply miscellaneous items, which is in a small table. i need them to link easily when i enter company name in the form.
    now i enter the data into the forms. i need that data to go somewhere to allow it to be retrieved in a weeks time for payment.
    i will need to be able to pull the information related to the docket number to see the
    starttime, breaks, finishtime, rate, operator, gps and any addons or miscellanous, costcodes and comments.
    where and how would you store that data ?

    so when i am entering the information into the form and i already have the company, resource,rates, operators, costodes stored in tables. where do i store the data from each individual docket?

    i thought it would be in a table that is related to the category eg(tblplantdocket, etc)

    am i making any more sense at what I am asking?

  13. #13
    Karaline's Avatar
    Karaline is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Location
    Just North Of Antartica
    Posts
    38
    i'll try to get better at explaining. i have a query calc question practice makes it get easier ...I think! lol

  14. #14
    Karaline's Avatar
    Karaline is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Location
    Just North Of Antartica
    Posts
    38
    Trying to explain again, I don't understand how to create tables to suit the forms we need to enter.

    I have looked and seen a few examples where a table had every single field that the form had in it minus the noun tables (Suppliers, Products, etc) to me it seems too messy

    if i put all of the details that need to be stored into one table, it would be over 30 fields long. that is ridiculously long

    i then tried to look at maybe splitting the table into a main table that stores the first part of the form information.
    docket number
    company id
    resource id

    a second table that stores docket id and main information conncerning operator/machine hours start breaks finish and machine start and finish.

    a third smaller table that dealt with the attachments and miscellaneous of the form. GPS, attachments.

    I then created a table just for labour form data to be stored in
    and a material table just for material form data to be stored in.

    i have looked online at sample databases but i haven't found any that deal with inputting data from receipts or external sources. purchasing, ordering, invoicing, all internal no external physical data held and redistributed.

  15. #15
    Karaline's Avatar
    Karaline is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Location
    Just North Of Antartica
    Posts
    38
    i am so over this. i am trying to live by the normalisation rules, but i don't get the rules with this type of data that needs to be regurgitated

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

Similar Threads

  1. Replies: 1
    Last Post: 12-05-2014, 12:26 PM
  2. Replies: 9
    Last Post: 04-01-2014, 05:06 PM
  3. Replies: 1
    Last Post: 07-26-2013, 06:01 AM
  4. Replies: 3
    Last Post: 11-29-2012, 11:00 AM
  5. How to store data many row in one table
    By dododo in forum Access
    Replies: 4
    Last Post: 06-25-2011, 12:42 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