Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2008
    Posts
    13

    Exclamation Unanswered: Searching item by year

    I have a Automobile database
    I already have a table call inventory category with car maker
    and another subcategory table with model and maker
    I make a query between them

    I want to search by year maker model
    how do i do it? I know this is a noob question
    please help
    Last edited by kidrobot2k4; 01-23-08 at 19:35. Reason: Re-wrote with correct font and spacing

  2. #2
    Join Date
    Feb 2004
    Posts
    137
    Firstly, stop using extended characters and special fonts in your posts; they will not correctly register in a text search, and not all browsers deal well with them.

    Secondly, it might help if you could list the table/field structures, so that we may more accurately answer your question.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You use something called a WHERE clause on your SQL statment
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2008
    Posts
    13
    Form Like This
    I want to have a search form like this
    when I select or enter Year, and It will show Automobile maker that have car came out at that year, and select maker will come out the model on the year
    Last edited by kidrobot2k4; 01-22-08 at 19:52.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Simply design a form to look like the one you've specified. That form has a listbox (or 2 or more listboxes) which shows the items. The query for the listbox(s) rowsource has criteria in it (ex: Select Year, Make, Model where Year = " & Forms!SearchForm!FindYear & "")
    You can easily save this as a query. You could then have many different queries saved which you would populate as the rowsource of the listbox with the query name (ex: me.mylistbox.rowsource = "SomeQueryName") depending on what field is entered for a search value. There are many examples of this. Download some of the Search examples from the code bank to see how this is done.
    Last edited by pkstormy; 01-22-08 at 20:01.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Jan 2008
    Posts
    13
    Thank you!! Paul
    I will try on that

  7. #7
    Join Date
    Jan 2008
    Posts
    13
    Hi I create the to list box like this
    How do i open a query and show at the 2nd list box when I click each year ?
    I have create a table like this and query

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I can't work with such a design.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Jan 2008
    Posts
    13
    I just want to search the car by year maker and model,
    the problem I have is like Lexus is start from 1994-2008 dodge is 1988-2008
    how should I design table, and form to make it work.

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You can either make 1 table which has each car, all the car details (make, model, etc.) including the year.
    ex of fields (MakeModelYearTable):
    CarID, make, model, year

    Or you can make 1 table which has each car (make, model, and other fields uniquely), and another table which is joined to this table and has each year listed relationally (joined by CarID in the below example).
    ex of fields of 1st table (MakeModelTable):
    CarID, make, model

    2nd table (YearTable)
    CarID, year

    The first suggestion will most likely have duplicates (depending on what other fields you're tracking) but it depends on how you want to set it up and what field values might remain consistent and if duplication (non-normalization) is a factor. The 2nd way is more normalized and most likely the route to go but again depends on how you want to set it up.

    Once you have the design of your table the way you want, you can then work on the form. The 2nd listbox on the form will have a query with criteria where the year equals what year is selected in the first listbox.

    If you go with the 1 table design, the query for the rowsource of the listbox will be something like "Select make, model from MakeModelYearTable where Year = " & Forms!MyForm!YearListBox & ""
    If you go with the 2nd table design, the query for the rowsource of the listbox will be something like "Select MakeModelTable.Make, MakeModelTable.Model From MakeModelTable INNER JOIN MakeModelTable ON YEARTable...."

    Hope that helps.
    Last edited by pkstormy; 01-24-08 at 00:34.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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