1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2000 Query

Discussion in 'Business Applications' started by nick3young, Apr 20, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. nick3young

    nick3young Thread Starter

    Joined:
    Nov 18, 2003
    Messages:
    48
    Hi Everyone!
    Could anyone tell me if it is possible to create an access query to only show records where a particular field is a certain word.
    I have created a database for all the equipment in our college and am struggling to find a way to query it by room. The reason being, 'room' is not a table but instead a field within each table - e.g. 'station', 'monitor' etc.
    So I would like to say, "if room = t47, then show ALL equipment for this room".
    Any way round this possible?

    Thank you all in advance
    Nick
     
  2. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Just set the criteria for the field to t47. It will then filter out all records where the room field does not equal 47.
     
  3. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    Or take it a few steps further and make it a "Parameter" query by enclosing text in square brackets like:

    [Enter Room number]

    in the criteria which will prompt the user to input the room number.


    or if your up for a challenge....

    Create a form with a text box named for eg. txtRoomNumber and the form named: frmRoom

    place the criteria below (using your names) in the criteria of the field in the query.

    [forms]![frmRoom]![txtRoomNumber]


    now if you place a button on the form to open the report or query, the query will use the control to pass the criteria.

    Use this below instead and in the Text box properties under Default value put in "*"

    Put this in the query criteria:

    Like ([forms]![frmRoom]![txtRoomNumber])
     
  4. nick3young

    nick3young Thread Starter

    Joined:
    Nov 18, 2003
    Messages:
    48
    Thanks a lot guys,
    I feel a bit stupid not knowing about the criteria part to a query!

    There is a new part to my problem now however and I think this one is going to be even more tricky, if not impossible.
    Like I say, I have all my different equipment categories saved into different tables, e.g Station and Monitor. All the tables have common fields, e.g 'serial number' and 'model number'. What I'm really hoping to do is include all categories (tables) into one query. So all stations would be listed, then all monitors below then printers and so on....
    This probably does sound easy at first, but if I include Stations and Monitors in the same query, then a new column is created for each field e.g 'monitors.serial' and 'monitors.model' AND 'station.serial' and 'station.model'.
    Ive probably gone the wrong way about this from the start and should have just had one table for all categories and then a field called 'category', but my project is too far gone now!

    Can this be done??!

    Thanks a lot
    Nick
     
  5. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    Just out of curiosity, why do you think you've gone too far to change your current structure? You could make a copy of the existing database and adjust and play with it while keeping your original...no harm, no foul.

    Anyway, just to be clear, queries are a means to an end...you may find that you can achieve the same thing by creating each small report as a subreport and putting it into a main report - this will achieve what you are looking for. Just create a simple report for each category, then create a big report manually and drag and drop each subreport into the detail section....

    Then, if you really want to keep this database and allow it to expand over time, you will have to go back and reorganize your tables....set up properly from the beginning, you will find everything much easier to accomplish...keep it as is, and every query/form/report you want to create becomes a headache.

    MBN

    PS: Use a copy of your dbase, create one table for all the equipment (primary key is model/serialnumber), and cut and paste all the data from the other tables into this one. This time, however, include the field "category". This could have values hard-coded into the field properties or you could create a separate table for categories and make it a lookup (second one is better as it's easier to add to the list later).
     
  6. nick3young

    nick3young Thread Starter

    Joined:
    Nov 18, 2003
    Messages:
    48
    Thanks for the answer mustbnuts, but it was kind of the answer I was hoping not to hear! But I guess you're right unfortunately.

    My only last bit of hope I suppose, to carry on as I am doing now, is the research I have done into 'union queries'.
    Can these not be used to combine fields from multiple tables into a single query? Does anyone know much about these?

    Thanks a lot
     
  7. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    It would be hard to address how to structure a union query without really knowing the data involved in your tables/database.

    Having said that, the help outlines the principle pretty simply:

    The following union query selects all company names and cities from both the Suppliers and Customers tables, and sorts the data alphabetically by city.

    SELECT [CompanyName], [City]
    FROM [Suppliers]

    UNION SELECT [CompanyName], [City]
    FROM [Customers]
    ORDER BY [City];


    why not give it a try? If you're not sure how to do it, easy! open up a query and choose the tables you want involved. Now go up to the top and either click on the dropdown button for the different views (design, datasheet, etc) and choose SQL. Type in your query manually using the above format. Another way to get the the SQL view is to click View...SQL while in the design view of the query.

    Hope this helps,

    MBN
     
  8. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    I tried that union query....didn't think much of it...

    And as I thought about your predicament later, I must say that IMHO you will be better served if you correct the database now rather than waiting for it to get any bigger.

    MBN
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/222195

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice