stuck on conceptual architecture for adb app

[vega]

I need to create an access app and am having a hard time getting through the planning stage.

Im an analyst in Ag. I have a field reports that captures 2-3 dimensions of data types; picking labor cost, hourly picking cost and variety harvested. The form is laid out by days of week. Formerly data was put into a spreadsheet, the kpi’s are being relied on more and more and solution needs to evolve to a db tool.

The tool is used to estimate harvesting costs and to archive variety by block by variety by planting date.

All 3 dimensions need to be tracked by ranch number.

I need help on the general layout of this solution. So far I have a table for harvest labor, table for hourly labor and table for variety. This sort of thing can get complicated and Im a seasoned analyst but at this point im stuck on where to go; I just cant conceptually see how this would be laid out.

A form with 2 subforms?

Tabbed solution?

For the end user, I would like the form to somewhat resemble the field form; could I used tabbed form; each tab representing day of week?

The attached relationships is just a rough draft after brainstorming.

 

[spikepl]

Just some initial comment (take a deep breath and recall what you know and what the readers of this do NOT know).

1. Who is the end user ?( and what does the user use it for)
2. Are there other users than end-user? Which?
3. How do you get data into this thing - how is data collected?
4. Do not use reserved words for column names (like Name): http://allenbrowne.com/AppIssueBadWord.html
5. Stick to a naming convention: if a field is called SomethingID then use the same name in the related table, do not switch between ID/Number or something entirely different

The alpha and omega of a good design is the data structure: not easy to see form your proposed tables what is used for what exactly. I'd suggest you list the table columns and explain what is what. The forms can come later after the data is sorted out, because they can be made whichever way is desired.

 

[vega]

Thank you for your response.

1. end users is management and production supervisor; used to monitor estimated cost per crate harvested and variety type and location harvested.
2. other user - personnel whom fill out form in the field
3. data is collected on paper form (attached), I then transpose it into a calculator in excel to derive metrics but I need to evolve this to a db solution.
4. I see
5. I overlooked that ranchID name, I know that, thanks

I understand that, designations were somewhat of a draft put together swiftly for brainstorming the structure

 

[jdraw]

Your xls is pretty much overload to me. Since you know the data and the business, I strongly recommend that you work to create a workable data model. Here are some links to information that will be useful to you.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip
http://www.rogersaccesslibrary.com/forum/uploads/5/12-Steps_to_Better_Databases.zip

You should set up some test data - both good and bad -- and test your model as it evolves. For any issue, reconcile that issue by a) adjusting the model, or b) adjusting the data as new facts are identified. Repeat the process until no issues occur.

I see a lot of calculated fields in your jpg of a model...

As spikepl said, getting the data structure designed to meet the business requirements is key.
Good luck with your project.

 

[vega]

jdraw, thank you much for this resource, reading now and hope to pick up more best practices.