Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access Minus with multiple fields to join on

Posted on 2006-10-24
7
Medium Priority
?
359 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 66

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 750 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…
Suggested Courses

972 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