Solved

Access Minus with multiple fields to join on

Posted on 2006-10-24
7
351 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

685 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