14 Replies Latest reply: Jun 21, 2012 3:30 PM by bregent RSS

    single record per database field

    aonefun Community Member

      For the following query, I'd like to ensure that only one record per ManufacturerParNumber results as currently there are duplicates. How can the code be modified accordingly?

       

      "SELECT OptionsHeaders.ID AS OptionsHeadersID, SortOrder, Products.ID AS ProductsID, Products_1.ID AS Products1ID, url_img, ManufacturerPartNumber, Description, ? FROM accessories WHERE Products_1.ID = ? OR ProductFamily=? ORDER BY SortOrder DESC"

        • 1. Re: single record per database field
          bregent MVP

          Please explain what you mean by duplicates. If your entire select list is duplicated, then you can use the DISTINCT keyword to remove duplicates. If the other columns are different for a given ManufacturerPartNumber, then you need to decide which ManufacturerPartNumber you want to include. Otherwise it makes no logical sense to include those other columns.

           

          In other words, if you have two OptionHeaders for a given ManufacturerPartNumber, which OptionHeader do you want to display?

          • 2. Re: single record per database field
            bregent MVP

            I'm also very confused with your naming conventions.

             

            "OptionsHeaders.ID" appears to be a table called OptionsHeaders and a column called "ID", yet you are only selecting from a table called accessories. What's up with that?

            • 3. Re: single record per database field
              aonefun Community Member

              many thanks for your replies

               

              this is a many to many select query whose fields are populated from 3 different tables, each having their own ids. I have built my website database around an existing one that is the backend of our quoting software and that is where OptionsHeaders.ID comes in. I'm not sure why it has been included in the access query though when remving it, the query brings up too many results. When removed from the Dreamweaver query, it makes no difference to end results.

               

              The query partially results in the following records:

               

              OptionsHeaders.ID    Products.ID  ManufacturerPartNumber   Products_1.ID

              17                                 1041              9220                                     499

              18                                 1041              9220                                     3

               

              For filtering against the querystring, i would need both these records to result in the recordset but for displaing on the page, i would only want either one of the above records (one record per ManufacturePartNumber) since only the fields that are the same for both records are the ones displayed on the page.

              • 4. Re: single record per database field
                bregent MVP

                >For filtering against the querystring, i would need both these

                >records to result in the recordset but for displaing on the page,

                 

                Sorry, I'm not following you. What info are you passing in the querystring? And is this used in the WHERE clause of the SQL you posted?

                 

                >i would only want either one of the above records (one record per ManufacturePartNumber)

                >since only the fields that are the same for both records are the ones displayed on the page.

                 

                Then perhaps you need two recordsets. One for what you want to display on the page, and the other for, well, whatever it is you need it for   Some more context of what you're doing would be very helpful.

                 

                This will give you one row for each MPN given the data above.  So I still need to know what you need those other columns for.

                 

                "SELECT DISTINCT Products.ID AS ProductsID,  ManufacturerPartNumber FROM accessories WHERE Products_1.ID = ? OR ProductFamily=? ORDER BY SortOrder DESC"

                • 5. Re: single record per database field
                  aonefun Community Member

                  The issue can be seen on the following webpage where on the right side there are 2 listings for the #9220 dollies:

                   

                  http://www.canchair.com/new_web_product_detail.asp?ProductID=497&ProductFamily=5&ProductFa milySub=

                   

                  I have narrowed down my query to contain only the essential fields as follows and parameters have been replaced with the querystring values for demonstrative purposes:

                   

                  SELECT ManufacturerPartNumber, url_img, Description, Products_1.ID AS Products1ID, ProductFamily, SortOrder, Products.ID AS ProductsID, ?

                  FROM accessories

                  WHERE Products_1.ID = Request.QueryString("ProductID")

                  OR ProductFamily=Request.QueryString("ProductFamily")

                  ORDER BY SortOrder DESC

                   

                  The first 3 fields are rendered on the actual webpage for the product description and the next 2 consecutive fields are used for querystring filtering as indicated above. SortOrder is used for ORDER BY and ProductsID is required for some reason otherwise no records display at all.

                  • 6. Re: single record per database field
                    bregent MVP

                    >The first 3 fields are rendered on the actual webpage for the product description

                     

                    OK, no problem here as the MPN, image url, and descriptions are the same for each row, right?

                     

                     

                    >and the next 2 consecutive fields are used for querystring filtering as indicated above.

                     

                    The querystring that I see in the results is using only ProductID. I don't see where Products1ID or ProductFamily is being used.  Can you explain the difference between ProductsID and Products1ID? 

                     

                    "http://www.canchair.com/productfamily_processor.asp?ProductID=1041"

                     

                     

                    >SortOrder is used for ORDER BY and ProductsID is required for some reason otherwise no records display at all.

                     

                    Can you try this query:

                     

                    SELECT DISTINCT ManufacturerPartNumber, url_img, Description, Products.ID AS ProductsID

                    FROM accessories

                    WHERE Products_1.ID = Request.QueryString("ProductID")

                    OR ProductFamily=Request.QueryString("ProductFamily")

                    ORDER BY SortOrder DESC

                     

                     

                    If that works however, I'd be tempted to fix this at the accessory query in Access as it should not return duplicate products, unless of course it is used in other queries where those additional columns are required.

                    • 7. Re: single record per database field
                      aonefun Community Member

                      The scenerio is as follows.

                       

                      The models featurd on the following webpage are all the same product except for their colour thus model numbers (Products_1.ID) are different though they all share in the same ProductFamily:

                       

                      http://www.canchair.com/new_web_product_family.asp?productfamily=5

                       

                      When clicking on any given model (Products_1.Id), for example #2250Y (burgundy) you are brought to this page.

                       

                      http://www.canchair.com/new_web_product_detail.asp?ProductID=497&ProductFamily=5&ProductFa milySub=

                       

                      Regardless of which colour product you choose to view, the accessories will be the same (Products.ID). Therefore, yes, the only field bringing varied results per MPN is Products_1.ID field.

                       

                      In the access query designed as a many to many relationship, we are declaring which Products_1.ID should display which accessory products (Products.ID). I do not want to have to declare the accessories for each individual model (Products_1.ID) sharing the same ProductFamily, therefore explaining the Dreamweaver query portion of "

                      OR ProductFamily=Request.QueryString("ProductFamily")". However, in the event that a Products.ID is declared for more than one member of a given ProductFamily, there is a duplicated listing of the accessory as previously demonstrated.

                       

                      Unfortunately placing the DISTINCT word as you have suggested, creates no results at all.

                      • 8. Re: single record per database field
                        bregent MVP

                        OK, so I think I understand your data model a little better. Can you try this:

                         

                        SELECT DISTINCT ManufacturerPartNumber, url_img, Description, Products.ID AS ProductsID

                        FROM accessories

                        WHERE Products.ID = Request.QueryString("ProductID")

                        OR ProductFamily=Request.QueryString("ProductFamily")

                        ORDER BY SortOrder DESC

                        • 9. Re: single record per database field
                          aonefun Community Member

                          many thanks once again for the time you have taken to respond. Unfortunately this brings zero results as well. I do believe that the solution may have something to do with using the group by function but not sure how it applies.

                           

                          Please consider also that not all products are members of a productfamily.

                          • 10. Re: single record per database field
                            bregent MVP

                            You do not need to use a GROUP BY since you are not aggregating anything. The DISTINCT keyword should eliminate the duplicate rows. If you are getting zero rows then the problem is with your WHERE clause. Unfortunately, most of the links you have provided do not seem to work for me so it's difficult to understand the workflow. Let me make sure I understand what your problem is:

                             

                            You have a page that returns a recordset that contains one item. The page also has another recordset that lists related items.  Or, on some pages I see various color options listed. Which is the recordset that you are having problems with? What are the related columns between the two recordsets?

                            • 11. Re: single record per database field
                              aonefun Community Member

                              Sorry you have had trouble with the links. I have removed the "distinct" keyword" so that the accessories recordset should bear results. The recordset of discussion is that of the accessories that are shown on the right pane of the following webpage (beneath the colours recordset). You will notice a duplicated lising of model #9220:

                               

                              http://www.canchair.com/new_web_product_detail.asp?ProductID=3&ProductFamily=5&ProductFami lySub=

                               

                              One listing is due to the where clause portion of " Products.ID = Request.QueryString("ProductID")" and one for "OR ProductFamily=Request.QueryString("ProductFamily")"

                               

                              As explained, the reason for the latter part of the where clause is to avoid having to match up each colour chair (Products_1.ID) with the same accessory (Products.ID). Rather, by matching up any one chair colour it would automatically cause the specified accessory to appear on the webpage of each individual chair colour sharing the same productfamily. I can't simply list the latter part of the where clause since not all products_1.ID are members of a productfamily.

                               

                              In response to your questions, yes the above linked page returns a recordset that contains one item (black folding chair). The recordset is filtered by "Where ID = Request.QueryString("ProductID")". The other recordset listing related items is the accessories discussed above.

                              • 12. Re: single record per database field
                                bregent MVP

                                If adding the DISTINCT keyword causes 0 rows to be returned, then there's a problem somewhere else. Rather than trying to troubleshoot the recordset/webpage, lets work directly in MS Access. Can you please create the exact same query and include the filter values for ProductID and ProductFamily that you are testing. Post the resulting rows.

                                Then, in the query properties toggle "Unique Values" to Yes and execute again. Post those results.

                                • 13. Re: single record per database field
                                  aonefun Community Member

                                  Sorry for the great delays in between posts as I am working on several projects at once.

                                   

                                  I have done as suggested. With and without declaring unique value as yes, the results remain as follows. As you will see each row is unique. The issue is that I would like either row 2 or 3 to display with the distinct value being the sixth column which is the Products1ID field (9220, 9840).

                                   

                                  17207499images/9840.jpg9840Hanging Chair Dolly - Suitable for #2250, #2700, #1284, #1285 models - Simple Assembly Required2250 Series Plastic Folding Chair
                                  17101041499images/9220.jpg9220Optional Heavy Duty Dolly for Plastic Folding Chairs - Suitable for model #2250 chairs only2250 Series Plastic Folding Chair
                                  181010413images/9220.jpg9220Optional Heavy Duty Dolly for Plastic Folding Chairs - Suitable for model #2250 chairs only2250 Series Plastic Folding Chair
                                  • 14. Re: single record per database field
                                    bregent MVP

                                    Why is your result set returning columns #1 and #4 ?