Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Check up query (Access 2000)

    I want to build a check up query that checks two fields, the so called Saldo ad Stock for each affiliate and after that to issue a message enumerating the affiliates where the figures in Saldo and Stock do not match.Can you help?


    Public Function BatchCheckUp() ' check the difference between Saldo and Stcok for each office and at
    'the end make alist with the offices where there is a discrepancy.
    My idea is to check for each office, but how to do it in the code ?

    Dim StrSQL As String
    Dim StrOffice As Integer
    Dim StrItems As Integer

    ' cary out the check up for each office

    ' First office
    StrOffice = 1
    StrItems = 0

    ' Second office
    StrOffice = 2
    StrItems = 1


    StrSQL = " SELECT qryCrosstab.ProductID, Sum(qryCrosstab.[-1]) AS [SumOf-1], Sum(qryCrosstab.[0]) AS SumOf0, [SumOf-1]-[SumOf0] AS Saldo, products.items6 AS stock" & _
    " FROM qryCrosstab INNER JOIN products ON qryCrosstab.ProductID = products.Productid " & _
    " WHERE (((qryCrosstab.afid) = = " & InOffice & "))" & _
    " GROUP BY qryCrosstab.ProductID, products.items= " & InItems & ";"
    CurrentDb.Execute StrSQL


    End Function

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

    Re: Check up query (Access 2000)

    There could be several products for an office (affiliate) for which Saldo doesn't equal Stock. To display a message for each would be annoying.
    Instead of using VBA code, I'd design a query that selects the products (for each office) for which Saldo doesn't equal Stock. You can then create a report based on this query. This report will list the problem offices and products.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check up query (Access 2000)

    Thank you for yor reply. Making a report would enable us to enumerate exactly which products are not matched.But i do not need it.We have a special report. When opened the unmatched products are red coloured with additional data attached.In my case i need to build a quick check up from bird's view and to establish in which ofifces something is not in order. Regarldess if one or more products are concerned.We need to eliminate the offices where everything is ok.And, we expect, in the future everything will be OK, so our employees will save time only by clicking on the button and geting the information that everything is Ok ( hopefully).If i suceed we will not need to open every time the different reports to check up the situation in the affiliates,provided the query does not warn us of that.Thats why this question is so important for me

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

    Re: Check up query (Access 2000)

    You can have a message "All OK" (or whatever you like) displayed if the report has no data (i.e. if there are no discrepancies), by writing one line of code in the On No Data event of the report.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check up query (Access 2000)

    Thank you for the reply. My query cannot contain all the information for all the ofices. i am sending the attachment . Maybe my idea is not feasible.

    kind regards
    Attached Files Attached Files

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

    Re: Check up query (Access 2000)

    The problem is that the Products table hasn't been designed correctly. Having fields Items0, Items1 etc. is a bad idea. I have warned you about this before, in <post:=675,829>post 675,829</post:>. This structure is now causing unnecessary problems.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check up query (Access 2000)

    I know. You have warned me of that. Anyway you have done more than enough for me and i thank you for that. Very grateful indeed. I appreciate you very much.I wish you all the best.

Posting Permissions

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