Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jan 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Queries (Access 2000)

    Hello: I hope you experts will be paitient with a "newbie."

    I operate a small business supplying automotive parts to Dodge collectors. My parts list database includes in each record four fields that indicate which vehicles the particular part will fit. I want to do a querie that produces a table that includes all parts for a given vehicle. Each vehicle type will appear in one of the four application fields. How do structure the query so that the result includes records based on all four application fields. For example, what happens to me is that I put g741, for example, in the criteria field. If I only do this in one of the four fields it returns the proper result. If I put g741 in a second application field I get no results at all. I know this has to be simple but I can't figure it out. Thanks in advance for your help. Sid

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Access Queries (Access 2000)

    Hi,
    In your query grid, you will notice that on the left side, under Criteria, it says "or:". What you need to do is put the cirteria for field 1 (="G741") on the first citeria row, then your criteria for field 2 (also ="G471") goes on the next row and so on.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Jan 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Queries (Access 2000)

    Hello Rory: I tried inputting =G741 every way I could think of - on the criteria row, on the or row, on both rows, with and without parentheses. Same thing - sometimes it would return the proper result on the first application field and sometimes nothing would come up. Maybe something is not right on my parts list table? Any further ideas? Sid

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Queries (Access 2000)

    Rory is not online at the moment, so I'll jump in. Could you provide some more information about the structure of your table - what exactly is the role of the four fields you mentioned, for example?

  5. #5
    New Lounger
    Join Date
    Jan 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Queries (Access 2000)

    Hello Hans: Thanks for the reply.

    The data base is a list of automotive parts for antique Dodge vehicles. All of the parts will fit several different models of Dodge vehicles, such as Civilian Power Wagon, WWII Dodge trucks and the later M series Dodge vehicles of Korean war vintage. So, the database describes the parts using text and part numbers, using several fields. The four fields in question list which vehicles the particular part will fit. Each type of vehicle has its own designation, such as g741 for the M series Dodge, g502 for the WWII Dodge, PW for Power Wagon etc. Any of the four fields can have any of the designators in them, but there is no duplication of designators in any record of the four fields. However, there may be blanks in up to three of the four fields if the particular part only fits one of the vehicles. Now, the goal is to produce a report which lists parts only for one of the vehicles. In this manner I can send a parts list to any potential customer with that type of vehicle. Therefore, I need to search the database in a manner that picks out the designated vehicle from all four fields.

    I did this in the past using a Borland Paradox database and it worked just fine. I am just having one really hard time getting up to speed on Access. Maybe I have the wrong book - I am using Running Access 2000. Maybe there is a book that better describes the capabilities of Access. Thanks for your help. Sid

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Queries (Access 2000)

    The query should look like the one in the screenshot below. The captions are in Dutch, but the general idea should be clear.

    Note: the table structure you describe is not ideal. If I were to set it up, I would create three tables:
    1) A Vehicles table, with a field identifying a vehicle type (say VehicleID) plus other fields providing info about the vehicle type.
    2) A Parts table, with a field identifying the part (say PartID) plus other fields providing info about the part.
    3) An intermediary table with two fields: VehicleID (linked to the Vehicles table) and PartID (linked to the Parts table). Each record in this table is a valid vehicle-part combination.

    To find the parts that fit a particular vehicle, you'd create a query based on the intermediary table, and enter the vehicle ID in the criteria row.

    See <post:=503,349>post 503,349</post:> and the links provided there for books and useful websites about Access.

  7. #7
    New Lounger
    Join Date
    Jan 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Queries (Access 2000)

    Hello Hans: I did get my query to return as I want it using the scheme you used. However, since I would have to re-input some 4000 line items, I elected to use my existing data base.

    I have now found out, to my dismay, that the query is "live." If I make changes to records in the Query the underlying table also changes. How do I stop this? Also, If I have duplicate parts, I create my records so that there is no violation of the key fields. Then, I leave the price of all of the parts blank except one. In this way, I thought that if I put >0 in the price field criteria, the record would not come up on the query table. In most cases this is true but I still get 100 or so blanks in the price field out of 1500 or so that come up properly. This has to be another simple setting, but I have not figured it out. Sid

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access Queries (Access 2000)

    If you change data in a query, you also change the data in the underlying table. That is the nature of queries in Access. If you came from Paradox, you might have expected the query to be a vritual table with no live link to the data, but that is not the case. You can make queries read only, but in that case you can't change the data. You can use a maketable query to put the results of an updateable query into a new table, but I don't see how that would be of benefit to you.

    In Access, null values cannot be compared to anything else, so you have to use additional criteria like Is Null or Is Not Null to filter out nulls
    Charlotte

  9. #9
    New Lounger
    Join Date
    Jan 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Queries (Access 2000)

    Hello Charlotte: Thanks for the information. I did finally see in the book that the Access Queries are "live" in contrast to those in Paradox. Guess I just have to be extra careful how I handle my data. Also, I just figured out that I had to put >0 for price on all four criteria rows that I am using. I can now get the answer that I want from the query. But, I still have a world of learning to do to get this thing to do all of the things I want to do. If I just live long enough I will get it figured out. Thanks, Sid

Posting Permissions

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