Dividing table

[Rod Smothers]

I have a tech inventory database. It has one table with about 50 fields on
it. The first field is "item". ITEMS include computers, software, servers,
etc. Can a query include a field like "item" multiple times? I'm having
difficulty creating such a query and I'm wondering if I split the table, will
I then be able to create a query that calls Items from a "HARDWARE" table and
IN THE SAME QUERY call "items" from a "SOFTWARE" table

 

[KARL DEWEY]

I would recommend just adding a flag field to indicate type of item --
H- Hardware
S- Software
F- Furniture
R- Real Estate

 

[Rod Smothers]

And the flag will enable me to create a query with "ITEM" showing on it
multiple times?

 

[Tim Ferguson]

I have a tech inventory database. It has one table with about 50
fields on it.

The first field is "item". ITEMS include computers,
software, servers, etc. Can a query include a field like "item"
multiple times?

A query certainly can include the same column as many times as you like;
but I am not sure that is really what you mean. In other words, I am not
really sure what you are asking here.

Reading on, you might be wanting something along the lines of

WHERE Item = "Hardware" OR Item = "Software" OR Item = "Vapourware"


which can be shortened to

WHERE Item IN ("Hardware", "Software", "Vapourware")

In the query grid you can do the first one by putting the criteria on
different lines above one another in the Item column vis:

| Hardware !
| Software |
| Vapourware |

You can do the second one by entering the whole criterion in one go on
one line vis:

| IN ("etc", "etc", "etc") |

However...

I'm having difficulty creating such a query and I'm
wondering if I split the table, will I then be able to create a query
that calls Items from a "HARDWARE" table and IN THE SAME QUERY call
"items" from a "SOFTWARE" table

I suspect that this table does indeed need splitting, but I am thinking
vertically (i.e. normalising it) not horizontal. There is very rarely any
reason to split a table by records, and usually a lot to be gained by
maintaining a "tall, narrow" table.

Hope that helps



Tim F

 

[KARL DEWEY]

Tim I agree with you on splitting vertically. Something like this --
MDL_ID (PK)
Model-Version
Name
Manufacture

MDL_ID (FK)
SerialNum
DatePur
PurPrice
LOC_Bldg
LOC_RM
Custodian

Rod if you would post your table structure we might be able to give you some
more suggestions.

 

[Rod Smothers]

I've begun to believe that in my case a spreadsheet might really be all I
need. The only field CONTENTS that reappear are BUILDING and
MANUFACTURER....and I don't store any detail on either one of these I'll
post my fields here but I fear that this expose' could make me the posterboy
for how NOT to construct a database.

To reiterate: I have one table with 40? fields as follows - Item building
room staff manufacturer vendor serial# asset# tag3 use status discarddate
phase KETS unmetneed fund USF purchaseby fy podate po# cosst waranty
usfdiscount dateentered KETS 486/p CPU speed RAM HD type-laser Color_Y/n
model licenses licensetype licbegin licend OS title subtitle version comments

These fields fall into a few large categories: Location, purchase details,
tracking details, hardware details, software details.

Item is the most important field and it lists every tech item we've ever
paid for....hardware, software, training, labor, etc.

Here's a very specific question about this whole setup: The item "software"
is causing me grief in my reports. Would I have been better off to make the
item more specific? That is, use "MS OFFICE XP PRO" in the item field rather
than calling it "software" in the item field and then being more specific in
the other fields dealing with software?

 

[Chris2]

I have a tech inventory database. It has one table with about 50 fields on
it. The first field is "item". ITEMS include computers, software, servers,
etc. Can a query include a field like "item" multiple times? I'm having
difficulty creating such a query and I'm wondering if I split the table, will
I then be able to create a query that calls Items from a "HARDWARE" table and
IN THE SAME QUERY call "items" from a "SOFTWARE" table

Rod Smothers,

If, by "using item" multiple times, you mean, making that column
appear multiple times, then yes.

SELECT I1.item as ItemFirstCol
,I1.item as ItemSecondCol
,I1.item as ItemThirdCol
FROM Inventory A I1


As for splitting the table up into multiple tables, one for
Hardware, one for software, I would go with Tim Ferguson and Karl
Dewey, and create normalized tables.


Sincerely,

Chris O.

 

[KARL DEWEY]

How often will a table use the field "486/p CPU speed RAM HD type-laser
Color_Y/n
model licenses licensetype licbegin licend OS title subtitle version"
Seems that much of this information could be stored in a remarks field or as
part of the description.

 

[Tim Ferguson]

Here's a very specific question about this whole setup: The item
"software" is causing me grief in my reports. Would I have been
better off to make the item more specific? That is, use "MS OFFICE XP
PRO" in the item field rather than calling it "software" in the item
field and then being more specific in the other fields dealing with
software?

I don't think it's possible to say, with the information given. The
column headings you listed are meaningless to a stranger; but I strongly
suspect that there are several tables in there trying to get out. I
really cannot tell what you are trying to model -- the sound advice at
this stage is to switch off the computer, step back, get out a big piece
of paper and to start drawing lists.

For example entities:

locations, buildings, manufacturers, printertypes,
computertypes, purchasers, etc etc

Then relationships:

each computer lives in one location
each location lives in one building
each printer is an example of one printertype
each printer lives in one location
each printertype comes from one manufacturer
etc etc

Then domains:

a printertype.class in (Laser, inkjet, dotmatrix, golfball)
a printertype.colour is True/False
a purchaseorder.datesigned is prior to today
a purchaseorder.dateexpires is later than .datesigned
and so on

Once you have got to the bottom of the page, you go back to the top and
do it all again, taking out all the errors you noticed on the way down.
Repeat for a very long time. Give it to someone else to look at and then
repeat for a very long time again, addressing the errors that someone
else noticed. When you have used up 75% of your project time, you are
nearly ready to switch the computer back on again.


Hope that helps

Tim F