Compare data in two tables, with multiple conditions…..

[green19]

Been trying to crack this one for a while hoping someone on here might be able to help me.

I have a table with a list of required software and a table with a list of computers and the software installed software. I made a query that displays the machines with the required installed. My problem is if a machine has more than required then it does not display the record. If it needs Office, Photoshop but has office, Photoshop and quark installed then it does not display that machine.

This should be quite a simple thing, I have played with Like, NOT, Where and others but with no luck...

Any help would be greatly appreciated.

Graeme

 

[mresann]

Sounds like your table structure is incorrect. You are probably trying to use a One to Many relationship when you obviously have a Many to Many relationship between software programs and computers.

You need to use a third table, call it ComputerSoftware join. In this table you will include the primary keys of both the Computer and Software tables, creating a co-Primary key assignment with those fields.

Although other fields may be involved, the basic structure is:

Table: Computer

ComputerID (Primary Key)
Computer Name
...plus other computer fields

Table 2: Software

SoftwareID (Primary Key)
Software Name
...plus other software fields

Table 3: ComputerSoftwareJoin

ComputerID (Co-primary key)
SoftwareID (Co-primary key)
...plus any other fields related to the computer/software assignment

Create a one to many relationship between the Computer and ComputerSoftwareJoin tables on ComputerID. Do the same for the Software and ComputerSoftwareJoin tables on SoftwareID.

HTH

 

[green19]

I think probably my relationships are wrong this is the current structure is wrong there is third table just to make life more interesting called time slots. Here is the current layout

Subject Table

Subject Name
SubjectCode
RunningTime
Runningday
Alocated
Room#
Office
Illustrator
Photoshop
Maya
Dreamweaver
Etc…

Time Slots Table
Room#
Day
Time

Room Table
Room#
Office
Illustrator
Photoshop
Maya
Dreamweaver
etc…

The SubjectTable, room# has a one to many relationship with TimeslotTable,room#
and RoomTable, room# has a relationship with TimeSlotsTable,room#

Hope this is clear….