Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access 2007 SQL query question

Posted on 2011-09-20
11
Medium Priority
?
305 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 26

Expert Comment

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

Accepted Solution

by:
dqmq earned 1200 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 800 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

916 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