need help with Access SQL query for web page

I have 2 Access tables, INV and PC_EQUIP and the pertinent fields are as follows:
INV: FullName, Ext, Location, CPU, MON, PTR, DOCK  ...
PC_EQUIP : Model, Serial ...

INV hold mostly user info but also the serial numbers of the equipment they have are in the fields CPU, MON, etc and is used by IT

PC_EQUIP is used by our receiving dept and they just enter everything that comes in by the model and serial number alond with numerous other pieces of information, therefore, our equipment is one big long running list of serial numbers and the type or model of the equipment corresponding to those serials with no regard to they type of equipment like PC or monitor.

I wrote this to tie the two together so I wouldnt have to reinvent the wheel when looking up the equipment a user has:
SELECT PC_EQUIP.Type,PC_EQUIP.Model,PC_EQUIP.Serial,PC_EQUIP.FormFactor,PC_EQUIP.StartWarDate,PC_EQUIP.EndWarDate,PC_EQUIP.WarLength,PC_EQUIP.Proc,PC_EQUIP.Mem,PC_EQUIP.HD,PC_EQUIP.OS,PC_EQUIP.LaptopScn,INV.CPU
FROM INV INNER JOIN PC_EQUIP ON INV.CPU = PC_EQUIP.Serial
WHERE INV.ID = ::ID::

This works great to pull all that information for the user based on matching the serial number in PC_EQUIP with the CPU number in INV and I can do the same for monitors and printers and such by changing the JOIN from INV.CPU to INV.MON and INV.PTR etc..

What I need though is to be able to create a full inventory list of PC's, monitors an printers. So far I have this and it does just fine pulling the PC's but the JOIN is throwing me for a loop trying to figure out how to incorporate the monitors and printers in as well.

SELECT INV.FullName,INV.location, INV.office,INV.ext,PC_EQUIP.Model,PC_EQUIP.Serial,PC_EQUIP.INV.CPU
FROM INV INNER JOIN PC_EQUIP ON INV.CPU = PC_EQUIP.Serial
ORDER BY INV = FullName ASC

Thanks and I hope I am make sense.
Mark LewisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
Try including them in your JOIN using ORs:

FROM INV INNER JOIN PC_EQUIP ON INV.CPU = PC_EQUIP.Serial  OR INV.MON = PC_EQUIP.Serial OR INV.PTR = PC_EQUIP.Serial 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
The problem is that your INV table is not normalized.  It looks like a spreadsheet rather than a relational database.  What happens if a person has a desktop and a laptop?  How about if he has two monitors? What if everyone needs the next great invention?
Mark LewisAuthor Commented:
Thanks mbizup, that worked.

Pat, I realize the table isnt normalized but I wasnt the creator of our Intranet or the database. That being said, I know what it means to normalize a database but not being a DBA or a programmer, I wouldnt know how to do it anyway even if I was starting from scratch. I am just the NetAdmin for a small law firm trying to get someone else's old code to do work for me ;). That's why I appreciate the help you guys and this site provide. Although I at least try, there are times you get get tired of beating your head against the wall and just ask. You can look back at all my requests over the years and you will see that 90% is for web and DB programming. You would think I would just go formally learn it by now.

Again, thanks fellas!
PatHartmanCommented:
Trying to overcome a poor design can be a challenge.  Don't let it get you down.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.