eed Access help to list numbers from a report

[AK Erin's Rose]

I have 2 tables in Access with the same fields (columns), but not the same
number of records (rows). Each table came from a monthly report (one for
Sept and one for Oct). I need to make a list of 4300 case numbers that
appear on one report but not the other. The total number of records for one
report is approximately 120,000 and the other is approximately 124,300.

Do I need to create a query? I have tried to do this, but since the fields
(column names) are the same on both tables, it will not let me take the
fields from both reports. Any help would be appreciated.

The number of records really makes it difficult to bring this info into
Excel and I'd rather stay with Access, if possible. I am an entry-level
user, so please give step-by-step instructions. Thanks so much!

 

[Gina Whipp]

AK,

I want to say use a Union Query but I'm not sure how you getting 4300 Case
Numbers from 120,000 to 124,000 records and if those case numbers appear in
both tables. And do you need the Case Number from each month which would
make 8,600 records reutrned? Are ther duplicate rows? Just ot sure how to
scrunch 120,000 to 4,300... I can tell you HOW to write Union Query but
perhaps there is better way if you expalin a bit more.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[AK Erin''s Rose]

I need 4300 case numbers. They are listed on one month's report, but not the
other month's report. Many (probably most) of the case numbers do appear on
both reports, but one has these extra case numbers. As for duplicate rows, I
am not sure what that means. Each case number is listed once on its own row
- does that make it any clearer?

Let me know if there is any other info that would help.

 

[Gina Whipp]

AK,

Okay, it could be me but I am missing something...

October has 4,300 case numbers but 120,000 records? Is a record on more
then row? In Access a record is a row so perhaps it's the terminolgy that
is confusing me? It might help to tell me the column layout or is that the
case numbers?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[John Spencer]

If you want to identify case numbers in table 1 that are not in table 2,
this simplest method would be to use the query wizard - the Find
Unmatched query wizard to be exact.

If you want to do it yourself then
-- Add both tables to a query
-- Join the tables on the Case Number fields (drag from one to the other)
-- Double-click on the join line and select option 2 or 3 in the dialog.
You want the option that shows All in the table with the extra case
numbers and matching in the other table
-- Add Case Number field from both tables
-- Under the case Number field for the table with FEWER case numbers
enter the criteria
IS NULL

Run the query to show you the case numbers that exist in one table and
not the other.

You may need to repeat this in the other direction, since I would guess
that there is no guarantee that all the case numbers in the table with
fewer records all exist in the table with more case numbers.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================