Complex query?

[SergeantMike]

I have no idea if this can be done in Access or if I need to do this manually in Excel as I have been.

I have counts from insects emerging from plants and I want to automate the calculation of the percentage that have emerged for each day. For example:

Data in the Table
PLANT DATE TimeEmerge NUM EMERGED
A1-1 6-20-19 9:00 1
A1-1 6-21-19 9:00 1
A1-1 6-21-19 13:00 5
A1-1 6-22-19 9:00 10
A1-1 6-22-19 13:00 25
A1-1 6-22-19 17:00 5



This query generates
SELECT T1.txtFarmPlotID, T1.intContNo, T1.dtePick, T1.BugFound, (SELECT Sum(qryRunningTotal.BugFound) AS Total FROM qryRunningTotal WHERE qryRunningTotal.dtePick <= T1.dtePick AND qryRunningTotal.txtFarmPlotID = t1.txtFarmPlotID AND qryRunningTotal.intContNo = T1.intContNo ) AS VarietyTotal, T1.[Elapsed Days], T1.intGallCount, T1.sngWeight
FROM qryRunningTotal AS T1
GROUP BY T1.txtFarmPlotID, T1.intContNo, T1.dtePick, T1.BugFound, T1.[Elapsed Days], T1.intGallCount, T1.sngWeight;



this sheet
PLANT DATE NUM EMERGED ACC EMERGED
A1-1 6-20-19 1 1
A1-1 6-21-19 5 6
A1-1 6-22-19 34 40

I would like to add an additional column of % emerged

PLANT DATE NUM EMERGED ACC EMERGED % EMERGED
A1-1 6-20-19 1 1 2.5
A1-1 6-21-19 5 6 15
A1-1 6-22-19 34 40 100

[ranman256]

yes, add the data in your table1 to the maindata.
then run the summary query to get the %.

Q1 to sum the plant total
Q2 sum 1 day event,
Q3 join Q1 & Q2 to get your final data with %.

[Cottonshirt]

SergeantMike said: I would like to add an additional column of % emerged

I admit to being confused about what exactly it is you want to calculate. as far as I can tell from your data, 100% of bugs that have emerged, have emerged, so your calculation of % emerged will always be 100%.

but the first row of your summary data says...

PLANT DATE NUM EMERGED ACC EMERGED % EMERGED
A1-1 6-20-19 1 1 2.5

...that one bug emerged today, and that is a total of 1 bugs that have emerged, and that is 2.5%.

but 1 is not 2.5% of 1, so I'm just confused.

maybe it would help if you were to clarify what you are trying to calculate.


Cottonshirt

[SergeantMike]

I admit to being confused about what exactly it is you want to calculate. as far as I can tell from your data, 100% of bugs that have emerged, have emerged, so your calculation of % emerged will always be 100%.

but the first row of your summary data says...



...that one bug emerged today, and that is a total of 1 bugs that have emerged, and that is 2.5%.

but 1 is not 2.5% of 1, so I'm just confused.

maybe it would help if you were to clarify what you are trying to calculate.


Cottonshirt

However, 1 is 2.5% of 40 which is the total number of bugs that emerged

[Cottonshirt]

okay, so you want to do several things,

1. for each plant, count the number of bugs that emerged each day
2. for each plant, sum the total number of bugs that have emerged on that plant, so far [totalsofar]
3. calculate the daily total as a percentage of the [totalsofar]

personally, I would do this in three queries, where each query does one distinct thing, as listed above.

therefore, the answer to your original question: I would like to add an additional column of % emerged

is, do it in a separate query.
you would need to get T1.intGallCount and Sum(qryRunningTotal.BugFound) on the same row of your query, then calculate the percentage: (T1.intGallCount/Sum(qryRunningTotal.BugFound)) * 100


good luck with your project,


Cottonshirt