Complex Access 2003 Query

[Guest]

Ok,

Sorry for the length of this message - rather give all the
details so it is clear to understand

Table Structure : ------------------------------------------

FileProduct
===========

ID AutoNumber -> Unique Key (Long Integer)
FileProduct Text -> Description of product.

FileDetails
===========

ID AutoNumber -> Unique Key (Long Integer)
ProductID Text -> Relational Link into the FileProduct Table
above.
Filename Text -> Name of the file.
Version Text -> Version Details of File.

PCDetails
=========

ID AutoNumber -> Unique Key (Long Integer)
PCName Text -> Name of PC
FileName Text -> FileName found on PC.
Version Text -> Version Details.

Table Data (what is in each table):

FileProduct
===============
ID FileProduct
-- -----------
1 P1

File Details
===============
ID ProductID Filename Version
-- --------- -------- -------
1 1 F1.DAT 1
2 1 F1.DAT 2
3 1 F3.DAT 2
4 1 F4.DAT 2

PCDetails
=========
ID PCName FileName Version
-- ------ -------- -------
1 PC1 F1.DAT 2
2 PC1 F3.DAT 2
3 PC1 F4.DAT 2
4 PC2 F1.DAT 1
5 PC2 F3.DAT 2
6 PC2 F6.DAT 3

Ok now here is the problem. What I am trying to do with Access 2003 is work
out how to make a query that will return every PCName that has has the items
in the FileProduct.

Ok here is how I would like it processed

Any file with the same name would be joined by an OR condition.

So the logic would be.

If the PC record has (F1.DAT - Version 1 OR F1.DAT - Version 2) AND (F3.DAT
- Version 2)
and (F4.DAT - Version 2) then it would be a succesfull match and return PC1.
So as you can see files with the same name are ORed together and files with
different
names are ANDed together.

In the PC Details this would match rows 1,2 and 3. However, PC2 would not be
matched
because it does not have a match for F4.DAT.

Now a Product could have mulltiple files in it and there would be multiple
products.

So how do I do this with a Access 2003 Query? I figure this is possible with
some
magic SQL - but I can't figure it out.. I am hoping I don't have to resort
to some
complex Access basic routine. My instincts say this is possible with just a
SQL statement. (BTW: All my data is held in Access 2003 if that makes any
difference).

Any help greatly appreciated !!!

Thanks,

Ward.

 

[Guest]

This looks to me like a relational division problem. I can't be confident of
the following SQL without having the tables to test it against, but the
thinking behind it is that for each FileProduct the count of the rows with
files of that product for each PC is compared with the count of the distinct
file names for that product. This does assume that only one version of any
file is installed on any one PC, however, as either the counts would not be
equal if more than one version of a file was installed, or the counts could
be speciously equal and return a PCProduct incorrectly if for instance there
were 2 versions of one file, but one other file from the same product were
missing from that PC.

SELECT DISTINCT FileProduct, PCName
FROM PCDetails AS PCD1, FileDetails AS FD1, FileProduct AS FP1
WHERE PCD1.FileName = FD1.FileName
AND FD1.ProductID = FP1.ID
AND (SELECT COUNT(*)
FROM PCDetails AS PCD2, FileDetails AS FD2, FileProduct AS FP2
WHERE PCD2.FileName = FD2.FileName
AND FD2.ProductID = FP2.ID
AND PCD2.PCName = PCD1.PCName
AND FP2.ID = FP1.ID) =
(SELECT COUNT(*)
FROM (SELECT DISTINCT ProductID, FileName
FROM FileDetails) AS FD3
WHERE ProductID = FP1.ID);

Ken Sheridan
Stafford, England