Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Unanswered: SQL Aggregates and COUNT

    Morning all,

    I'm having a problem with some of my SQL... Not so much a problem, but something I'd like some advice on.

    I have an SQL statement as follows:

    Code:
        strMLSQL = "SELECT " & _
                   "IIF(" & _
                       "ISNULL(ref_products.[Product vName]), " & _
                           "(" & _
                               "SELECT MAX(main.[Product Code] + '   (Product Description Not Found!)') " & _
                               "FROM main " & _
                           "), " & _
                       "IIF(" & _
                           "ref_products.[Product vName] = 'BESPOKE', " & _
                           "(" & _
                               "SELECT MAX(ref_products_BSPK.[BSPK Product vName]) " & _
                               "FROM ref_products_BSPK " & _
                               "WHERE ref_products_BSPK.[Product Code] = main.[Product Code]" & _
                           "), " & _
                           "ref_products.[Product vName]" & _
                       ")" & _
                   ") " & _
                   "AS [Product Description], " & _
                   "COUNT(1) AS [Quantity]" & _
                   "FROM main LEFT JOIN ref_products " & _
                       "ON ref_products.[Product Code] = main.[Product Code] " & _
                   "WHERE [Order Number] = 4444 "
    Which has two nested IIF statements, which work fine, but as you can see, take up quite a lot of room...

    You can also see that I've given it an alias of [Product Description] to whatever is found within these nested statements.


    The problem lies with the aggregate function line of; COUNT(1) AS [Quantity]... Obviously, this only returns one value, so I need to group by the nested IIF statements for this to become valid SQL.

    What I want to be able to do, is keep my nice formatting incase changes are to be made at a later date, but not have to create an extra long SQL statement, that looks like:

    Code:
        strMLSQL = "SELECT " & _
                   "IIF(" & _
                       "ISNULL(ref_products.[Product vName]), " & _
                           "(" & _
                               "SELECT MAX(main.[Product Code] + '   (Product Description Not Found!)') " & _
                               "FROM main " & _
                           "), " & _
                       "IIF(" & _
                           "ref_products.[Product vName] = 'BESPOKE', " & _
                           "(" & _
                               "SELECT MAX(ref_products_BSPK.[BSPK Product vName]) " & _
                               "FROM ref_products_BSPK " & _
                               "WHERE ref_products_BSPK.[Product Code] = main.[Product Code]" & _
                           "), " & _
                           "ref_products.[Product vName]" & _
                       ")" & _
                   ") " & _
                   "AS [Product Description], " & _
                   "COUNT(1) AS [Quantity]" & _
                   "FROM main LEFT JOIN ref_products " & _
                       "ON ref_products.[Product Code] = main.[Product Code] " & _
                   "WHERE [Order Number] = 4444 "
                   "GROUP BY " & _
                   "IIF(" & _
                       "ISNULL(ref_products.[Product vName]), " & _
                           "(" & _
                               "SELECT MAX(main.[Product Code] + '   (Product Description Not Found!)') " & _
                               "FROM main " & _
                           "), " & _
                       "IIF(" & _
                           "ref_products.[Product vName] = 'BESPOKE', " & _
                           "(" & _
                               "SELECT MAX(ref_products_BSPK.[BSPK Product vName]) " & _
                               "FROM ref_products_BSPK " & _
                               "WHERE ref_products_BSPK.[Product Code] = main.[Product Code]" & _
                           "), " & _
                           "ref_products.[Product vName]" & _
                       ")" & _
                   ")"
    So what I'm asking, basically, is why can't I simply use:

    Code:
    GROUP BY [Product Description]
    Instead... And, as that doesn't work, what other options are available to me?

    I mention losing the formatting, as Access only allows 20 or so lines for continuous strings, which means that at the moment I have to use the long winded SQL code above, compressed onto a couple of long lines, which will make it incredibly difficult to decipher if I need to update/change it at a later date.


    Any suggestions, as ever would be more than welcome.

    Thanks guys!
    Last edited by kez1304; 01-17-12 at 06:43.
    Looking for the perfect beer...

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you cant use the result of a compond statement such as IIF in a group by clause.
    the real problem is your table design, whether you'd regard that as an Access limitation or a design limtiation is moot.
    a product is a product, but iof there are differences between types of products such a generic, bespoke and so on then push the details of those differences to a sub table have a google at sub/supertypes

    ID push the product description IIF's to a query and join on the product ID to that query in this query

    incidentally although using + to concatenate strings in Access works, it only works on strings if the value of the strings is numeric you are adding the values NOT concatenating.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Quote Originally Posted by healdem View Post
    you cant use the result of a compond statement such as IIF in a group by clause.
    the real problem is your table design, whether you'd regard that as an Access limitation or a design limtiation is moot.
    The problem comes from the fact that all the data used in this database is coming from our Sage database... The problem with that is that we make custom products, that all go under a couple of product codes, and in the description in Sage, the product is explained (colours, sizes, etc).

    As this import from Sage is going to occur everyday, the database needs the ability to breakdown a code such as, BSPK into, BSPK CNT GR GY 33 ED, based around its description, and what the product actually is... The further problem is that to perform this breakdown, and re-product coding, someone has to manually decipher the description, and input what exactly it is, manually. This is a one way street, nothing is written, and can't be written back to the Sage database. So whenever the data is imported, there needs to be a system in place to prevent my code from asking you for a breakdown over and over again, because the product code being imported is BSPK, thus the existence of the extra table, to lookup the description against the already manually created breakdown.


    Quote Originally Posted by healdem View Post
    ID push the product description IIF's to a query and join on the product ID to that query in this query
    I don't quite follow what you mean, but I'll have a think on it and try and put something together... I really do hate SQL...


    Quote Originally Posted by healdem View Post
    incidentally although using + to concatenate strings in Access works, it only works on strings if the value of the strings is numeric you are adding the values NOT concatenating.
    Thanks for that, I'll change it to use the CONCAT() function instead.
    Looking for the perfect beer...

  4. #4
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Cool

    Finally got it fixed... After searching and thanks to your advice healdem, I found that I could dump the bulk of the query into a subquery within my statement.

    The final working code is:

    Code:
        strMLSQL = "SELECT x.[Product Description], COUNT(1) AS [Quantity] FROM " & _
                   "(SELECT " & _
                   "IIF(" & _
                       "ISNULL(ref_products.[Product vName]), " & _
                           "(" & _
                               "SELECT MAX(main.[Product Code] & '   (Product Description Not Found!)') " & _
                               "FROM main " & _
                           "), " & _
                       "IIF(" & _
                           "ref_products.[Product vName] = 'BESPOKE', " & _
                           "(" & _
                               "SELECT MAX(ref_products_BSPK.[BSPK Product vName]) " & _
                               "FROM ref_products_BSPK " & _
                               "WHERE ref_products_BSPK.[Product Code] = main.[Product Code]" & _
                           "), " & _
                           "ref_products.[Product vName]" & _
                       ")" & _
                   ") AS [Product Description] " & _
                   "" & _
                   "FROM main LEFT JOIN ref_products " & _
                       "ON ref_products.[Product Code] = main.[Product Code] " & _
                   "WHERE [Order Number] = 4444" & _
                   ") AS x " & _
                   "GROUP BY x.[Product Description]"
    Thanks for your help buddy, learnt a lot about what's actually going on in the query, as opposed to using trial and error and getting frustrated.
    Looking for the perfect beer...

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    vba concatenation symbol is &

    eg:-
    strMyFullName = Title & " " & Forenames & " " & Surname & " " & Qualtifications
    you should be able to break apart your description form sage using the split function

    in fact whilst you are at it its probably worth getting up to speed on the string manipulation functions

    what you could do is set up a table within your Access application which knows the codes used in sage and then conflates the coded description into something more meaning full.
    when you import from Sage then as part of that process identify which codes you haven't already got defined and then encourage soemone to define those codes.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    If only that were possible.

    If Sage was set up with properly coded products, none of this would be an issue... Unfortunately anything that isn't standard has a generic 'BPK' code, and an often lacking description...

    As people will need to know how many of a certain colour we used for a certain peroid of time, for a certain type and size of product, this makes my life a pain, as all I can grab from Sage is for these bespoke orders is:

    Product Code: BPK
    Description: Some random, usually vague description of what colours/materials/etc it's made from

    This is the case for about 900 orders out of the 40,000 or so held in Sage, all those 900 will have the same Product Code, but will refer to totally different products... But this is getting beyond the scope of this conversation I feel.


    Thanks for the links, but I'm already pretty versed in the stock VBA functions, my database at it's current state is just over 12,000 lines of code... SQL is where I tend to fall down a little, but you know, I'm learning slowly but surely.


    Thanks for your help once again buddy.

    All the best.
    Looking for the perfect beer...

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK so you have a desctiption in Sage of
    BSPK CNT GR GY 33 ED
    have a tabel in your applciation that stores the code alomng with a descriptionj
    Code:
    CNT 
    GR   green
    GY   Gray
    33
    ED
    then pick up any discrepancies ie where the code from the sage system isnt' known within the Access application
    that would eliminate most of the re writing you refer to as
    he further problem is that to perform this breakdown, and re-product coding, someone has to manually decipher the description, and input what exactly it is, manually.
    if you redefine new codes as they are discovered then you will reduce the amount of manual retyping. the problem with an automated process is however if the same code is used for different words / description
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Quote Originally Posted by healdem View Post
    OK so you have a desctiption in Sage of
    BSPK CNT GR GY 33 ED
    Nope. I have a PRODUCT DESCRIPTION in sage of:

    Code:
    BPK VI1101 TAKAMURA BLACK/IVORY 16SA
    Or,

    Code:
    EXTRA LARGE 13VC - IVORY / NATURAL
    Or,

    Code:
    Hankyu Takahara's 21TC
    Or,

    Code:
    PATTERN ORDER SAMPLE ROYAL BLUE 21TC
    All of which will have exactly the same PRODUCT CODE of: BPK

    ... The descriptions are totally random, input by whoever is processing the order in Sage. Thus why I needed to have an area for people to breakdown the code manually.

    The problem was occurring because when these are re-imported the next day, all the database can see is the product code 'BPK', and would request breakdowns for all of them, all over again.

    So, to counter this, I set up an extra table, that contains all the Descriptions (the examples above), and what the user created as a NEW product code...

    For example...

    Imported from Sage:
    Product Code - BPK
    Product Description - EXTRA LARGE 13VC - IVORY / NATURAL

    Information that's stored in the new table (product code manually created by the user):
    Product Code - BPKCNTINA13VC
    Product Description - EXTRA LARGE 13VC - IVORY / NATURAL


    With this extra table storing all the lookup information for new orders, I can use a script to check if that product description has already had a new Product Code assigned to it. If it has then it can be automatically updated before being appended to the databases main table, otherwise, the user will have to provide a new one.


    Now it's all set up and working, once a bespoke product is broken down once, it'll never require it again... The only downside to using this method is that if two products happen to have exactly the same description, but are physically different products... This is a failure on the Sage inputters part, not on the database itself though.
    Looking for the perfect beer...

Posting Permissions

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