Solved

Access 2007 SQL query question

Posted on 2011-09-20
11
279 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

820 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