Solved

Access 2007 SQL query question

Posted on 2011-09-20
11
296 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
[X]
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
  • 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
Independent Software Vendors: 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 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
 
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

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

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…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

729 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