Results 1 to 12 of 12

Help Me Fix Location Query

  1. #1
    aamer is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Location
    Pakistan
    Posts
    216

    Help Me Fix Location Query

    When I Select a CITY the following Query shows the price list of that city. - This is working Fine

    In the table and in field PlantLoc is defined. there are 3 PlantLoc
    1. Gujranwala
    2. Islamabad
    3. Rahim Yar Khan

    Now I want To Add Another 4th Plantloc "Lahore" but this should show along with the all 3 Plantloc

    Example if I select Gujranwala it should show price list of:


    Gujranwala + Lahore
    Islamabad + Lahore
    Rahim Yar Khan + Lahore

    What modification shoud I do in the following Query



    SELECT Cities.Cities FROM Cities WHERE (((Cities.Cities) In ("Gujranwala","Rahim Yar Khan","Islamabad"))) ORDER BY Cities.Cities;




    SELECT [Price List Querry].Description, [Price List Querry].CCIDescripCode, [Price List Querry].[Off Price], [Price List Querry].Units, [Price List Querry].[GST Percent], [Price List Querry].[Serial Id], [Price List Querry].PlantLoc, [Price List Querry].[From Date], [Price List Querry].Catagory FROM [Price List Querry] WHERE ((([Price List Querry].PlantLoc)=Forms.[Admin Purchase].[Purchase Subform].FORM.Ploc) And (([Price List Querry].[From Date])>#6/9/2018#)) ORDER BY [Price List Querry].Description;

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,743
    if the cities are in the table, then join the t.PlantLoc table to the main data table to get the list. There's no need for the IN clause.

    If you don't want ALL cities, make another tPicked table, for user to select items from the tPlantLoc table (via append query). Then join tPicked to the data table.

  3. #3
    aamer is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Location
    Pakistan
    Posts
    216
    1. There are more than three cities in the Cities Table by using IN clause I have restricted it to 3 cities
    2. Price List Table has the price list
    3 On the form Admin Purchase when I select the plant location it should show me
    Gujranwala + Lahore
    Islamabad + Lahore
    Rahim Yar Khan + Lahore

    Note Lahore is in the table of
    Price List Not In Cities Table
    consider Lahore items to be constant for Gujranwala, Islamabad & Rahim Yar Khan

  4. #4
    aamer is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Location
    Pakistan
    Posts
    216
    Does any one know how to do this?

  5. #5
    mike60smart is online now Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    188
    Hi AAmer

    Can you upload a zipped copy of your database?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,371
    Show the SQL of Price List Querry. What field in PriceList table has PlantLoc?

    Maybe:

    WHERE ([Price List Querry].PlantLoc=Forms.[Admin Purchase].[Purchase Subform].FORM.Ploc OR [Price List Querry].PlantLoc="Lahore") And [Price List Querry].[From Date]>#6/9/2018#
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,340
    aamer,

    How about trying again to describe what you are trying to do?
    You have a PlantLoc table with several cities including
    1. Gujranwala
    2. Islamabad
    3. Rahim Yar Khan
    and you also have a cities table with several cities.

    We are not familiar with your set up nor your country/cities. How does Lahore fit into your set up? It is not clear from posts so far.
    As others have suggested post a copy of your tables and relationships or a copy of the database with instructions to get to the problem.

  8. #8
    aamer is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Location
    Pakistan
    Posts
    216
    First of all my apology for late reply.
    Thank You June7 You have pointed me in the right direction.
    am able to fix the issue i was having.

    SELECT [Price List Querry].Description, [Price List Querry].CCIDescripCode, [Price List Querry].[Off Price], [Price List Querry].Units, [Price List Querry].[GST Percent], [Price List Querry].[Serial Id], [Price List Querry].PlantLoc, [Price List Querry].[From Date], [Price List Querry].Catagory
    FROM [Price List Querry]
    WHERE ((([Price List Querry].PlantLoc)=[Forms].[Admin Purchase].[Purchase Subform].[FORM].[Ploc] Or ([Price List Querry].PlantLoc)="Lahore") AND (([Price List Querry].[From Date])>#6/9/2018#))
    ORDER BY [Price List Querry].Description;



    AND Fixed The Other Drop Down Box also

    SELECT [Price List Querry].CCIDescripCode, [Price List Querry].Description, [Price List Querry].[Off Price], [Price List Querry].Units, [Price List Querry].[GST Percent], [Price List Querry].[Serial Id], [Price List Querry].Catagory
    FROM [Price List Querry]
    WHERE ((([Price List Querry].PlantLoc)=[Forms].[Admin Purchase].[Purchase Subform].[FORM].[Ploc] Or ([Price List Querry].PlantLoc)="Lahore") AND (([Price List Querry].[From Date])>#6/9/2018#))
    GROUP BY [Price List Querry].CCIDescripCode, [Price List Querry].Description, [Price List Querry].[Off Price], [Price List Querry].Units, [Price List Querry].[GST Percent], [Price List Querry].[Serial Id], [Price List Querry].Catagory
    HAVING ((([Price List Querry].Catagory)="Non-Hazardous"))
    ORDER BY [Price List Querry].CCIDescripCode;











    Quote Originally Posted by June7 View Post
    Show the SQL of Price List Querry. What field in PriceList table has PlantLoc?

    Maybe:

    WHERE ([Price List Querry].PlantLoc=Forms.[Admin Purchase].[Purchase Subform].FORM.Ploc OR [Price List Querry].PlantLoc="Lahore") And [Price List Querry].[From Date]>#6/9/2018#

  9. #9
    aamer is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Location
    Pakistan
    Posts
    216
    As stated above the issue is resolved. unfortunately it did resolve the problem but created another.
    To start the PURCHASE entry data a location is selected, after which it shows the price list in "Description & Rate" If the location is Null it should not show the items in price list and the items in price list whose location is LAHORE.

    Where as now if the location is null it shows the items whose location is lahore.

    As the Database file is about 5mb and cannot be uploaded as attachment please download it from the link

    https://drive.google.com/file/d/1PI-...ew?usp=sharing
    Last edited by aamer; 12-03-2019 at 04:23 PM.

  10. #10
    aamer is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Location
    Pakistan
    Posts
    216
    Quote Originally Posted by aamer View Post
    As stated above the issue is resolved. unfortunately it did resolve the problem but created another.
    To start the PURCHASE entry data a location is selected, after which it shows the price list in "Description & Rate" If the location is Null it should not show the items in price list and the items in price list whose location is LAHORE.

    Where as now if the location is null it shows the items whose location is lahore.


    As the Database file is about 5mb and cannot be uploaded as attachment please download it from the link

    https://drive.google.com/file/d/1PI-...ew?usp=sharing

    assistance reqested

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,371
    A 2MB zip file is allowed. Run Compact & Repair then use Windows Compression.

    I would not use dynamic parameterized query. I would use VBA to build filter criteria and apply to form or report or combobox.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,477
    There is an image on the start up form that made the zipped file size 4,738 kb.

    I removed the image and the zipped file is now 976 kb.


    Note:
    There are a lot of naming issues.
    Several reserved words are used as field names - "Date", "User", "View",...
    The code modules do not have "Option Explicit" - consequently there are lost of undefined variables.
    There also seems to be a lot of orphaned sub routines.
    Also seems to have several 1-to-1 relationships????
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 02-25-2019, 02:09 PM
  2. Calculation based on Query Row or Record Location
    By Kaloyanides in forum Queries
    Replies: 2
    Last Post: 07-08-2015, 12:11 PM
  3. Query moves - Last location of tube
    By pault in forum Queries
    Replies: 5
    Last Post: 09-23-2013, 04:57 PM
  4. Replies: 3
    Last Post: 06-04-2013, 09:51 AM
  5. Replies: 3
    Last Post: 05-08-2013, 01:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums