Query Noob

[skaas]

Greetings all, I am still a noob at Access and have now started learning to use Queries.

I am still reading a lot and trying out some things.

I could use some tips on solving a query, being the greenie I am .....

I have put together a simple Inventory database which all works fine so far.

But I would like to create a query to show any items that have been disposed of

Overview

I have 4 main tables which all store various information for the inventory

The first table shows the status of each items, this status table only contains two fields, the table contains as shown:

ID.....Status
1...... In Use
2...... In Storage
3...... In Service
4...... Disposed


3 of them hold the item descriptions, serial number etc
The Tables are named:

Workstations
Monitors
Printers

These three tables contains as follows: (Example)

Item ID.. Item Description.. Serial Number.. Location.... Status

001........ Jack's PC.............. 0000001............Reception.... In Use
002........ Tony's PC............. 000002..............Sales.......... Disposed
003........ Jon's PC............... 000003..............IT Desk....... In Service


These three tables have relationship links to the status field on the Status Table.

I have set up a dropdown combo box on these three tables to allow me to select the status directly from the status table

So far this is all great and works fine, the data is consitant and I can quickly change the status of the items contained in the inventory.

The Question

What I would like is to learn how to create a query that will allow me to show the serial number and location of the Disposed items only.

Taking data from the 3 Inventory tables and using their designated status taken from the Status Table.

Using the example details above the query would return only Tony's Pc.

I would like it to stretch over the three tables.

For example if Tony's Pc, Printer and Monitor were all disposed of, in this case the query would show his PC, Printer and Monitor.

In Conclusion

I would basically like to show all of the items that have thier status placed at disposed to show on the one query.

Any help, advice, links to tutorials ..... anything at all, so that I may learn how to make some more complex Quiery's using Access

Thanks to anyone in advance who takes the time to Read or Reply to this

Kind Regards

 

[DCrake]

You seemed to have missed a crutial lessen in creating tables and normalisation.

You say you have three tables, one for printers, one for monitors and one for workstations.

All of which hold information about each item, albeit slightly different. What you need is one table with an extra field called Category. In this field you would store Printer,Monitor, Workstation, etc.

Try and generalise your addtional field names, such as Model, serial number, Make, Shelf Life, Purchase Date, etc.

 

[skaas]

Eventually I was hoping to expand the information on the tables.

For example the workstation table would also hold information on the Workstations hardware. The mobo, cpu, ram type, whether it has a CD-Rom or Writer, etc.

Printers table would hold info for compatible toners, etc.

This way I could also see what machine has what hardware/software and which printer takes what inks...... these kinds of things...

Originally I was just playing around but eventually the database started taking shape and some potential for use is evident.

I am not all that great with Access to be honest but trying to learn.

The problem with placing all the information on one table with just a category. Though a good idea, I don't see how it would allow me to place the additional information that I am also trying to analyse.

Unless there is another way to get around this?