Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2012
    Posts
    28

    Unanswered: Query doesn't order once Where Clause is added

    Hi. I have an SQL problem in Access, so I'm not sure if this is an issue with SQL itself, or the Access version of SQL. I've posted this in the Access forum but haven't had a reply yet.

    In my BM_Maps subform, there is a combo box called called Borders. I want the borders to be filtered based on product series, so if the user picks "City Guide", they only get city guide borders, so I have added a table called Product_Series_X_Border which has the product series and border id.

    I have a query which joins two tables, and I've ordered it by the border name, which works fine, until I add a where clause for the product series from the other table. I have attached a screengrab which I hope helps. I have tried joining other tables but it still doesn't work. Is there any other way I can order by border name with the where clause? I've tested with individual product series as the where clause with the same result.
    Attached Thumbnails Attached Thumbnails Screen shot 2012-08-31 at 9.36.58 PM.png  

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Are you using a SQL Server backend?
    If so are yiu using pass through queries?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2012
    Posts
    45
    Quote Originally Posted by healdem View Post
    Are you using a SQL Server backend?
    If so are yiu using pass through queries?
    From the picture, it looks like the OP is using linked tables to a SQL Server back end. Access will replace the '.' with a '_' between the schema and table name.

  4. #4
    Join Date
    Mar 2012
    Posts
    28
    That's right. At the moment I am just copying the SQL tables but when this tool is rolled out it will link to the SQL database.

    If it helps here is the code.

    Code:
    SELECT DISTINCT dbo_BM_Borders.BORDER_ID, dbo_BM_Borders.BORDER_NAME, dbo_PRODUCTSERIES_X_BORDER.ProductSeries
    FROM dbo_BM_Borders INNER JOIN dbo_PRODUCTSERIES_X_BORDER ON dbo_BM_Borders.BLING_ID = dbo_PRODUCTSERIES_X_BORDER.BorderID
    WHERE (((dbo_PRODUCTSERIES_X_BORDER.ProductSeries)=[Forms]![BM_Product]![Prod_Series]))
    ORDER BY dbo_BM_Borders.BORDER_NAME;

  5. #5
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    It would help if you properly indented your query before posting. What you posted will be looked at by some, but many will just move on to something else.

  6. #6
    Join Date
    Mar 2012
    Posts
    28
    Sorry, Access SQL syntax is a little annoying, so the query is longer than it would be in SQL Server. Is this any better?

    Code:
    SELECT DISTINCT dbo_BM_Borders.BORDER_ID, dbo_BM_Borders.BORDER_NAME,
    	dbo_PRODUCTSERIES_X_BORDER.ProductSeries
    FROM dbo_BM_Borders INNER JOIN dbo_PRODUCTSERIES_X_BORDER ON
    	dbo_BM_Borders.BLING_ID = dbo_PRODUCTSERIES_X_BORDER.BorderID
    WHERE (((dbo_PRODUCTSERIES_X_BORDER.ProductSeries)=
    	Forms]![BM_Product]![Prod_Series]))
    ORDER BY dbo_BM_Borders.BORDER_NAME;

  7. #7
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Suggest you write a standalone query and try the same WHERE on the ProductSeries table.

    I suspect there may be a datatype difference.

  8. #8
    Join Date
    Mar 2012
    Posts
    28
    OK I got it working by joining two other tables, which I tried before but I needed to move and alter the where clause.
    Attached Thumbnails Attached Thumbnails screengrab.JPG  

  9. #9
    Join Date
    Aug 2012
    Posts
    45
    Quote Originally Posted by joshuag View Post
    Sorry, Access SQL syntax is a little annoying, so the query is longer than it would be in SQL Server.
    You can alias your tables in Access just like any other database. I agree the syntax, required parenthesis especially, is annoying. I would suggest you try to use the SQL view. Another thing I would do is to use VBA to build the query as you are making selections in your combos. Then you can use DoCmd RunSQL strSQL, rather than worrying about linking form values to a query. Just a thought.

    Greg

  10. #10
    Join Date
    Mar 2012
    Posts
    28
    Thanks for that suggestion. I'll keep these mind. I've used SQL with VBA a few times in this database.

Tags for this Thread

Posting Permissions

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