Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Home > Forums > Access
Back to Access Forum    Comments List
Salesboard Starting again Upload Images   Link   Email  
Dan Jackson 
Hi All,

Intro
I apologise for the length of the post, i'm trying to keep it simple but this is mind blowing! Rather than trying to extinguish a forest fire with a squirt gun, i want to redo this form entirely! What do i need to research?  Many thanks

Form/Query Setup
At the moment, each figure is using its Row Source query to calculate which is very inefficent and difficult for anything more complex.

The Three "Types" of Data Calculated
The Names are the Sales Advisors in the Office
You've got date ranges Month to Date and Year To Date
Finally theres the difficult bit - Essentially Sale Status (Theres a field called status which is used for filtering)

Written - When a sale is made/agreed
Commission Due - Between Written and Issued
Issued - When we have been paid commission on said sale
Remaining Pipeline - All Sales at Written stage (Not date filtered)

To further complicate matters, the Written figure calculated has to use an if statement that basically says "If the Sale is issued, use the Issued figure. Otherwise, use the Written figure". This is because we might "Write" a sale for figure X but the issued figure might be different. This calculation is shown in picture 2

Finally, i've just been asked to change the issued figure... This will require calculating on its own grrrrrrrrr

Essentially need to perform a number of calculations, then maybe insert the figures into a table on a schedule to display on the board. Where do i begin???


Dan Jackson 

Dan Jackson 

Dan Jackson 
Perhaps i can explain another way... I need to display a table like the above, in the most efficient way possible.
You've got the Month To Date and Year To Date, as well as the peoples names filters - these are self explanatory.

Written Figure is Calculated as Sum (Add up the Written Figures Unless the Issued Figure is More than 0, then use the Issued)

Issued Figure is Calculated as Sum (Sum up all Issued Figures).
Sum up All Figures in "Clawback" and deduct from yearly Issued Figures.

I need to be able to perform all these calculations to output into individual figures as above. It was suggested that I output to a table on a schedule by Adam here (https://599cd.com/blog/display-article.asp?ID=352&CommentID=49980#StartOfComments). Any good training suggestions?

Dan Jackson 
Just reviving this one...
Dan Jackson 
If i did one query where

Column 1 Calculation of Issued Figure
Column 2 Calculation of Clawback Figure
Then Column 3 is Column 1 - Column 1, would that work?

I'd then just need to work out how to filter the individual calculations (For example, by date/advisor etc).

I hope this helps make it easier...
Adam Schwanz 
This is only for the purpose of filling in the fields on that "dashboard" looking form? If so, maybe just use DSUM, I don't think queries are going to be any faster than that, it just might take a second to load the values in (which is where storing in a table MIGHT come in handy if things get too many records and too long of a delay in calculating.
Dan Jackson 
Awesome cheers. I'm not familiar with DSUM so I'll look it up. There won't ever be more than a years figures since it's filtered YTD and MTD and it already has since Jan 2021 in there so I don't think it should get any slower. Takes a few seconds to load the dashboard but that really doesn't matter, it works. My two concerns are

Building in the additional calculations and
Being a stickler for too many queries!
Adam Schwanz 
Yea, I think he uses DSUM in Dashboards but basically you could just add multiple where criteria to it, like where name = joe and date > 1 year ago and type = written or whatever. That would fill in those unbound fields in that dashboard if that is the goal.
Dan Jackson 
Been watching all the video's on dlookup and dmax, including the dash. Doing well but I get a syntax error when i add this second Where criteria (First works fine).

=DSum("NetIssuedAMT","SalesAllQ",""Status=""Paid"" AND "Combo_AdvisorID=3")

The Table field is called AdvisorID
The Field Control is called Combo_AdvisorID

Really appreciate the help. To think this morning I knew nothing about DLookup and DSum and was dreading it to understanding how it works and how to build them in just a day! Unfortunately, i'm a bit in the deep end with this one but that'll just mean the learning will be easier!!!

Cheers
Kevin Robertson 
You have too many quotes. Try this:

=DSum("NetIssuedAMT","SalesAllQ","Status=""Paid"" AND Combo_AdvisorID=3")
Dan Jackson 
I'm watching the Double Double Quotes Video. Your example works but i'm trying to understand why the quotes before AdvisorID are not needed (Removed combo_ as it needs to be the table field, not the control).

=DSum("NetIssuedAMT","SalesAllQ","Status=""PAID"" AND AdvisorID=3")

To Me, its

Open Quote
Status=

     OpenQuote
     "PAID"
     Close Quote

     AND

*Here is the double quote not needed. This is because it isn't a string but rather a function
AdvisorID=3
CloseQuote

Yeah, makes sense as i'm typing it out. I'm getting confused by the data types and have to remember that only text strings need to be enclosed. Definitely the toughest part of the learning experience so far.
Adam Schwanz 
The only times you should need to use quotes are for text strings ""PAID"" or when you are calling a value that changes (that I can think of right now haha) ID=" & FieldName or ID=""" & ShortTextField & """" or ID=" & DLOOKUP("Field","table"Where") etc.
Dan Jackson 
This is my best attempt so far

=DSum("NetIssuedAMT","SalesAllQ","Status=""PAID"" AND AdvisorID=3 AND StatementDate>=# & "DateSerial(Year(Date()),Month(Date()),1)" & "#""")
Adam Schwanz 
Too many quotations. Remember you are only using quotations to open and close the statement, or to signify a string

=DSum("NetIssuedAMT","SalesAllQ","Status=""PAID"" AND AdvisorID=3 AND StatementDate>=#" & DateSerial(Year(Date()),Month(Date()),1) & "#")
Adam Schwanz 
Also & are only ever going to come after/before a quotation (again, that I can think of now, morning brain still LOL). " & Field & "
Dan Jackson 
Thank you. Its 15 mins to the end of a work day where i've been at this all day so i'm gonna bookmark here. Will try and pick up again next week, also push on with the course.

Somewhere out there, my math teacher is laughing!!
Dan Jackson 
Hi All,

The DSUM Adam shows above generates the figure required (ThumbsUp) but i still need to deduct another figure
(Do i just tack the other figure on like =DSUM(~~~~~~ & "#") - NEXT DSUM).
This also then needs to happen for each advisor (x7) plus other calculations.

I'm just thinking it's a LOT of calculations for Access to handle. Is it most efficient to perform all this directly on the form which requeries itself periodically?

Thank You
Adam Schwanz 
It is a lot of calculations, so you're going to have a little lag probably starting it up, I don't think you have so much that you have to go the table route with just those few fields, although if you need to refresh that form a lot that may be an option for some values that don't change as often.

No matter where you put the calculations, query or a different form or wherever, access still has to do the same calculation. So you're not really saving any time. Just throw it all on the form and see if it can handle it. If it can't you might have to make subforms on that form, if it still can't handle it then you need to look into setting the values to a table.

And yea, if you still need to do more calculations, just add/subtract them. If you're doing it in the on current event of the form, I like to break it up so I can actually see what I'm doing instead of a 500 character line. So I'd personally probably do

Dim A, B
A = Dsum...
B = Dsum...
FieldName=A-B


Dan Jackson 
I'll do that, thanks! It'll be on a timed event but it's only used on the salesboard (I did a similar sheet people can access but doesn't auto requery, its static)

Quick question, with the Dim, is it exactly as you have typed or do i have to

Dim A as a String, B as a string

Thanks buddy
Dan Jackson 
Something isn't calculating right..... Ignoring the Clawback "B" section (Which is calculating correctly), CommRec "A" is Way Too High.


DSum("CommRecAMT", "SalesAllQ", "AdvisorID=8 AND ""StatementDate>=#" & DateSerial(Year(Date), 1, 1) & "#""")
Generates a figure of '266,051.39 (I'm using a test textbox called "TEXT1" on the form) SEE 1st Picture

I ran a query with the same criteria (PIC 2) and it shows 65 Records with the (At least, much closer to) Correct Amount of '80,070 (PIC 3)

Where have I gone wrong with the DSUM to calculate such an incorrect figure that the Query gets right? Thank You
Dan Jackson 

Dan Jackson 

Dan Jackson 

Dan Jackson 
I've just figured out, i think the issue is with the DateSerial. I am really struggling with that one. I noticed on the Clawback, i had it set to this month only. I corrected it to this YEAR and the figure didn't change... Also, Access hasn't corrected the bit at "ADVISORID=8 AND"      to      "Advisor = 8 And"
Dan Jackson 
This is the best i could come up with -
~~~, "AdvisorID=8 And "StatementDate>=#" & DateSerial(Year(Date), 1, 1) & "#"")



I wrote this out as
"AdvisorID = 8 And
     "StatementDate>=#"
             &
              DateSerial(Year(Date), 1, 1)
                    &
                     "#"
                         "

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

 
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 3/28/2024 8:47:57 AM. PLT: 0s