UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Daily Field Report For Construction Crew Table Relationships, Access 2016    
 
   
todouble22
post Nov 21 2019, 12:49 PM
Post#1



Posts: 10
Joined: 16-April 16



Good Afternoon,

I am trying to create a form for my earthwork construction crews to fill out for Daily Field Reports. The form will be for the foreman to input his job specific daily log of what activity or activities (i'd like to have several entry fields on the form) he did, what material was used, the quantity of material that was used, and the number of dump truck loads that hauled the chosen activity and material.
I have split up the database to FE and BE on my server but the relationships that I have set up are not working and I'm sure my table design is more clunky than it needs to be. I want the estimated values for each activity and material to be Populated when the activity and material are chosen on the form. I will be filling in the estimated values on the back end and not allow them to be changed on the form.
I cant get the estimated values to show when i select a certain material or activity and be relative to the chosen job. I have combo boxes for job name , activity, and material type so that the user can only select from my fields. Please help with formatting this correctly and building my form and relationships. thanks in advance. I have attached my test file for you to see what I have going on.

Attached File(s)
Attached File  Test.zip ( 82.13K )Number of downloads: 10
 
Go to the top of the page
 
Larry Larsen
post Nov 21 2019, 02:45 PM
Post#2


UA Editor + Utterly Certified
Posts: 24,435
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
Just a couple of small observations with regards to your [AutoNumbers], your going to have a nightmare further down the road.

You really need to give them a meaningful name that can be related to their tables.. and not simply call each one [ID].

In your tblReport you also have a number of "LookUp" fields that reference back to your tables, been suggested many time about using "LookuP's" at table levels..

Reference: The Evils of Lookup Fields in Tables.

HTH's
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
June7
post Nov 21 2019, 04:31 PM
Post#3



Posts: 1,220
Joined: 25-January 16
From: The Great Land


Not understanding purpose of the "Estimated" and "Loads" tables. Why don't you just type a quantity into field in TblReport? TblReport records associate estimates/loads with jobs.

Having two sets of controls on form for Activity 1 and Activity 2 will not create multiple records. This is only 1 record. If a job can have multiple activities, then you need a related dependent table - TblJobActivities. Then a form bound to that table to select job from TblReport and activities from TblActivity.

ControlSource cannot reference tables directly like that.


This post has been edited by June7: Nov 21 2019, 04:35 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
mike60smart
post Nov 23 2019, 11:31 AM
Post#4


UtterAccess VIP
Posts: 13,650
Joined: 6-June 05
From: Dunbar,Scotland


Hi

You need a Main Form for the Job and a Subform for the Materials used

See the attached example.

I would question why you need a Combobox to select Estimated items. I would imagine that these Controls would be a manual entry of specific values?

Attached File  Test1.zip ( 38.79K )Number of downloads: 5

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
todouble22
post Nov 24 2019, 01:40 PM
Post#5



Posts: 10
Joined: 16-April 16



Thank you for the input. I will work on my naming conventions.
Go to the top of the page
 
todouble22
post Nov 24 2019, 01:46 PM
Post#6



Posts: 10
Joined: 16-April 16



The purpose for my estimated and loads tables are because when I get a job I do an estimate on the time, materials, and quantities of the materials by either ton, cy, and those quantities relate to the number of loads that I estimate. My goal is to have my foreman enter in the daily reports for the activity(ies) that they did that day and it will show what I estimated and they will put in what they did for the day. I will then be able to see our productivity, efficiency, and how close my estimated values were to what was actually used. Each job can have several activities over the duration of the project. Each daily report can have several activities completed that day. I am a complete novice to access and database so pardon my ignorance in regards to all this. I am unclear what you mean that ControlSource cannot reference tables directly like that.

HTH to clear it up a bit as to what my end goal is here.
Go to the top of the page
 
June7
post Nov 24 2019, 03:32 PM
Post#7



Posts: 1,220
Joined: 25-January 16
From: The Great Land


E.g., textbox ControlSource property has: =[TblMaterialEstimated]![MaterialEstimated] which results in a #Name? error because that reference cannot be resolved.

For a small investment compared to your time and effort, this product might serve your needs (link removed)
I am NOT associated with that site. It was the best resource I found in my search.
This post has been edited by jleach: Nov 25 2019, 05:40 AM
Reason for edit: removed link, was highly sketchy...

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th February 2020 - 04:49 AM