• Creator
  • #2196450

    Newbie – help creating code for Access 07


    by octavian1979 ·


    I am a newbie with script, and a beginner with Access 2007 with ability to create basic queries, etc.

    For the following table I am creating for work, I need code that will query to ONLY display the rows of Product Codes (PROD CODE), where subsequent rows of that product code of money1 and money2 columns (MON1 and MON2) do not net. Multiple rows exist, and if possible, we only need to see the rows that do not pair off for each product.

    The attached sample table is marked with asterisks for the rows that need to to be removed from the result of the query.

    SORRY, pasting the table is messy. Imagine the number values alternate, from column MON1 in first row to MON2 in second row, etc.

    I cannot hand-write code yet, so I would appreciate any help!

    AAI -120 W
    AAI -120 C
    AASS 15300 W
    AASS 15300 C
    AASS 44198 W
    AASS 44198 C
    AASS 804 W *
    AASS 904 C *
    ABC -11100 W
    ABC -11100 C
    ABJO -30 C
    ABJO -20 W
    ABJO -50 C
    ABXQ 2650 C *
    ADPW 2205 W
    ADPW 2205 C

All Answers

  • Author
    • #2838010


      by octavian1979 ·

      In reply to Newbie – help creating code for Access 07


    • #2837811

      Well that’s a total nightmare

      by tony hopkinson ·

      In reply to Newbie – help creating code for Access 07

      If you were using a database with table functions and or cursors. You could probably do with the Coalesce function as well as Mon1,Mon2 is a complete waste of space, but access doesn’t have that either…

      You have got to change that table.

      For a start there is no explicit order.

      Add a column Prod_Order,
      type integer and make it an identity, if you’ve nothing else. and if this is a permanent table, make it the priomary key.

      Rule one in database design never have a table without a key.

      get rid of Mon1,Mon2 with

      Select Prod_Order,[Prod Code]as Prod_Code,
      Case When MON1 is not null Then MON2
      else Mon1
      As Amount,CWM
      From [Product Codes]

      Call that qryProductCodes

      At that point you can find the first one
      with something like

      Select Prod_Code,Min(Prod_Order) as FirstInstance
      Group By Prod_Code
      From qryProductCodes

      Call the query say qryFirstInstance

      Then you join that back to qryProdcutCodes
      to get each first row

      Select pc.* From qryProductCodes pc, qryFirstIntance q
      Where pc.Prod_Order = q.FirstInstance

      Call That qFirstInstanceDetail

      and then again to get the sum of all the other ones,


      Select pc.Prod_Code,Sum(PC.Amount) as amount From qryProductCodes pc, qryFirstIntance q
      Where pc.Prod_Order <> q.FirstInstance
      and pc.Prod_code = q.Prod_Code

      Call that qryRestOfThem

      Then you can join those results to get the answer


      Select d.Prod_Code,d.Amount,d.CWM From
      qryFirstInstanceDetail d,
      QryRestofThem r
      Where d.Prod_Code = e.Prod_Code
      and d.Amount <> r.Amount

      Only other way would be to write a script to do it logic wise in VBA, but that’s a clueless numpty trick, or a one off power user script for analysis purposes.

      Sometimes you have to have a table design that makes it difficult to do some things, because others were more important, if you don’t, don’t…

      Why are you making things harder for yourself by having column and table names with spaces in them?

      PPs I’m not picking on you, this is classic newbie problem trying to use SQL which is effectively set based on data that can’t be described as a set.

      Don’t know how much new stuff I’ve sprung on you either :p

      I didn’t check any of it for dumbass errors either, the theory is sound though, if I’ve understood what you meant.

      • #2837772

        RE: Well that’s a total nightmare

        by octavian1979 ·

        In reply to Well that’s a total nightmare

        Not the subject line I hoped to see! Haha! Thank you for the detailed logic.

        I am a beginner with Access 07, and only know enough SQL to know the basics to follow the simplest lines. I will take a couple days after work to try to create the queries as described.

        My table structure is what it must be for the report we are given. In this case, we cannot chose our data or data structure. It is a new report, thus table, every day to overwrite the previous day’s data. We can combine data in order to do the queries, but when the results are displayed back out, the individual records must show, and the values must show under MON1 or MON2 as they did in the original table.

        Do these steps provide an output such as the following (if spacing keeps in this post), where the money values are identified by their original and respective column names?

        AAI -120 W
        AAI -130 C
        AASS 804 W
        AASS 904 C
        ABXQ 2650 C
        ADPW 2205 W
        ADPW 3205 C

        • #2837740


          by tony hopkinson ·

          In reply to RE: Well that’s a total nightmare

          Get rid of the step that takes Mon1 or Mon2 and amend the rest of the queries.

          Or do one more step and join to the original table by [prod code]

          When you get lumbered with this sort of drivel, break it down into steps doing a littel bit at a time, check teh intermediate results, then you can have a look at optimisation if required.

          Don’t do it all in one go from the start though, you’ll confuise the heck out of yourself.

          PS I was thing after I posted if you don’t like case, you could do

          Select IsNull(Mon1,0) + IsNull(Mon2,0)

          If you don’t coalesce these two fields though the next stages will be even more of a mare,

Viewing 1 reply thread