Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    dpn
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    13
    Rep Power
    0

    Deleting duplicates in Listbox Help for a Newbie


    Hello all, I searched all around but cant find the answer to my question.

    I am doing an assignment for school, where I make a form with a list box choosing property type and a range of currency to enter then after my command button the results are displayed in a report.

    Here is the problem:

    The table I'm drawing the data from has a row called "property type" in which there are villa, manor and castle mutiple times. I made a query but when it puts the data into the form ALL of the castles and villas show up in the listbox, where as I just want one instance of each.

    Ex:
    (how listbox looks now)
    castle
    castle
    castle
    villa
    villa
    villa
    manor
    manor
    manor

    (how I want it too look)
    castle
    villa
    manor

    I'm stuck and my teacher is pretty useless when answering my questions. Maybe because it's online but man is he vague....it's already handed in but I can make revisions for more points.

    Thanks for any help, I'm freaking out over here lol
  2. #2
  3. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    59
    In the query properties you can set the "Unique values" property to yes. That should limit it to one of each value returned in your query.
  4. #3
  5. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Location
    Lake County, IL
    Posts
    865
    Rep Power
    152
    An alternative to meratigoerr's solution is to use group by in your query to arrive at unique entries. Look at this video.

    Macromedia Flash (SWF) Movie Created by Camtasia Studio 2
    Alan Sidman
    Office 2010, 2007, 2002

    If I helped you, then click "give rep" button in the lower left corner.
  6. #4
  7. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    59
    Well gee Alan... if you want to do it the easy way.... (by the way, love the datapig videos. Have helped me so much.)
  8. #5
  9. No Profile Picture
    dpn
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    13
    Rep Power
    0
    Thank you for the quick replies.

    I tired both hose methods and they didn't work. Maybe because I forgot to mention I have mutiple fields?

    I did find a part solution... I changed the row source property on the listbox itself and the query on it has one field and the results aren't duplicate... but, although they work as intended, the list items do not show up!

    So now I can click on the spaces where the items in the list box are, Have the query work without duplicates, but the text doesn't show up... crap.
  10. #6
  11. No Profile Picture
    dpn
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    13
    Rep Power
    0
    *The text for the query selections doesn't show up in the listbox to select them, just blank spaces.

    I think that makes it more clear than my last post, sorry!
  12. #7
  13. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Location
    Lake County, IL
    Posts
    865
    Rep Power
    152
    I tired both hose methods and they didn't work. Maybe because I forgot to mention I have mutiple fields?
    What does this mean? Perhaps you should post a sample of your multiple fields. I don't understand.
  14. #8
  15. No Profile Picture
    dpn
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    13
    Rep Power
    0
    For sure it's an assignment, should I just post the whole db? list box in frmType and related qryImrprovedParameter are what's killin me lol
  16. #9
  17. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    59
    Neither of those suggestions will work with multiple fiels.. Yes, you probably should have mentioned that from the start. Simplest solution is to create a query from your query with only the one field you need for your listbox. Then either suggestion will work. (although I will admit that Alan's suggestion is the better route to go than mine.) To then get your list of items that contain the type selected in the list box, create a query that uses the list box as criteria to populate your list. In the on change event of your listbox add code to requery the list.
    Last edited by meratigoerr; November 26th, 2012 at 02:46 PM.
  18. #10
  19. No Profile Picture
    dpn
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    13
    Rep Power
    0
    Here are some pics of my dilemma, I'm trying what you guys are saying but I'm doing it wrong. As I said I'm a newbie but this is the first assignment giving me trouble so please don't laugh too hard.

  20. #11
  21. No Profile Picture
    dpn
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    13
    Rep Power
    0
    Posted some pics just waiting for them to clear mods
  22. #12
  23. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,621
    Rep Power
    650
    I am afraid to ask - do you have lookups set in tables?

    I presume the displayed query is RecordSource for report?

    We are going in circles. Upload db to a fileshare site such as box.com and post link to the file. Run Compact & Repair and zip file. If there is confidential data, make copy and remove that data.
    I like to know if my suggestion helped you. One way to let me know is by clicking star at bottom left corner of post.
    Debug!Debug!Debug!http://www.cpearson.com/excel/debug.htm
  24. #13
  25. No Profile Picture
    dpn
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    13
    Rep Power
    0
    Originally Posted by June7
    I am afraid to ask - do you have lookups set in tables?
    No, how would that be relevant? Like I said I'm learning

    Originally Posted by June7
    I presume the displayed query is RecordSource for report?
    Yes

    Originally Posted by June7
    We are going in circles. Upload db to a fileshare site such as box.com and post link to the file. Run Compact & Repair and zip file. If there is confidential data, make copy and remove that data.
    Will do.
  26. #14
  27. No Profile Picture
    dpn
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    13
    Rep Power
    0
    https://www.box.com/s/pt8drv3mgpbz5nx62b21

    Thanks for all the help everyone.
  28. #15
  29. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    59
    ok... the form [frmType] is pretty sparse. I'm assuming you want it to fill in the rates automatically from the choices you select, but you'll going to need more infor in your query to do it.
    Code:
    SELECT tblProperty.PropertyType, Max(tblProperty.NightlyRate) AS MaxofRate, Min(tblProperty.nightlyRate) AS MinofRate
    FROM tblProperty
    GROUP BY tblProperty.PropertyType;
    Using the above query for the record source of your list box will give you the single types your looking for, as well as the min and max values to populate into the range fields you have on the form. You can display just the property type and hide the min and max values. You would need to either add code the on change event of the list box, or reference the list box directly from your min and max fields.
Page 1 of 2 12 Last
  • Jump to page:

Similar Threads

  1. Deleting Duplicates in a Query in Access
    By chrisjack001 in forum Microsoft Access Help
    Replies: 5
    Last Post: January 7th, 2011, 07:47 AM
  2. Reorder Items a Listbox - Help for Newbie
    By ljuliusson in forum HTML, JavaScript And CSS Help
    Replies: 1
    Last Post: April 25th, 2007, 06:05 AM
  3. selecting multiple values in listbox and deleting them
    By hithere in forum Visual Basic Programming
    Replies: 1
    Last Post: August 31st, 2005, 04:20 PM
  4. deleting duplicates
    By kamberman in forum Microsoft Access Help
    Replies: 1
    Last Post: November 15th, 2004, 04:32 AM
  5. Listbox Duplicates
    By eddy99 in forum Microsoft Access Help
    Replies: 2
    Last Post: July 28th, 2004, 11:15 AM

IMN logo majestic logo threadwatch logo seochat tools logo