We help IT Professionals succeed at work.

Access Minus with multiple fields to join on

Chipmunk77
Chipmunk77 asked
on
Medium Priority
409 Views
Last Modified: 2008-02-07
I have the following query and do not know how to restructure it for Access:
SELECT "Computer" AS sType, DynamicInventory.Manufacturer AS sVendor, DynamicInventory.Model AS sModel, DynamicInventory.[Memory (KBytes)] AS Memory, DynamicInventory.CPU, '10/23/2006' AS dDateAdded, DynamicInventory.Retired
FROM DynamicInventory
WHERE DynamicInventory.Retired Is Null
MINUS
Select sType, sVendor, sModel, Memory, CPU, dDateAdded
From BaseValue
Where Memory is not null
and CPU is not null;

I can't do a join or a not in because it takes multiple fields to join these two tables.  Vendor, Model, Memory and CPU together make it unique.  The first table is an imported csv file that is updated regularly, so I can't just add a column to give a unique key and compare on that.  

Any ideas are appreciated, thank you!
Comment
Watch Question

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Access SQL does not recognize MINUS. You'll have to incorporate that into a JOIN or WHERE Something NOT IN (SELECT Something FROM SomethingELSE)

>'10/23/2006' AS dDateAdded,
Access uses pound signs to delineate dates, so #10/23/2006#
>>I can't do a join or a not in because it takes multiple fields to join these two tables.


Access does support joins on multiple fields:


Select * from TableA a inner join TableB b on
     a.Vendor = b.Vendor
     and a.Model = b.Model
     and a.Memory = b.Memory
     and a.CPU = b.CPU
Something similar to this: (You will need to verify which fields should be connecting):

SELECT
     "Computer" AS sType,
     d.Manufacturer AS sVendor,
     d.Model AS sModel,
     d.[Memory (KBytes)] AS Memory,
     d.CPU, '10/23/2006' AS dDateAdded,
     d.Retired
FROM
     DynamicInventory as d
          inner join BaseValue as b
               on a.Vendor = b.Vendor
               and a.Model = b.Model
               and a.Memory = b.Memory
               and a.CPU = b.CPU
WHERE
     d.Retired Is Null
     and b.Memory is null

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
Try using the EXISTS keyword something like as follows (I didn't test this, so excuse any errors/typos)...

SELECT "Computer" AS sType, DynamicInventory.Manufacturer AS sVendor, DynamicInventory.Model AS sModel, DynamicInventory.[Memory (KBytes)] AS Memory, DynamicInventory.CPU, #10/23/2006# AS dDateAdded, DynamicInventory.Retired
FROM DynamicInventory
WHERE DynamicInventory.Retired Is Null AND NOT EXISTS
(SELECT Memory FROM BaseValue WHERE BaseValue.Memory is not null
and BaseValue.CPU is not null AND BaseValue.Vendor=DynamicInventory.Vendor AND BaseValue.Model=DynamicInventory.Model AND BaseValue.Memory=DynamicInventory.[Memory (KBytes)] AND BaseValue.CPU=DynamicInventory.CPU);

It's not really important the field I picked (I picked Memory because it is forced to be non-null).  It's just important whether any records are found or not.  I probably could have used "*" as well...

Commented:
As an aside, if you really want to simulate a minus query, you probably have to SELECT DISTINCT...  I *think* MINUS queries are implicitly distinct in most SQL.
MINUS subtracts the results of one query from another in Oracle. Other databases use EXCEPT but SQL Server and Access don't use either. The SQL from boxcar7 should provide what you need, although I don't believe you need to use the DISTINCT keyword.

Author

Commented:
Thanks all, here is the query I ended up using - finally figured it out!  

SELECT [My_temp].sType, [My_temp].sVendor, [My_temp].sModel, [My_temp].Memory, [My_temp].CPU, [My_temp].dDateAdded
FROM [SELECT BaseValue.nID, "Computer" AS sType, DynamicInventory.Manufacturer AS sVendor, DynamicInventory.Model AS sModel,  
                      DynamicInventory.[Memory (KBytes)] AS Memory, DynamicInventory.CPU, '10/23/2006' AS dDateAdded, DynamicInventory.Retired
                      FROM DynamicInventory
                      LEFT JOIN BaseValue ON (sVendor = Manufacturer AND sModel = Model
                      AND BaseValue.Memory = DynamicInventory.[Memory (KBytes)]
                      AND BaseValue.CPU = DynamicInventory.CPU)
                      WHERE DynamicInventory.Retired Is Null]. AS My_temp
WHERE [My_temp].nID is null;

Thanks for the suggestions - most helpful was MNelson  
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.