Solved

Access Minus with multiple fields to join on

Posted on 2006-10-24
7
349 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!
0
Comment
Question by:Chipmunk77
7 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 17799449
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#
0
 
LVL 15

Expert Comment

by:MNelson831
ID: 17800070
>>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
0
 
LVL 15

Accepted Solution

by:
MNelson831 earned 250 total points
ID: 17800084
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

0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 4

Expert Comment

by:boxcar7
ID: 17800748
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...

0
 
LVL 4

Expert Comment

by:boxcar7
ID: 17800770
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.
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 17805054
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.
0
 

Author Comment

by:Chipmunk77
ID: 17812152
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  
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question