Solved

Comparing two tables

Posted on 2000-02-23
14
283 Views
Last Modified: 2012-08-14
I am trying to compare two tables that contain balance sheet information.  Each record from one table should match a record in the other table. (i.e. $500 in table1 should match a $500 in table2)The problems I am running into are:
1. There is not an equal number of records
2. There are sometimes a different number of equal entries (three entries of $500 in table1 compared to two entries of $500 in table2)
3. There are entries in each table that will not match up ($24 in table1, no such entry in table2)

I can write a query that gives me the entries that don't match up, but how do I get the equal amounts that don't match up. For example, if I have the three entries of $500 in table1 and only two in table2, how do I get Access to tell me that I have one entry of $500 in table2 that is out of balance.

Someone mentioned to me bookmarks, but I have never used them and I do not see how they would apply.  Can I not just write a Query?  Any help is appreciated.
0
Comment
Question by:u8semaj
  • 5
  • 4
  • 2
  • +2
14 Comments
 

Expert Comment

by:JTD3
ID: 2551185
First U8 -- i dealt with enough finicial craziness like your talking about, so my first question each amount should be tieing to som othe piece of information, like account number or catagory correct.

If this is the case then I would use that in my match to find records that dont match.  Proovide me with a little more info on what you data source is like and then we can get a better hit.

I know from past results seeing the account number or account catagory that wsa out of balance was also very usefully.

John
0
 

Author Comment

by:u8semaj
ID: 2551233
Here is where it gets insane.  The data that comes to me has no reference/account numbers or categories.  Each table is essentially a column of numbers (pulled from ADP and MTTI if you know what those are) that need to be compared.  I have tried creating a new field that is autonumbered and making it the primary key but it does not seem to help.

This is not my area of expertise, I was just told to do it.
0
 
LVL 10

Expert Comment

by:paasky
ID: 2551320
Hello u8semaj,

I hope there's a field which creates relation between these two tables.

I made you a sample how I would find the unbalanced records and calculate the difference:

Here are two sample tables (headerID is assumed to be join field):

TABLE1:
HeaderID      Debit
1      500
2      250
3      600
4      100

TABLE2:
HeaderID      Kredit
1      250
1      250
2      260
3      400

This UNION query should find all the records from the both table that does not match. If the value is negative it's from table1 and positive when data is from table2. It also finds the records not found from other table.

SELECT A.HeaderID AS JeHeaderID,
              -1 * SUM(A.Debit) AS Amount
  FROM Table1 AS A
  GROUP BY A.HeaderID
  HAVING SUM(A.Debit) <>
     (SELECT SUM(B.Kredit)
      FROM Table2 AS B
      WHERE A.HeaderID = B.HeaderID)
UNION ALL
  SELECT B.HeaderID,
                SUM(B.Kredit)
  FROM Table2 AS B
  GROUP BY B.HeaderID
  HAVING SUM(B.Kredit) <>
     (SELECT SUM(A.Debit)
      FROM Table1 AS A
      WHERE B.HeaderID = A.HeaderID)
UNION ALL
  SELECT A.HeaderID,
               -1 * SUM(A.Debit)
  FROM Table1 AS A
  WHERE  NOT EXISTS (SELECT 1 FROM Table2 AS C
                                       WHERE A.HeaderID = C.HeaderID)
  GROUP BY A.HeaderID
UNION ALL SELECT B.HeaderID,
              SUM(B.Kredit)
FROM Table2 AS B
WHERE  NOT EXISTS (SELECT 1 FROM Table1 AS C
                                WHERE B.HeaderID = C.HeaderID)
GROUP BY B.HeaderID
ORDER BY 1;

Here's a query which is based to previous UNION ("UnBalance") query and counts the difference:

SELECT UnBalance.JeHeaderID, Sum(UnBalance.Amount) AS Difference
FROM UnBalance
GROUP BY UnBalance.JeHeaderID;

Hope you this helps.. you have a tought task to solve there.

Regards,
Paasky




0
 
LVL 10

Expert Comment

by:brewdog
ID: 2551340
Wow, that's a wild way to send you the data. I feel sorry for you. :o)

Since you can get the individual amounts that don't match, you're in decent shape. Here's what I'd do for the second piece, finding out where you have three $500s in one table vs. 2 in the other, etc.

Build a query on each table that looks like this:

SELECT [DollarAmount], Count([DollarAmount]) as Times
FROM YourTable
GROUP BY [DollarAmount]

Call these two queries QueryOne and QueryTwo. Then do this query, one for each direction:

SELECT [QueryOne].[DollarAmount], Difference: [QueryOne].[Times] - [QueryTwo].[Times]
FROM QueryOne INNER JOIN QueryTwo
ON QueryOne.DollarAmount = QueryTwo.DollarAmount
WHERE QueryOne.Times <> QueryTwo.Times

Does that make sense? QueryOne and QueryTwo will pull the number of times each dollar figure appears in each table, and then the third query will pull the DollarAmount and the number of times one appears more than the other. So if your result looked like:

$500              2
$250             -1
$100              1
$10               -7

This would mean that (based on the SQL I posted above) there are two more $500 entries in QueryOne/TableOne, one more $250 entry in QueryTwo/TableTwo, one more $100 entry in QueryOne/TableOne, and 7 more $10 entries in QueryTwo/TableTwo.

Hope this idea helps . . . let me know if you have any questions.

brewdog
0
 
LVL 10

Expert Comment

by:paasky
ID: 2551353
here are some results of my queries...

Union query results:
JeHeaderID      Amount
2      260
2      -250
3      400
3      -600
4      -100

Difference:
JeHeaderID      Difference
2      10
3      -200
4      -100

paasky
0
 
LVL 1

Expert Comment

by:jkrautner
ID: 2551358
Hi,
lets assume you have
table1 with values 10,20,30,30,40,50
and table2 with 10,20,20,30,80
You said you like to get 20 and 30 as
result of a query 'cause the amount of records differs.
Well try this:
Create three Queries:
q1: SELECT balance, Count(balance) AS Num FROM table1 GROUP BY balance;

q2: SELECT balance, Count(balance) AS Num FROM table2 GROUP BY balance;

q3:SELECT Q1.balance, Q1.Num, q2.Num
FROM Q1, q2 WHERE Q1.balance=q2.balance AND Q1.Num<>q2.num;

This will return the two wanted records with a counter how often each one is found in each table

Hope that helps
 Juergen
0
 
LVL 1

Expert Comment

by:jkrautner
ID: 2551373
Oups,

there where a lot of answers almost at the same time. Feel free to choose the one you liked most as solution, if any ...

Juergen
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 10

Expert Comment

by:brewdog
ID: 2551384
jkrautner:

I notice from your profile that you have been on EE for a while, though you seem to not be very active. I believe this is the first time I've seen you.

In general, in the Access forum, we try to post Comments, not Answers. (I'm assuming that you either mistakenly marked your submission an Answer or didn't know this etiquette.) This gives every expert a chance to take a shot at the question, without locking the question, where other experts might not look at it.

In this particular case, there are two strong reasons why you should have posted a Comment. First, several other experts had already posted ideas which could be equally valid to your submission. And second, I posted the exact same idea. :o) I'm assuming (again) that you didn't see my comment before posting your answer. Regardless, at this time, you'll see that your comment mirrors mine and you should change yours back to a Comment. If this is the Answer u8semaj uses, s/he will have to decide which of us to give the points to.

Thanks for your cooperation, and I look forward to seeing you around EE.

brewdog
0
 
LVL 10

Expert Comment

by:brewdog
ID: 2551390
thanks for the followup comment, jkrautner. I believe you have the ability to change your Answer to a Comment, which would help u8semaj's options at this point. :o)
0
 

Author Comment

by:u8semaj
ID: 2551473
brewdog,

is there a way to split the counts, so that when i combine my lists to create a single report, the individual amounts are not combined.

example:

if I have
100  1
200  1
900  2

is there a way to have the report list as
100
200
900
900

I rejected jkrautner's answer only so that others could comment
0
 
LVL 10

Accepted Solution

by:
brewdog earned 500 total points
ID: 2551867
If you're looking for a final report from this, leave the queries as I wrote them, design the report with both the dollar amount and the Times fields in the detail section (but set the Visible property of Times to No), and add a label (if you want) with no Caption. In the label, we could list which query/table the value appears in. Then put this for the code behind the report:

************************************************************
Option Compare Database
Option Explicit

    Dim intCounter As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    If intCounter = Abs(Difference) Then
        Me.MoveLayout = True
        Me.NextRecord = True
        Me.PrintSection = True
        intCounter = 1
    Else
        Me.NextRecord = False
        Me.PrintSection = True
        intCounter = intCounter + 1
    End If
    If Difference < 0 Then
        lblSource.Caption = "Query Two"
    Else
        lblSource.Caption = "Query One"
    End If

End Sub

Private Sub Report_Open(Cancel As Integer)

    intCounter = 1

End Sub

I just tested this and it worked beautifully. Let me know if you have any questions . . .

brewdog
0
 

Author Comment

by:u8semaj
ID: 2551931
Adjusted points to 500
0
 

Author Comment

by:u8semaj
ID: 2551937
Thank you for your help.
0
 
LVL 10

Expert Comment

by:brewdog
ID: 2551945
You are very welcome. That was kind of fun to work on. :o) A nice break from my regular work!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

760 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

20 Experts available now in Live!

Get 1:1 Help Now