Help needed to understand my error

[FruitGuy]

Hi,
I have been trying to get this database to work, and I thought I had it figured out when all the data was going into its proper places in the tables. But when go to make the reports it’s showing records that shouldn’t be there or at the very least be filtered out.
For an Example: In the database that is attached, the for frmSprayApplicationsMain is used by a coordinator to list the spray application items (Pesticides) on the top subform and then select the areas (FarmNames and Blocks etc.) that are being completed by the applicators as they are being reported to him via 2 way radio. This allows him to record as well track what has been done by simply checking the checkboxes.
This form works rather very well I think, the problem is when I go to generate the reports, its listing the farms from a different AppID that shouldn’t be there.

I suspect either/both of one or two of the following things
1. I may not have setup the relationships up correctly. I have tried different methods of doing that and ended up with what is attached. Breaking normalization rules
2. That I have setup the form frmSprayApplicationsMain incorrectly by using 2 continuous subforms in it.
Hopefully someone can spot my error and point me in the right direction and explain to me what has been frustrating me for the last few weeks.

Thanks in advance!!

 

[GinaWhipp]

Well, can't opened attached what version of Access was it created in?

 

[FruitGuy]

Hi GinaWhipp, and thank you for the help
I apologize for not getting back to you sooner.

It was created using 2013 Access. I have converted it to 2003 that is attached.

 

[FruitGuy]

Ok for some reason I can't even open the attachments in my own thread. Tried using my dropbox to share the file because of the size limits of the attachments here. But luckily converting it to mdb it's under the limit.
Try the one attached this message. Sorry about the inconvienence

 

[GinaWhipp]

Presently, not in a location to open file, will look at later today when I am.

 

[DavidAtWork]

Some of the key tables do not have Primary Keys defined, these are essential to a relational database.
You might want to reconsider the design of your table structure

David

 

[Simon_MT]

Some of the Pesticides are Nutrients here are some I recognise:

Agri-Grow (calcium in suspension)
Citric Acid
Copper (Spray)
Hydrophos M/P/K
Urea

It would seem that you need to cross-reference:

FarmBlock With FarmID
FarmBlock With Crop

Pesticide / Treatment With (Active) Ingredients

Application With FarmBlock
Application with Pesticide / Treatment

From Application you can go to Ingredients and determine Re-Entry and PHI values (I'm not sure what that is).

So your Supervisor enters the FarmBlock and the details of the Application and Treatment / Pesticide info.

From the FramBlock you can determine the Crop and Farm.

Simon

 

[FruitGuy]

Some of the key tables do not have Primary Keys defined, these are essential to a relational database.
You might want to reconsider the design of your table structure

David

Ok thanks. I remember at one point I did have Primary Keys defined in both
tblFarmsBlocksSprayed and tblPesticideItems and I couldn't get the 2 subforms linked properly, they would either show nothing or all records from their tables. But then again I might not have understood/setup that correctly either.
Any suggestion on how you would structure this.

 

[FruitGuy]

Some of the Pesticides are Nutrients here are some I recognise:

Agri-Grow (calcium in suspension)
Citric Acid
Copper (Spray)
Hydrophos M/P/K
Urea

Thanks Simon, you got a good eye. You must have prior experience with these or at one point worked in Agriculture. We have to log all inputs that go on to our trees for our CanadaGap Certification in order to sell to the big chain stores.

It would seem that you need to cross-reference:

Cross-reference? Do you mean create a relationship between these

FarmBlock With FarmID
FarmBlock With Crop

Pesticide / Treatment With (Active) Ingredients

Application With FarmBlock
Application with Pesticide / Treatment

From Application you can go to Ingredients and determine Re-Entry and PHI values (I'm not sure what that is).

So your Supervisor enters the FarmBlock and the details of the Application and Treatment / Pesticide info.

From the FramBlock you can determine the Crop and Farm.

Simon

From what I'm understanding is if I were to create these new relationships (Which would essentially be restructuring everything like DavidAtWork mentioned)
Do I leave the ones I have now intact or delete and go with only the new ones.
I must be really screwing up this many to many relationship rule.

Just FYI PHI=PreHarvestInterval

 

[Simon_MT]

There are a few things

The tblReEntryPHI I don't think works.

I would have:

Applications
Applications Ingredient
Applications Purpose

In the Applications Ingredient I would have the protective information and Re-Entry info.

Take Sniper and Guthion Solupak both have the Active Ingredient Azinphosmethyl one you have set-up All the Protective Apparel the other has not. There are products with duplicated Active Ingredients, different blands, these Ingredients could be sorted by a separate file. If every application needs Chemical Resistant Gloves, Boots, Coveralls and Long Pants and sleeves these can be treated a mandatory and don't need flagging. I would also change your flags to True/False (boolean -1 for true, don't know why!) rather than X.

The Varieties could have Extimated Harvest unless there is a huge geographical spread and the ideal harvest times differ.

When if comes to Applications it may pay to add a Scheduled Application and well as the Actual Application. That way you can ascertain what needs to be done.

Taking this one step further to get your CanadaCAP Certification you could profile what treatments need to be applied to each variety obviously these are tied to critical dates so again you can plan your treatment program. You probably do this in your head so I'm just talking possibilities because this is a great project and quite sophisticated.

I'm more than happy to help. I played around with the data tables but I don't want to presumptuous.

The way you can use a database is to reduce the textual information and use FarmID rather than Farm Name in the field. Applications only needs to the FieldID because the Field is related to FarmID.

Simon

 

[Simon_MT]

I would add that these three Applications don't need Face / Mask Protection?

Chlorantraniliprole
Fenbuconazole
Propiconazole

Simon

 

[FruitGuy]

There are a few things

The tblReEntryPHI I don't think works.

This table was imported from an excel file where I have been doing this logging/summarizing somewhat manually after the spray applications have been completed. I believe access could be much more automated by tracking with the check lists and logging at the same time

I would have:

Applications
Applications Ingredient
Applications Purpose

In the Applications Ingredient I would have the protective information and Re-Entry info.

Take Sniper and Guthion Solupak both have the Active Ingredient Azinphosmethyl one you have set-up All the Protective Apparel the other has not. There are products with duplicated Active Ingredients, different blands, these Ingredients could be sorted by a separate file. If every application needs Chemical Resistant Gloves, Boots, Coveralls and Long Pants and sleeves these can be treated a mandatory and don't need flagging. I would also change your flags to True/False (boolean -1 for true, don't know why!) rather than X.

Guthion has been discontinued and isn't available any more so it really shouldn't even be in there. I have to do some purging of obsolete pesticides this winter, but thanks for pointing that one out.
I agree about the X that should be switched to true/false, I was planning this for the Spray Applicators Instruction Sheet that I haven't had time to build yet. As well as the mandatory items that are just repeated but would rather like to keep them in there as this way we have it documented they are required personal protective equipment.

The Varieties could have Extimated Harvest unless there is a huge geographical spread and the ideal harvest times differ.

Unfortunately this true,

When if comes to Applications it may pay to add a Scheduled Application and well as the Actual Application. That way you can ascertain what needs to be done.

Taking this one step further to get your CanadaCAP Certification you could profile what treatments need to be applied to each variety obviously these are tied to critical dates so again you can plan your treatment program. You probably do this in your head so I'm just talking possibilities because this is a great project and quite sophisticated.

I would love to have something like this in place and practised but we are pretty much at the mercy of mother nature and planning spray applications are usually last minute.

I'm more than happy to help. I played around with the data tables but I don't want to presumptuous.

The way you can use a database is to reduce the textual information and use FarmID rather than Farm Name in the field. Applications only needs to the FieldID because the Field is related to FarmID.

Simon

Simon, I would very much like to see what you have done as I mostly have been watching a lot of videos and reading articles. I think I learn more from studying examples and then applying them to where I need them.

It sounds like you have fairly a decent understanding of what the end goal is for this project and would be very interested in seeing what you have done.

 

[Simon_MT]

Let me have a go at it it maybe a day or two. I was just tinkering to understand the project. I will do it thoroughly.

Don't worry about the Protective clothing issue everything is included just some are deal with in Bulk.

How do you know what to spray and when (allowing for mother nature)?

What does CanadaGap Certification impose on farmers?

What does PCP mean I'm taking a stab in the dark - Pesticide Control Program

Simon

 

[GinaWhipp]

FruitGuy...

I don't want you to think I abandoned you, I just see you are in capapble hands so just going to sit here on the sidelines.

 

[FruitGuy]

Let me have a go at it it maybe a day or two. I was just tinkering to understand the project. I will do it thoroughly.

That would awesome Simon, no rush either as I'm only hoping to roll this out for the next years crop, I might try and input this years data in the winter depending on time.

Don't worry about the Protective clothing issue everything is included just some are deal with in Bulk.

How do you know what to spray and when (allowing for mother nature)?

For the most part it is decided by the Owner/Manager of the farms/business. For example he would receive hand written scouting reports from a hired scout that would have the number of pests in the orchard and then decide if it is feasible to spray for them. But when it comes to controlling scab there is a recommended interval and after any rain.

What does CanadaGap Certification impose on farmers?

CanadaGap is a fairly new Certification Entity. It focuses on farm food handling safety. For the most part other then a lot of common sense issue (no coughing, smoking or bleeding on the apples) it focuses on being able to prove that you didn't harvest apples within the PHI (Pre harvest Interval), As well as a wide range of other hazardous materials.

What does PCP mean I'm taking a stab in the dark - Pesticide Control Program

Very close stab there. PCP - Pest Control Products. Pretty much a registration number for the pesticide.

Simon

 

[FruitGuy]

FruitGuy...

I don't want you to think I abandoned you, I just see you are in capapble hands so just going to sit here on the sidelines.

No worries GinaWhipp, I know you guys are doing this in your free time and do very much appreciate the effort.