Yes, your relational setup is almost certainly the problem. Before making any recommendations, however, I would need to know what these tables represent.
Recommendation = ?
Mix = ?
Application = ?
Field = ?
You mention "chemicals". Is each record in Mix a chemical? A blend of chemicals? Does each Application record represent a single application of a given mix of chemicals?
What are "EH7 and AV1"?
Part of getting the right answer is asking the right question. You know what you're talking about. Doesn't mean the rest of us do.
I sent a huge long reply but I can't see it now
Did it get to you ??
Guess not. It's not present on the forum.
Jive gets a little confused sometimes. It's happened to me occasionally.
Well, that answers the first question (sort of; still not sure what this is telling me). What about the others?
Mix table - this contains details of the chemicals selected for the job (Recommendation)this example has 4 different chemicals selected from a product table each with its own application rate .and looked up chemical group
Application table - This is where the job is done to each field (Land Parcel) the date and field ID such as EH 7 is recorded as well as the actual spray area the 4 products were applied to.
Field table - this is the table that contains the Land parcel details such as crop growing land parcel name such as EH 7, AV 1
My goal is to produce a report that records as follows
Field No Date Product used type Rate applied ha sprayed units used cost/Ha
EH 7 3/2/16 Man Mag Humate Foliar Feed 2 2.2 4.4 £18.08
EH 7 3/2/16 Movon Cereal Herbicide 0.5 2.2 1.1 £26.40
EH 7 3/2/16 Oxytril Cereal Herbicide 1 2.2 2.2 £15.81
EH 7 3/2/16 Hallmark Zeon Insecticide 0.05 2.2 0.11 £3.85
With this I can then store records for each land Parcel (Field) and cost the applications
We English farmers call the land parcels fields which can be confusing when talking about Data bases !
I hope that I have made things a little clearer
Americans and Englishmen ... two people separated by a common language.
So the Mix table contains one record per chemical (like line items for Application)?
What's the difference between Application and Recommendation? Don't they both describe a job?
Recommendation records what the boss suggests to put on a group of fields.
Applications is what the tractor driver actually puts on which field on what date some days later and records the actual Ha (size)
Some fields may be part sprayed and on different days so this has to be recorded accurately .
So an Application is based on a Recommendation? Like a template, or something?
And what does each Mix record represent?
Edit: The reason I'm asking all these questions is because I think we need to back up and look at your data model. I don't think your Relationships Graph is set up correctly, but I don't want to start ripping things up until we're on firm ground.
Yes Like a template a good way of thinking of it
Each recommendation can be a combination of anything from 1 chemical to 7 chemicals (each mix record) each with its own group (looked Up) and varying rate of application. This template is then applied to a field eg EH 7
Sorry just spotted a mix is based on a recommendation not an application as in your last post
I have to leave for home now as it is 6:15pm
will check in in the morning
Thanks for your time