Solved

Access 2007 SQL query question

Posted on 2011-09-20
11
259 Views
Last Modified: 2012-05-12
Hi

i had to import a text file into a new Access table that contained 2,686 records, I then had to import an Excel file with 1,496 rows and four columns into another table. Both tables have 1 field with matching values that I am querying on.

The goal being to run a couple queries to return the records where the values match and where they do not , the first query runs fine and the results = 377

The query is below

SELECT DISTINCT s7, hlq
FROM hlq, RINCODE
WHERE s7=hlq
ORDER BY s7;


The 2nd query however returns way too many records, over 2 million.

The query is below

SELECT distinct s7,hlq
FROM hlq, RINCODE
WHERE s7 <> hlq
ORDER BY s7;

Thanks for any help, I am not sure what the heck I am doing wrong to get so many records returned.

0
Comment
Question by:rinkydink
  • 5
  • 4
  • 2
11 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 36569111
Not sure which col goes with which table, but one of these should work.  Then you can do the reverse to find missing rows in the other table


SELECT * from hlg
where s7 not in
  (select hlg from RINCODE)

or

SELECT * from hlg
where hlg not in
  (select s7 from RINCODE)


0
 
LVL 26

Expert Comment

by:Nick67
ID: 36569176
<The 2nd query however returns way too many records, over 2 million.>
Did the first query have or create a relationship between  hlq and RINCODE?
Did the second one NOT have such a relationship.
Unrelated tables make cartesian joins (all records join to all records)
2686 * 1496 =  4022744
Throw out identicals with Distinct and 2M+ could be in the cards!
0
 
LVL 1

Author Comment

by:rinkydink
ID: 36569793
The first query result is fine, I think should have imported only what I needed into 1 table as it turns out I only needed the 2 fields that have some matching data between them with plenty of duplicate values along for the ride.

But anyway, the 2 queries did not create a join and I had no relationships pre defined as it was what I thought would be a quick little simple operation....learned my lesson I guess   :)

I took all of the duplicate hlq values out of the RINCODE table which holds the hlq field and put the cleaned up data into a new table called RincodeNoDupes, it has only 1085 records now but the below query returns me no results.

The HLQ table contains the s7 field

SELECT DISTINCT s7
FROM hlq
WHERE s7 not in
  (select * RincodeNoDupes)
ORDER BY s7

0
 
LVL 26

Expert Comment

by:Nick67
ID: 36569810
Can you post a sample?
0
 
LVL 42

Accepted Solution

by:
dqmq earned 300 total points
ID: 36570020
Which tells me:

1. RincodeNoDupes has a single column
2. All of the values in hlq.s7 are also in RincodeNoDupes


To check the other way:

SELECT * from RincodeNoDupes
WHERE hlq not in (select s7 from hlq)
ORDER BY hlq
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 1

Author Comment

by:rinkydink
ID: 36570032
No prob
temp.accdb
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36570043
Accdb :(
I'm on Access 2003 where I am at right now
0
 
LVL 1

Author Comment

by:rinkydink
ID: 36570277
SELECT DISTINCT s7
FROM hlq
WHERE s7 not in
  (select * RincodeNoDupes)
ORDER BY s7

seems to have did the trick!

I should have been able to figure that out for myself but apparently I am just dumb

Here is a .mdb as well in case there is an easier way to do this, I'll take any suggestions so I don't waste hours again in the future on some 15 minute job     LOL
temp.mdb
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 200 total points
ID: 36570560
Your first query sets a Where of WHERE s7=hlq
I think it may be faster to simply make the implicit inner join of s7 <--->hlq
Your second query is tortured and slow
A left join of rincode.hlq ---> is a much faster executing query.

Very likely with the joins and distinct you wouldn't have need to clean rincode up
Sample returned

You do know that you can make ad-hoc joins in the Query editor by dragging one field in the table to another field in another table, right?
temp.mdb
0
 
LVL 1

Author Comment

by:rinkydink
ID: 36571559
Please don't assume that I have any idea beyond the basics in what I am doing   LOL

I tend to just try to bang out sql syntax while avoiding relationships/joins as much as possible


I'll look at your sample in the morning at work, I just had some beers so now my ineptness is multiplied   :)

0
 
LVL 26

Expert Comment

by:Nick67
ID: 36571604
All sorts of folks come from all sorts of backgrounds. Sometimes the reponse is 'Duh, of course.' And other times it's 'WTF, you can DO that?'. So I ask :)
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

943 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

Need Help in Real-Time?

Connect with top rated Experts

4 Experts available now in Live!

Get 1:1 Help Now