Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Stock Balance (2000 sp3)

    I have a database with sales, purchases etc for various stock items. I would like to generate a form that shows (for each Month) the opening balance of the stock item, any Sales that month, any purchases that month, any stock transfers and any inventory adjustments
    I have individual summary queries for each of the 4 tables (Sales, Purchases, Transfers and Adjustments) that shows total per month but cant work out how to combine these into one form.
    I picture a form where the user chooses an item number from a combo box and this shows a datasheet with:
    Date Op Bal Purchases Sales Adjustments Transfers Closing Balance
    May 2004 100 50 40 -3 4 111
    July 2004 111 21 90
    August 2004 90 20 7 77
    Note no transactions for June so no line shown
    Any help appreciated
    Dummy from downunder
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stock Balance (2000 sp3)

    I don't know if this will be usable in a real application due to the complexity.
    Create a union query based on your 4 existing queries. (I use tables in my sample)
    Based on this union query, create a crosstab query.
    This crosstab query is the base for a select query to calculate the monthly balance.
    The Query BalanceWithOpening, give you the end result, totalising the balances for the previous months.
    Have a try in with real data.
    Francois

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stock Balance (2000 sp3)

    Francois; Thank you for both the speed and detail of your response.
    The Union query (very clever technique) is working well but I have obviously done something wrong with the PrevBalance technique as it is not working. My final version is shown below.

    PrevBalance: nz(DSum("Balance","Balance","[ItemNumber] = '" & [ItemNumber] & "' and [Date] < #" & Format([Date],"mmmm yyyy") & "#"),0)

    Rightly or wrongly I changed the date format part as I thought maybe that was the problem and my queries have the date formatted as mmmm yyyy. However all date formats I tried (including the Australian one of dd/mm/yyyy gave me a zero prev balance. I dont actually understand what this formula is doing so makes it hard to fix. Any explanation would be much appreciated
    Thanks again
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stock Balance (2000 sp3)

    First a little remark : Don't use Date for a field name. Date is reserved word (Function).

    The formula sum the field balance in the query balance where the Item number is the same as in the handled record and the date is smaller than the date in the handled record.
    Your field Date should be a date type and format as mm/dd/yyyy as we are looking for the dates that are smaller then the date in the current record. If we use a text field and compare April 2000 to January 2004, April 2004 will be smaller then January 2004.

    If you don't come out, can you post a stripped version of your db so I can have a look at it.
    Francois

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •