473,324 Members | 1,678 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Compare two tables, only 1 common field

sgood
5
A little background first. I am currently working with data from two seperate applications. Both have been exported to CSV files, then queried into their own tables in the access database. The field names do not match, and they each contain a different number of fields. The only thing similar about these tables is the data in field rcpt_number and QB_num. I am running the query through ColdFusion, and trying to output the data from one table that does not match the other based on the two common fields.

Here's a few rows of the data.

Table: ProfilerCSV (from csv file)

Expand|Select|Wrap|Line Numbers
  1. office,paid_date,rcpt_number,rcpt_year,lastname,pri_ssn,product,prepared,transmited,totalfee,amtpaid,credit,how_paid,deposits,creditdeps,difference,creddifference
  2. 13110,4/13/2006,44166,2005,MCDANIEL,XXXXXXXXX,PAP,1-Feb-05,13-Apr-06,89,89,0,Check,0,0,-89,0
  3. 13772,4/30/2006,10001,2006,BRIGGS,XXXXXXXXX,RAL,20-Mar-06,21-Mar-06,215,0,0,Cash,0,0,0,0
  4. 13772,4/25/2006,10003,2006,JORDAN,XXXXXXXXX,PAP,21-Mar-06,25-Apr-06,150,150,0,Check,0,0,-150,0
  5. 13772,4/30/2006,10011,2006,JACKSON JR,XXXXXXXXX,AR,23-Mar-06,23-Mar-06,252,0,0,Cash,0,0,0,0
  6. 13772,4/3/2006,10020,2006,HOPKINS,XXXXXXXXX,ETR,28-Mar-06,3-Apr-06,101,101,0,Check,0,0,-101,0
  7. 13772,4/1/2006,10028,2006,BARTHEN,XXXXXXXXX,ETR,1-Apr-06,1-Apr-06,491,0,491,Credi,0,0,0,-491
  8. 13772,4/1/2006,10030,2006,PRESTON,XXXXXXXXX,PAP,1-Apr-06,3-Apr-06,211,0,211,Credi,0,0,0,-211
  9. 13772,4/1/2006,10031,2006,PRESTON,XXXXXXXXX,PAP,1-Apr-06,2-Apr-06,225,0,225,Credi,0,0,0,-225
  10. 13772,4/3/2006,10033,2006,WARREN,XXXXXXXXX,PAP,3-Apr-06,3-Apr-06,79,79,0,Cash,0,0,-79,0
  11. 13772,4/3/2006,10034,2006,SULLIVAN,XXXXXXXXX,ETR,3-Apr-06,3-Apr-06,256,0,256,Credi,0,0,0,-256
  12. 13772,4/30/2006,10035,2006,THOMAS,XXXXXXXXX,AR,3-Apr-06,4-Apr-06,286,0,0,Cash,0,0,0,0
  13. 13772,4/4/2006,10036,2006,RUIZ,XXXXXXXXX,ETR,4-Apr-06,4-Apr-06,452,452,0,Check,0,0,-452,0
  14. 13772,4/4/2006,10037,2006,KOEHLER,XXXXXXXXX,ETR,4-Apr-06,4-Apr-06,179,179,0,Check,0,0,-179,0
  15. 13772,4/4/2006,10038,2006,SAMPLE,XXXXXXXXX,ETR,4-Apr-06,4-Apr-06,130,130,0,Check,0,0,-130,0
  16. 13772,4/14/2006,10039,2006,ISHIE SR,XXXXXXXXX,ETR,4-Apr-06,14-Apr-06,337,337,0,Check,0,0,-337,0
  17. 13772,4/4/2006,10040,2006,REYES,XXXXXXXXX,ETR,4-Apr-06,4-Apr-06,108,108,0,Cash,0,0,-108,0
  18. 13772,4/4/2006,10041,2006,MCCORMICK,XXXXXXXXX,ETR,4-Apr-06,4-Apr-06,88,88,0,Check,0,0,-88,0
  19. 13772,4/30/2006,10042,2006,CRUZ,XXXXXXXXX,AR,4-Apr-06,4-Apr-06,163,0,0,Cash,0,0,0,0
  20. 13772,4/13/2006,10044,2006,PETERSON,XXXXXXXXX,ETR,5-Apr-06,13-Apr-06,239,239,0,Check,0,0,-239,0
Table: QuickbooksCSV (from csv file)

Expand|Select|Wrap|Line Numbers
  1. QB_type,QB_date,QB_num,QB_memo,QB_name,QB_qty,QB_sales_price,QB_amount,QB_balance,QB_junk
  2. Sales Receipt,4/1/2006,16445,Dixon-13772-16445 collections,Cash\Check Sales:Cash\Check Sales-13772,1,158,158,158,--
  3. Sales Receipt,4/1/2006,16743,Brumfield-13772-16743 collections,Cash\Check Sales:Cash\Check Sales-13772,1,379,379,537,--
  4. Sales Receipt,4/1/2006,22124,50228 Greene 22124,Cash\Check Sales:Cash\Check Sales-50228,1,134,134,671,--
  5. Sales Receipt,4/1/2006,13513,mai  13513  11998,Cash\Check Sales:Cash\Check Sales-11998,1,241,241,912,--
  6. Sales Receipt,4/1/2006,18160,Ward #18160 #16342,Cash\Check Sales:Cash\Check Sales-16342,1,93,93,1005,--
  7. Sales Receipt,4/1/2006,19305,Muniz-16828-19305,Cash\Check Sales:Cash\Check Sales-16828,1,97,97,1102,--
  8. Sales Receipt,4/1/2006,15304,Polson-13110-15304,Cash\Check Sales:Cash\Check Sales-13110,1,189,189,1291,--
  9. Sales Receipt,4/1/2006,15310,Tabor-13110-15310,Cash\Check Sales:Cash\Check Sales-13110,1,93,93,1384,--
  10. Sales Receipt,4/1/2006,15305,Stout-13110-15305,Cash\Check Sales:Cash\Check Sales-13110,1,215,215,1599,--
  11. Sales Receipt,4/1/2006,15309,Williamson-13110-15309,Cash\Check Sales:Cash\Check Sales-13110,1,236,236,1835,--
  12. Sales Receipt,4/1/2006,15307,Smith-13110-15307,Cash\Check Sales:Cash\Check Sales-13110,1,119,119,1954,--
  13. Sales Receipt,4/1/2006,15306,Williamson-13110-15306,Cash\Check Sales:Cash\Check Sales-13110,1,100,100,2054,--
  14. Sales Receipt,4/1/2006,15308,Greenroy-13110-15308,Cash\Check Sales:Cash\Check Sales-13110,1,155,155,2209,--
  15. Sales Receipt,4/1/2006,15303,Floyd-13110-15303,Cash\Check Sales:Cash\Check Sales-13110,1,240,240,2449,--
  16. Sales Receipt,4/1/2006,19311,Naqvi-16828-19311,Cash\Check Sales:Cash\Check Sales-16828,1,127,127,2576,--
  17. Sales Receipt,4/1/2006,22717,stine  22717  50284,Cash\Check Sales:Cash\Check Sales-50284,1,58,58,2634,--
  18. Sales Receipt,4/1/2006,22716,sergeant  22716  50284,Cash\Check Sales:Cash\Check Sales-50284,1,256,256,2890,--
  19. Sales Receipt,4/1/2006,12582,Olivarez#11857#12582,Cash\Check Sales:Cash\Check Sales-11857,1,58,58,2948,--
  20. Sales Receipt,4/2/2006,20992,Thomson-20992-50220,Cash\Check Sales:Cash\Check Sales-50220,1,144,144,3092,--
Thanks in advance for any help!
~Steve
Dec 5 '06 #1
8 2766
Killer42
8,435 Expert 8TB
This is what the Access "Find Unmatched" wizard thew together for me...
Expand|Select|Wrap|Line Numbers
  1. SELECT ProfilerCSV.ID FROM ProfilerCSV
  2. LEFT JOIN QuickbooksCSV
  3. ON ProfilerCSV.rcpt_number = QuickbooksCSV.QB_num
  4. WHERE (QuickbooksCSV.QB_num Is Null);
Dec 6 '06 #2
sgood
5
This is what the Access "Find Unmatched" wizard thew together for me...
Expand|Select|Wrap|Line Numbers
  1. SELECT ProfilerCSV.ID FROM ProfilerCSV
  2. LEFT JOIN QuickbooksCSV
  3. ON ProfilerCSV.rcpt_number = QuickbooksCSV.QB_num
  4. WHERE (QuickbooksCSV.QB_num Is Null);

Thanks! I'll try this when I get to work tomorrow and will let you know the outcome.

~Steve
Dec 6 '06 #3
NeoPa
32,556 Expert Mod 16PB
This is what the Access "Find Unmatched" wizard thew together for me...
Expand|Select|Wrap|Line Numbers
  1. SELECT ProfilerCSV.ID FROM ProfilerCSV
  2. LEFT JOIN QuickbooksCSV
  3. ON ProfilerCSV.rcpt_number = QuickbooksCSV.QB_num
  4. WHERE (QuickbooksCSV.QB_num Is Null);
That's how I discovered that technique.
It's stood me in good stead for lots of different projects :).
Dec 7 '06 #4
Killer42
8,435 Expert 8TB
That's how I discovered that technique.
It's stood me in good stead for lots of different projects :).
Yeah, Access makes a handy tool, even when you don't use it as your database.
Dec 7 '06 #5
sgood
5
I used the query posted but ColdFusion ends up throwing errors about thequery.

Expand|Select|Wrap|Line Numbers
  1. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. The error occurred in D:\Inetpub\cashRec\exceptions.cfm: line 3
  2. Called from D:\Inetpub\cashRec\index.cfm: line 14
  3. Called from D:\Inetpub\cashRec\exceptions.cfm: line 3
  4. Called from D:\Inetpub\cashRec\index.cfm: line 14
  5. 1 : <cfif #SESSION.auth.AccountType# IS "Administrator" OR #SESSION.auth.AccountType# IS "Manager">
  6. 2 : 
  7. 3 : <cfquery datasource="#APPLICATION.datasource#" name="getData">
  8. 4 : SELECT ProfilerCSV.ID FROM ProfilerCSV
  9. 5 : LEFT JOIN QuickbooksCSV
  10.  
  11. SQL: SELECT ProfilerCSV.ID FROM ProfilerCSV 
  12. LEFT JOIN QuickbooksCSV
  13. ON ProfilerCSV.rcpt_number = QuickbooksCSV.QB_num 
  14. WHERE (QuickbooksCSV.QB_num Is Null); 
  15.  
  16. DATASOURCE: jhdsn
  17. VENDORERRORCODE: -3010
  18. SQLSTATE: 07002
Where do I find the wizard you used to make the query? I poked around Access, but nothing jumped out and bit me.
Dec 7 '06 #6
sgood
5
AHA! ignore my last post, I changed the SELECT statement from ProfilerCSV.ID to ProfilerCSV.rcpt_number

It dawned on me that I had no ID field, so I made one and no more error, then I changed the select statement and wahla! It works! Thanks again!

~Steve
Dec 7 '06 #7
Killer42
8,435 Expert 8TB
...Where do I find the wizard you used to make the query? I poked around Access, but nothing jumped out and bit me.
Glad you sorted out the problem - ID was probably just a primary key that I allowed Access to add, or something.

As for the wizard, it may depend on what options you have installed. But when I click the "New" button in the Queries tab, I get a selection dialogue listing "Design View", "Simple Query Wizard", "Crosstab Query Wizard" (hm... I must have a look at that one!), "Find Duplicates Query Wizard" and "Find Unmatched Query Wizard".
Dec 7 '06 #8
sgood
5
Cool! Thanks for the tip!
Dec 8 '06 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Rachel Curran | last post by:
Please can anybody help me with the following: I have two separate excel spreadsheets that I have imported into access, each sheet holds the same fields. Both spreadsheets hold all information...
5
by: Megan | last post by:
Hi everybody- I'm helping a friend with a music database. She has an old one and is creating a new one. She wants to compare records and fields in the old database with records and fields in the...
2
by: Megan | last post by:
Hi everybody- I have 2 tables, Hearings and Rulings. Both have primary keys called, CaseID, that are autonumbers. I don't want both tables to have the same autonumber. For example, if Hearings...
1
by: Prakash RudraRaju | last post by:
Hi, I have recently migrated MSAccess Tables (nearly 120) to MySQL. To test successful migration I want to compare all tables. I have linked MySQL tables through ODBC connection. I want to...
9
by: geronimo_me | last post by:
Hi, I am atempting to compare part of a field with the whole of another field in access. Is this possible? Basically I have 2 tables with the following info: Table1 Field1 = MR.
1
by: Mark | last post by:
by m.r.davies I have 2 tables on seperate Db's (and servers) I want to use a datareader on the first table to pick the booking ref, and then use that booking ref to query the 2nd DB when i have...
5
by: rcolby | last post by:
Evening, Wondering if someone can point me in the right direction, on how I would compare a system.guid with a system.byte. system.guid (pulled from sql server table with a data type of...
6
by: napatel04 | last post by:
Hi everyone, I would like to know if there is a quick query someone can help me write for the following scenario. I think I can do this with VBA but since this is suppose to be a temp. solution,...
6
osward
by: osward | last post by:
Hi everyone, I have query(s) that I need to perform which I couldn't figure out, and following is the situation I have event table (which stores the event details) that has eid, date, time,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.