Create Form and ability to Query by Date to determine our Contract Obligation

[Sandboxer]

I want to be able to program Access to provide for me, by individual day, what my contract obligations are to my customers. Will Access recognize all the individual days in between a date range (simply a "from" date and a "to" date)? Additionally, I need to delivery a specific quantity of product when the customer's inventory is within about parameter levels. EXAMPLE:

"During the Date Range January 1, 2010 through April 30, 2010 when Customer ABC's inventory under Contract 123 is between 100,000 and 80,000 Customer ABC may withdraw 100 Widgets per day from Inventory, guaranteed deliverable"

"During the date range January 1, 2010 through April 30, 2010 when the customer ABC's inventory under Contract 123 is between 0 and 79,999 customer ABC may withdraw 50 widgets per day from inventory, guaranteed deliverable"

"During the date range May 1, 2010 through September 30, 2010, when customer's inventory is between 0 and 100,000, customer may withdraw 100 widgets from inventory, but delivery is interruptible."

"During the date range May 1, 2010 through September 30, 2010, when customer's inventory is between 0 and 80,000, Customer may deliver 100 widgets to inventory, guaranteed accepted."

"During the date range May 1, 2010 through September 30, 2010, when customer's inventory is between 80,001 and 100,000, customer may deliver 50 widgets to inventory, guaranteed accepted" etc.

These are the records needed (and if they already exist in a table somewhere I will use a drop down box)

Customer ABC (drop down from existing table)
Contract 123 (drop down from existing table)
Date Range From M/D/YYYY (not sure how to do this)
Date Range To M/D/YYYY (not sure how to do this)
Customer Inventory Between "From Value" "To Value" (drop down from existing table in which each Inventory Range Possible is given an Auto-Number primary key)
"Value" per day from or to inventory. (drop down)

For the QUERY that I envision, I'd want to be able to select a date range (probably the upcoming month but possibly just one day) to determine what my obligations are for that month or day for each contract / customer.

So, I'd want the query to return for me:
For the date range September 1 - 30, 2010

Customer ABC
Contract: 123
Maximum Widgets allowed to be stored under contract: 100,000
Inventory 80001 - 100,000
Maximum 50 Widgets per day
Inventory 0 - 80,000
Maximum 100 Widgets per day

Customer XYZ
Contract 789
Maximum Widgets: 200,000
Inventory ______ to _______
________ widgets per day

etc. Then, I'd like to be able to total up how many widgets per day we may be asked deliver.

I just don't know how Access will know that August 1 - 31, 2010 is within the range of April 1, 2010 through September 30, 2010. I currently keep all of this in one cumbersome spreadsheet and would love to make things easier. Also, same question relative to inventory levels:

Ideally I'd like to be able to forecast on a particular day exactly what my deliverability is based on each contract's current individual inventory level. So, the same question holds ... will Access know that 74302 is within the range of 0 - 80001 ?

Hope that makes sense %^} and thanks in advance for reading all the way to the bottom!

Sandy

[shaffy08]

you can use make-table query to get your contract obligation between two dates. use
Between [Type the beginning date] And [Type the ending date]
under criteria. when you run the query, it will ask you for the dates. sorry, i'm newbie so if its not what you are asking for, my apologies.

[Sandboxer]

Thanks for trying to help me, fellow newbie! That makes sense, I'll read up on make table queries ... I've probably done them without knowing ;^!

In the example that I will try, my "date range" is made up of two separate records, April 1, 2008 is in the field "From" and September 30, 2008 is in the field "To". Will the query be able to find all records for the range July 1, 2008 through July 31, 2008 ? Hmmm ...

[Sandboxer]

Thanks for trying to help me, fellow newbie! That makes sense, I'll read up on make table queries ... I've probably done them without knowing ;^!

In the example that I will try, my "date range" is made up of two separate records, April 1, 2008 is in the field "From" and September 30, 2008 is in the field "To". Will the query be able to find all records for the range July 1, 2008 through July 31, 2008 ? Hmmm ...

Okay ... I just took the Microsoft Online "Queries II". I think that I can say use the expression >June 30, 2008 for the From Field and the expression <August 1, 2008. Yes? I'll let you know if that works!

[shaffy08]

Thanks for trying to help me, fellow newbie! That makes sense, I'll read up on make table queries ... I've probably done them without knowing ;^!

In the example that I will try, my "date range" is made up of two separate records, April 1, 2008 is in the field "From" and September 30, 2008 is in the field "To". Will the query be able to find all records for the range July 1, 2008 through July 31, 2008 ? Hmmm ...

'm not sure. But if you place

Between [Type the beginning date] And [Type the ending date]

under criteria, access will ask you for beginning date and end date once you run the query. hope this help.