×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Comparing Data What's the best way?

Comparing Data What's the best way?

Comparing Data What's the best way?

(OP)
I have two tables. They both contain the field "InvoiceNumber". Table A has 1 record for "InvoiceNumber". Table B has multiple records for "InvoiceNumber". Both also have an "Amount" field and a "Quantity" field. I am trying to compare the "Amount" and "Quantity" fields for the matching records from each Table. Obviously the data from Table B will have multiple records and will need to be summed before they are compared.
I will also need to verify table B has at least one matching record for each record in table A.

Any idea on the best way to accomplish this?

RE: Comparing Data What's the best way?

Do you have a relation establieshed between Table A (parent) and Table B (child) based on PK-FK (one-to-many) of InvoiceNumbe?

And, if a records in Table B has 10, 12, and 15 for Quantity, do you have to have Quantity in Table A of the sum of 10, 12, and 15?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Comparing Data What's the best way?

(OP)
Yes, I have a relationship established.
Quantity (one record) in Table A should match the sum of quantity (multiple records) in table B.
We have a customer that pays our invoices on separate line items. we only use one line to invoice. I am working on a method to reconcile the two.

RE: Comparing Data What's the best way?

So it looks to me you are looking for Parent records (Table A) that do not have children records (Table B)

In Oracle I would try:
Select InvoiceNumber From TableB
MINUS
Select InvoiceNumber From TableA


I hope you can do the same in Access

or (this will work in Access)

Select InvoiceNumber From TableA
Where InvoiceNumber NOT IN (
Select InvoiceNumber From TableB)


But – in my opinion, it is very bad idea to have calculated fields in your DB.
By ‘calculated’ I mean: you have 10-12-15 in one table, and a Sum of it in another. Why….?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close