Results 1 to 7 of 7
  1. #1
    ShostyFan is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    76

    counting sums

    I have a db that tracks a rental business.
    I have a query that returns how many open rentals are out any any given day. I enter the DOY and the query returns all rentals that have a start date before the entered DOY and either do not have a returned date or have a returned date that is greater then the DOY I entered.
    So if I enter the DOY of 6/1/2019 the query returns 100, this is how many open rentals I have out on that date.



    I want a query that will allow me to enter a date range, let's say 6/1/2019 - 6/5/2019 and it will return the open rentals I have out on each day. Ideally it would look like this:
    Date OpenRentals
    6/1/2019 100
    6/2/2019 102
    6/3/2019 104
    6/4/2019 102
    6/5/2019 107

  2. #2
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    In the criteria Between #6/1/2019# and #6/5/2019#

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Building on Alan's suggestion, you could have a parameter query that would take 2 date values - start and end of your date range. Run the query and it will prompt you for date values.
    Here's a sample that shows the format.

    Code:
    PARAMETERS [Bdate] DateTime, [Edate] DateTime;
    SELECT Orders.OrderID
    , Orders.OrderDate
    , Orders.ShipVia
    , Orders.ShipName
    FROM Orders
    WHERE (((Orders.OrderDate) Between [Bdate] And [EDate]));

  4. #4
    ShostyFan is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    76
    Quote Originally Posted by orange View Post
    Building on Alan's suggestion, you could have a parameter query that would take 2 date values - start and end of your date range. Run the query and it will prompt you for date values.
    Here's a sample that shows the format.

    Code:
    PARAMETERS [Bdate] DateTime, [Edate] DateTime;
    SELECT Orders.OrderID
    , Orders.OrderDate
    , Orders.ShipVia
    , Orders.ShipName
    FROM Orders
    WHERE (((Orders.OrderDate) Between [Bdate] And [EDate]));

    what i currently have is a startDate <=[Forms]![Search_Form]![StartDate_Rental] and
    returnDate Is Null Or >=[Forms]![Search_Form]![StartDate_Rental]


    This gets me a list of all the current rentals, I then run another query on this list that counts everything so I know on 1 particular day, I had X count of rentals
    All of this works great for 1 day, what I need is for these two queries to run for each day of the week
    and the between criteria suggested returns new rentals only, not the count of existing, minus returned, plus new.
    This is very difficult to explain and I hope this message is more clear. Thanks.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Step back and describe your situation with an example or 2 in plain English. I think your queries (2 of them) and a desire to get Access to do "something" is/has interfered with your focus.
    Access won't do something that you don't ask it to do. The key is to ask Access what you need, and in terms Access understands.

    Sounds like you have:
    -existing rentals
    -new rentals
    -rental returns
    and these can occur daily,weekly, hourly (various time periods).

    See this link from PBaldy to show how activities (rentals in your case) and dates relate.

    Make some test records and identify exactly what you want to be reported. You only need a few records to test your ideas.
    Get it working on paper, then do it with Access.
    Good luck.

  6. #6
    ShostyFan is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    76
    Quote Originally Posted by orange View Post
    Step back and describe your situation with an example or 2 in plain English. I think your queries (2 of them) and a desire to get Access to do "something" is/has interfered with your focus.
    Access won't do something that you don't ask it to do. The key is to ask Access what you need, and in terms Access understands.

    Sounds like you have:
    -existing rentals
    -new rentals
    -rental returns
    and these can occur daily,weekly, hourly (various time periods).

    See this link from PBaldy to show how activities (rentals in your case) and dates relate.

    Make some test records and identify exactly what you want to be reported. You only need a few records to test your ideas.
    Get it working on paper, then do it with Access.
    Good luck.
    Fair enough, and thank you for your patience.
    I have a rental business, products are rented and returned on a regular basis. It is desirable for me to know how many products are out on rent on any given day, past and present.
    I accomplished this by creating a form where I enter a single date, then this form calls a query and returns a list of every product that was out on rent during the day I entered. I then have another query that counts how many products are on the list and gives me a single quantity.
    My query that generates the list of products out on rent looks like this, and it works wonderfully:
    SELECT [Tbl Rental Instruments].Instrument, [Tbl Rental Instruments].BeginingDate, [Tbl Rental Instruments].DateReturned
    FROM [Tbl Rental Instruments]
    WHERE ((([Tbl Rental Instruments].Instrument) Is Not Null) AND (([Tbl Rental Instruments].BeginingDate)<=[Forms]![Search_Form]![StartDate_Rental]) AND (([Tbl Rental Instruments].DateReturned) Is Null Or ([Tbl Rental Instruments].DateReturned)>=[Forms]![Search_Form]![StartDate_Rental]));
    My business is very seasonal, so in September I do a lot of rentals, and then in June I get a lot of rental returns. I want to be able to enter a date range and have access return how many rentals were out on each day within that range. I don’t need to use the query I already have, it is just that since it gets me the qty on 1 day, I thought to modify it for a range of days.
    When I see a list of dates and how many instruments were out on each day I can then plot a line graph that will show me what will look like a parabola for the year.
    I hope this is more clear, thanks.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The critical part is to get your tables set up to match your "business".
    I'm attaching a "doctored" graphic provided to someone doing Equipment loans to employees.
    The Items being "loaned" were weapons with various calibers and associated with different Categories.

    In overview, in the draft,
    an Employee is assigned an Item (weapon) on a certain Date
    the weapon is to be returned on some agreedUponDate
    when the weapon is returned the return date is recorded.

    You will have Customer, Equipment/Instrument, Loans/Returns.

    I hope the draft model is useful to you.

    Your question is specific to a query (ies) to identify the status of your Inventory based on a Date or Date range.

    Click image for larger version. 

Name:	EquipLoanDraft.png 
Views:	9 
Size:	148.7 KB 
ID:	39923

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Getting Sums
    By Batselot in forum Access
    Replies: 4
    Last Post: 08-27-2018, 01:09 PM
  2. How to do Sum of Sums in Query
    By braveali in forum Access
    Replies: 4
    Last Post: 01-19-2014, 10:32 PM
  3. Query help with Sums
    By dhogan444 in forum Queries
    Replies: 1
    Last Post: 08-10-2012, 02:19 PM
  4. Sum of Sums?
    By Chameleon in forum Reports
    Replies: 1
    Last Post: 03-14-2012, 06:32 PM
  5. How do I sum multiple sums?
    By RobRay in forum Reports
    Replies: 6
    Last Post: 11-26-2010, 08:48 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums