Results 1 to 11 of 11

Thread: counting (2000)

  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    counting (2000)

    Hello, I have a diary system for chasing oustanding loaned materials in a one to many relation, the item being the one and each chase the many. On a form in list boxes the outsanding items items are shown, from clicking the list box the record opens in another form for modification/updating. The oustanding items list s are populated from queries. There are three list boxes, one for items that have been chased once (ie. one month old), one for two and so forth. I am struggling to find an automated way of telling the underlying queries how many times each case hase been chased and therefore populating the relevant list boxes. Currently each list box shows all the same details regardless of how many chases have been done. For info my query has the date of previous chase as a field.

    Hope this makes sense, thanks.

    Thanks Darren

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: counting (2000)

    Do you want to determine the number of chases from the date, or do you want to use the actual count?

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: counting (2000)

    Thanks Hans, I want the ability to have each of my lists of cases to show in the first list, cases that were due 1 month back from current day, second list two months and third list 3 months. Each time there is a chase the underlying table is populated with the chase date. Ideally using some kind of counter coupled with the date would be great so for instance in list two it shows cases that have been chased once and where the first chase was one month back.

    Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: counting (2000)

    That doesn't make it much clearer to me, I'm afraid. Can you provide detailed information about the structure of the tables involved, or post a stripped down copy of the database?

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: counting (2000)

    Darren
    Just thinking from a different angle and not being totally aware of your structures but have you thought of a cross tab query which runs a report? It may save you time mucking about with the table updates etc

    Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> worth
    Jerry

  6. #6
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: counting (2000)

    Hello folks, sorry for delay. Attached is a mock up. Two tables; 'name' and 'rec', one to many. One contains name of a lender, many details when we have chased for outstanding items. A query 'name query lists all 'chased items'. Form; 'form 1' when opened shows a list box of chased items, when you click on one of the items the record is opened in the form 'name' ready for the user to add another 'chase'. What I need is a way of restricting items in listbox on form1 to show items that have been chased only once, similary I will put another list box on to show items that have been chased twice. How can I model this?

    Thanks

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: counting (2000)

    See modified version. Look at the new queries, and at the code behind the form. (I switched things around a bit, but that's not essential).

  8. #8
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: counting (2000)

    Cheers Hans, marvellous.

    Darren.

  9. #9
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: counting (2000)

    Hans, further help on this please. I am using your solution which is great, recall this is a one to many relation with the one table detailing loan of products with the date it is loaned out, then the many dating and recording each time outstanding items have been chased, Your solution is great when there has been a chase, what I want to do now is incorporate into the relevant code, the functionality to display items that are due a first chase, i.e. ones where the many side hasn't been activated. Currenlty I have two list boxes, one for the cases yet to be chased but due and a second list with your solution. Any guidance is appreciated as ever.

    Thanks Darren.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: counting (2000)

    You can include the "no-chase" cases by using outer joins in the queries (you change the join properties by double clicking the line joining the queries). I also changed the row source of the combo box on the form to include 0 as count of chases. See attachement.

  11. #11
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: counting (2000)

    TVM again Hans.

    Darren.

Posting Permissions

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