Calculate Daily Work Completed - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Calculate Daily Work Completed

Cara Simpson

3 years ago

Dear Mr. Rost,
I am enjoying MANY of your videos but I do need help with a query if you don't mind. I have been playing around in access based on what I've learned from you and I'm trying to build a query to make a report based on daily work closures. I work with orders where many employees could complete the order on different dates. I have 3 different dates with 3 different quantity amounts for work completed but I can't get my query to only pull the current date for closed work. It keeps including prior dates. Such as.. Order#28 has a first closure date of 7/15/2023 with 10 closed, second closure date of 7/16/2023 with 10 closed and a third of 7/21/2023 with 5 closed. The query includes all those dates and sums up all quantities to 25 instead of 5 for 7/21/2023 to my GrandDailyClosed field. How can I get my query to only calculate the closures for the current date, in this case, 7/21/2023? I have the criteria for the closed dates set to Date(). Please help. I have watched lots of videos but cant find any to help me. Thank you for all your lessons. Please keep them coming!

	Kevin Robertson @Reply 3 years ago
Can you post a screenshot of your query so we can see your criteria/calculated fields?

	Cara Simpson OP @Reply 3 years ago
Kevin, Thank you for your response and offer to help. I have included screenshots for your review. Please understand my knowledge of access is a beginner level so don't judge too harshly LOL

	Cara Simpson OP @Reply 3 years ago

	Cara Simpson OP @Reply 3 years ago

Richard Rost @Reply

3 years ago

In the example that you show, there are three different close dates. The criteria are set so that if the current date is equal to any one of those three dates, then the record will be included. Now, how would you like the results to be displayed? Do you want it to be based on the current date equaling the first closed date, or the second, or the third, or all three? If it has to be equal to all three, then put the date criteria on the same row going across. Remember, the rule is "AND" across "OR" down. So, if you move them down in different rows, that's an "OR" condition. If you want all three closed dates to match today's date, then put them all in the same row.

Cara Simpson OP @Reply

3 years ago

Thank you, Richard for responding. I noticed my dates for my explanation and screenshots were different and I apologize. The explanation was copied and pasted from previous information and I failed to change the dates. Thanks to your videos on AND/OR I have previously watched many times, I believe the order results are correct. The problem is with the calculations. I'm trying to get a daily total closed from each order but my total for what was closed that day keeps including prior dates amounts in the total. I believe I need to add a Total daily closure field in my query that states the total daily closed is equal to the first complete cases plus the second completed cases plus the third completed cases where/if the first, second or third close date is today's date but I can't seem to find the correct expression even after hours of research/trial and error. Please help.

	Richard Rost @Reply 3 years ago
I don't understand what you're looking for based on the information you gave me. Can you show me some examples perhaps with screenshots of what it's supposed to look like?

Cara Simpson OP @Reply

3 years ago

I'm sorry I'm not explaining it right. I'm still learning Access lingo. Maybe it would be easier if I post the report I am making. I have highlighted a few rows of the report. If you look under the closed dates, I've highlighted the date 8/14 (today's date) and their quantities. I also highlighted the totals column. The first highlighted line, Order 53971, the total column should be 8, not 25, as only 8 were closed 8/14. The second, Oder 53975, is fine as 25 closed on 8/14 and 25 total. The 3rd line highlighted, Order 54046, 4 closed on 8/14. The total column should be 4, not 25. I have posted the report below. I hope this helps. Thank you again for your patience.

	Cara Simpson OP @Reply 3 years ago

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View