Hello all...
I have what I hope to be a very simple question and I am sure it is, to all the experts out here....but to us mere mortals, its not so easy. I have been researching for days and cannot figure it out... LOL
Anyway so I am trying to figure out how to craft a query that will "dynamically" query between the 29th day of month 1 through the 28th day of the following month.
Let me be more specific by way of example:
Lets say I own "My Big Fat Used Cars" car dealership and I have several hard working salespeople out there very day on the lot selling cars.
I have established a predetermined "selling window" which runs from the 29th of one month to the 28th of the following month. This is "selling window" helps me keep track of cars being sold for any given month and to establish/monitor sales quotas for each of my salespersons.
Lets call this selling window, the monthly sales cycle. The fiscal sales cycle runs on a monthly basis and it runs from the 29th day of month 1 through the 28th day of the following month.
Now lets say my dealership processes the " cars sold report" on the 29th day of every single month which shows all the sales from my sellers during the sales cycle. This report reflects (by salesperson) what each salesperson has sold during the fiscal sales month. Also on the 29th day, a new sales cycle begins which will run through the 28th day of the following month...and son and so on...through the complete calendar year.
I have tasked each of my salespeople to track how many cars they have sold during the active sales cycle and give me daily reports of their sales activity.
So now lets flip the page and say I am one of those hard working salespeople out there selling cars. Because I am tech savvy salesman heh heh, I am going to create a database that tracks my sales so when the boss man or woman barks out they want my report, I have it all ready and waiting.
Now lets say I have created a database to track my daily sales called, "MyBigFatDatabase." and inside my MyBigFatDatabase, I have the following table:
tbl_MyCarsSold
Inside of MyCarsSold I have 7 fields:
ProductID (PK) Autonumber
CarModel Shorttext
CarMake Shorttext
CarYear Shorttext
Sold Yes/No
PriceSold currency
DateSold Date/Time
So....I want to be able to create a query in my database that basically says, show me all the cars sold during the monthly sales cycle. This will be achieved by checking the "Sold" Yes/No field and selecting a date in my tbl_MyCarsSold table Ok I know what you thinking...."Just do a query and use the Between criteria on the DateSold Field"....easy enough right?
But here is the catch....the query needs to be dynamic. Meaning, I need to build a query that will dynamically account for every single months sale cycle. Specifically, it will go from 29th day of each month through the 28th day (at midnight) of the following month. As you know.....the number of days in the calendar months are not always the same and I can't figure out how to create a query that will change dynamically (date wise speaking) to reflect the current sales month.
Any thoughts?
Also...as a bonus question...once I get this figured out, I plan to build a dashboard in the database that has an unbound form which I can place various text fields (some bound...and some not bound) to display various bits of monthly tracking data. For instance a unbound text box that will display the result of the query I build above...I will do this by using the DLookup function within the record source of the text box's data control. I will also create "speedometer" gauges to reflect percentage of cars sold (in terms of dollars) divided by my monthly quota. This will display my percentage of quota achieved for the active sales month by way of a "speedometer" gauge.
Anyway, these are just after thoughts and I am rambling... I apologize.
So back to the query question....
Any thoughts or help from the Access Gurus?
Sincerely in your debt for your advice.
-mgm