Solved

Access 2007 SQL query question

Posted on 2011-09-20
11
250 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

705 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

16 Experts available now in Live!

Get 1:1 Help Now