Access 2000 Query

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 
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.
 
Joined
Jun 17, 2002
Messages
2,556
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])
 

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
 
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).
 

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
 
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
 
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Top