472,784 Members | 1,204 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,784 software developers and data experts.

finding info in a master table

Hi all

I've not used Access 2k for a few years and can't remember how to get
information from one table that is not in several other tables.

I have 4 Tables in my DB

tbl 1 - TblMaster - 220k rows <<< note size
tbl 2 - tblA - 10k rows
tbl 3 - tblB - 8k rows
tbl 4 - tblC - 3k rows

The master table has a column with an ID number - I was told this is
unique but have found dupicate numbers. (its not an auto number)

Tables A, B, C, contain information (different from the Master table)
except for one field. the "unique" id number

I need to create a table D from table MASTER - that contains all the
records/rows that are *NOT* in tables A, B, C

I have looked at something like this:

SELECT tblmaster.* (i'll put field names here), INTO tblD
WHERE tblMaster.id NOT IN (SELECT id FROM tblA)
AND tblMaster.id NOT IN (SELECT id FROM tblB)
AND tblMaster.id NOT IN (SELECT id FROM tblC);
Is this the correct/best way to pull out all the rows from master tbl
that are not in A, B, C and store that info into tblD ???

Any other way that might be better. I did the above and it was still
working after 20 mins (had to leave work so stopped it)

Thanks for any help.


Nov 13 '05 #1
1 1817
If possible, use outer joins instead of NOT IN.

SELECT A.fld1, A.fld2, B.fld1,B.fld2

Then just do a bunch of left joins (A to B, A to C...)
This will return a group of records. Once you get this right, turn the
query into an Append Query and add the records to a new table. (and
you might want to outer join to that to eliminate all the records that
are already in the destination table).

Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: M Bourgon | last post by:
I'm trying to figure out how to find the last whitespace character in a varchar string. To complicate things, it's not just spaces that I'm looking for, but certain ascii characters (otherwise,...
by: Chris Belcher | last post by:
While I'm sure this is simple I just can't figure it out. Table A (assignments) is on the One side of a One to Many relationship With Table B (assignees)There are many assignees assigned the one...
by: feck | last post by:
I have a database with several tables, one of which is I use this to bring up a form so you can unlock the record (set by using a yes no box on the data input form to prevent unauthorised...
by: Mr. B | last post by:
VB.net 2003 c/w Framework 1.1 and MS Access db We have a commercial program that does our Acounting and Time Sheets (Timberline). At least once a day our Accounting department runs a Script...
by: rdemyan via AccessMonster.com | last post by:
I'm trying to implement a licensing scheme. There are three types of licenses: Trial - good for 30 to 60 days Interim - good for 1 year Fully Paid - no expiration Everything is working fine...
by: Extremest | last post by:
Here is the code I have so far. It connects to a db and grabs headers. It then sorts them into groups and then puts all the complete ones into another table. Problem I am having is that for some...
by: jrett | last post by:
I'm new to ASP.NET and fairly inexperienced with web development in general, but I've been a professional software dev for over 10 years, C++, Unix and windows, C# the past 4 years. I've been...
by: goldfish999 | last post by:
I am new to Visual Basic (have not used since version 1 !!) but am trying to learn a new language (have had experience with Delphi ). I have an existing database created in VB 2005 of a Master Table...
by: Rosy | last post by:
I have a form "A" that is set on table vessel names. I have subform "B" that is set on table vessel info. I want form "B" to update based on what vessel I choose in form "A". I know this is...
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.