Do i need a custom program for this?

[hokie]

I have 2 tables [Table1] and [table2]with a unique reference number in table1 and this reference number is in the other table multiple times. I wanted to have a field in Table1 (deposit amount) subtracted from a field in table2 (expected deposit) but in table2 there are multiple lines with the same reference number so I need those added up and then subracted from the same reference number in Table1. I can't even get the query to do a regular join between these two tables muchless get the math part working. Would rather use a query than VBA code because it has been 4 years since I did any real programming and that was under JAVA.

[jgelpi]

Try this...
1)Create one query [SumQuery1], make it a Sum Query to Sum [Table2].
2)Create a relationship between [Table1] and your [SumQuery1] - Just a standard relationship.
3)Create a third Query [Query2] and add [Table1] and [SumQuery1] to it.
4)Bring all fields from [Table1] and the Sum field from [SumQuery1].
5)Create another field and use this expression:
Diff: [SumOfAmount]-[Amount]
where SumOfAmount is from your [SumQuery1] and Amount is from your [Table1]

[hokie]

when I do step 2 I get the same problem where the query returns no results. Could it be something related to the way the fields were imported from the outside source? They are like bank reference numbers but it is put in the table as 'text' since there are some exceptions....

edit: could number stored as text be causing the problem?

[jgelpi]

It shouldn't matter. AS LONG AS the fields in both tables are the same (in your case, Text). One cannot be classified as text and the other as numeric, etc. I have included a copy of my example database.

[hokie]

Think i found the problem. It was imported from Excel so number as text had an (') in front of the numbers which created a space on the one table but for some reason the other table has no space in front of the numbers. Do you have any kind of quick way to correct this problem?

[jgelpi]

I have not heard of that. You're stating that when you import the data from Excel that it will come across as '153453x, or something of that nature? And that leading ' is not in the Excel file?

[hokie]

Yea but it replaced the ('12345) with ( 12345) space before the 12345. I donno but I've tested bringing in the same data and got it to import correctly without the space. I'll let you know how it works out now.
thanks

[jgelpi]

Good to hear! I'm not sure why the data imported that way. Best of luck to ya!

[hokie]

Great it is working as I had imagined. Thanks for your help.

[jgelpi]

Glad it worked out for you!