Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Access Minus with multiple fields to join on

Posted on 2006-10-24
7
350 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

837 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