Query- issue (part 2) with multiple queries

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi All,

Below are the four tables that I'm trying to query the
datas from. As suggested from the internet users, the
multiple queries should get all the datas from all four
tables. But I'm still having trouble getting the results
from Table4, which is the Application table.

Relationship between tables:
A user can have a computer, that can be a Desktop or a
Laptop, and each computer must have some application
installed.


User tables:
Fields:
StudentName
StudentAddress
DeskModel
LapModel
Desktop table:
Fields:
Model
Processor
RAM
AppID
Laptop table:
Fields:
Model
Processor
RAM
AppID
Application table:
Fields:
AppID
AppName
AppVersion

Any more suggestions for the queries or if I'm building my
relations incorrectly?

Thanks for the help.

Mike
 
Why do you have separate Desktop and Laptop tables? A computer is a
computer - add a "ComputerType" field to one of the tables (= Desktop or
Laptop) and delete the redundant table. Also, it appears that the rows in
Desktop and Laptop are for distinct machines (maybe you need a serial
number?). If that's the case, you should have StudentID (or StudentName) in
this table to link back to Students so that a student can own more than one
computer. Finally, can a computer have more than one application installed?
(Probably.) If so, you should create a separate ComputerApplications
linking table that has the ComputerID from Desktop or Laptop (whichever you
decide to keep) and AppID from the Applications table - with perhaps an
install date field. Remove AppID from the computer table.

After you fix these design problems, the query should be easy.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
Back
Top