Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 20th, 2007, 04:46 PM
Authorized User
 
Join Date: Nov 2006
Location: Evergreen, CO, USA.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default Matching rows in two tables

I'm maintaining a table that contains all the people in our part of the company, about 700 rows. Human Resources will provide a spreadsheet weekly that contains the latest data. I have a work table that I clear, then import all the data from the spreadsheet.

The next process is to identify any new people. I want to find rows in the work table whose employee numbers do not exist in the main table. I'm looking for suggestions on how to implement this process.

  #2 (permalink)  
Old April 20th, 2007, 08:50 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Don,
Is your main table indexed, by employee number?

Kevin

dartcoach
  #3 (permalink)  
Old April 25th, 2007, 11:39 AM
Authorized User
 
Join Date: Nov 2006
Location: Evergreen, CO, USA.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes. The primary key is email address, but employee number is unique and indexed.

  #4 (permalink)  
Old April 25th, 2007, 11:46 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Don,

In the query wizard, there is one for "Find Unmatched Query Wizard".
This will build a query where based on a field in table #1 compared to a field in table #2 doesn't exist.

HTH,

Kevin

dartcoach
  #5 (permalink)  
Old April 25th, 2007, 11:59 AM
Authorized User
 
Join Date: Nov 2006
Location: Evergreen, CO, USA.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This doesn't appear in my Query Type dropdown list. I find Select, Crosstab, Make Table, Update, Append, and Delete.

I'm running Access 2003 in XP Pro.

  #6 (permalink)  
Old April 25th, 2007, 12:03 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Don,
When you create a new query, the first box that pops up is where that is. Not inside the query wizard. Sorry about that.

Kevin

dartcoach
  #7 (permalink)  
Old April 25th, 2007, 12:17 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Don,
Can you send me the names of your 2 tables, and the fields that you are trying to check against? I'll create the query sql for you and post it.

Kevin

dartcoach
  #8 (permalink)  
Old April 25th, 2007, 12:19 PM
Authorized User
 
Join Date: Nov 2006
Location: Evergreen, CO, USA.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by dartcoach
 Don,
When you create a new query, the first box that pops up is where that is. Not inside the query wizard. Sorry about that.

Kevin

dartcoach
I'm trying to get to the right place. I start the query wizard, select the People Load table, select the fields, select the People table, select the corresponding fields, click Next. I click on the box to modify the table, and now I see the design view. At this time, the Query Type icon becomes active: it was dimmed previously. I click the dropdown, and get the query types that I listed previously.

  #9 (permalink)  
Old April 25th, 2007, 12:40 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Don,
When you click new query, in the main screen, the first box that pops up has whether you want design view, or a wizard. One of the wizards is the one I mentioned above. It's before you ever get to design view.

Kevin

dartcoach
  #10 (permalink)  
Old April 25th, 2007, 01:50 PM
Authorized User
 
Join Date: Nov 2006
Location: Evergreen, CO, USA.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey, more than I expected.

Table: PeopleLoad fields: EmployeeNbr (Key), First, Last
Table: People fields: EmployeeNbr (Key)

I want to select rows in PeopleLoad that don't match on EmployeeNbr

Thanks for your patience
Don





Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding matching rows on two worksheets Wire323 Excel VBA 0 April 13th, 2006 12:11 AM
Rows to columns by joining to tables subhasis SQL Server 2000 0 February 7th, 2005 09:26 AM
tables not displaying all the rows values... abhit_kumar JSP Basics 2 January 17th, 2005 12:03 AM
tables not displaying all the rows values... abhit_kumar Servlets 0 January 12th, 2005 10:15 AM
Limit rows returned and next rows minhpx General .NET 1 August 12th, 2004 06:25 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.