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) - office,paid_date,rcpt_number,rcpt_year,lastname,pri_ssn,product,prepared,transmited,totalfee,amtpaid,credit,how_paid,deposits,creditdeps,difference,creddifference
-
13110,4/13/2006,44166,2005,MCDANIEL,XXXXXXXXX,PAP,1-Feb-05,13-Apr-06,89,89,0,Check,0,0,-89,0
-
13772,4/30/2006,10001,2006,BRIGGS,XXXXXXXXX,RAL,20-Mar-06,21-Mar-06,215,0,0,Cash,0,0,0,0
-
13772,4/25/2006,10003,2006,JORDAN,XXXXXXXXX,PAP,21-Mar-06,25-Apr-06,150,150,0,Check,0,0,-150,0
-
13772,4/30/2006,10011,2006,JACKSON JR,XXXXXXXXX,AR,23-Mar-06,23-Mar-06,252,0,0,Cash,0,0,0,0
-
13772,4/3/2006,10020,2006,HOPKINS,XXXXXXXXX,ETR,28-Mar-06,3-Apr-06,101,101,0,Check,0,0,-101,0
-
13772,4/1/2006,10028,2006,BARTHEN,XXXXXXXXX,ETR,1-Apr-06,1-Apr-06,491,0,491,Credi,0,0,0,-491
-
13772,4/1/2006,10030,2006,PRESTON,XXXXXXXXX,PAP,1-Apr-06,3-Apr-06,211,0,211,Credi,0,0,0,-211
-
13772,4/1/2006,10031,2006,PRESTON,XXXXXXXXX,PAP,1-Apr-06,2-Apr-06,225,0,225,Credi,0,0,0,-225
-
13772,4/3/2006,10033,2006,WARREN,XXXXXXXXX,PAP,3-Apr-06,3-Apr-06,79,79,0,Cash,0,0,-79,0
-
13772,4/3/2006,10034,2006,SULLIVAN,XXXXXXXXX,ETR,3-Apr-06,3-Apr-06,256,0,256,Credi,0,0,0,-256
-
13772,4/30/2006,10035,2006,THOMAS,XXXXXXXXX,AR,3-Apr-06,4-Apr-06,286,0,0,Cash,0,0,0,0
-
13772,4/4/2006,10036,2006,RUIZ,XXXXXXXXX,ETR,4-Apr-06,4-Apr-06,452,452,0,Check,0,0,-452,0
-
13772,4/4/2006,10037,2006,KOEHLER,XXXXXXXXX,ETR,4-Apr-06,4-Apr-06,179,179,0,Check,0,0,-179,0
-
13772,4/4/2006,10038,2006,SAMPLE,XXXXXXXXX,ETR,4-Apr-06,4-Apr-06,130,130,0,Check,0,0,-130,0
-
13772,4/14/2006,10039,2006,ISHIE SR,XXXXXXXXX,ETR,4-Apr-06,14-Apr-06,337,337,0,Check,0,0,-337,0
-
13772,4/4/2006,10040,2006,REYES,XXXXXXXXX,ETR,4-Apr-06,4-Apr-06,108,108,0,Cash,0,0,-108,0
-
13772,4/4/2006,10041,2006,MCCORMICK,XXXXXXXXX,ETR,4-Apr-06,4-Apr-06,88,88,0,Check,0,0,-88,0
-
13772,4/30/2006,10042,2006,CRUZ,XXXXXXXXX,AR,4-Apr-06,4-Apr-06,163,0,0,Cash,0,0,0,0
-
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) - QB_type,QB_date,QB_num,QB_memo,QB_name,QB_qty,QB_sales_price,QB_amount,QB_balance,QB_junk
-
Sales Receipt,4/1/2006,16445,Dixon-13772-16445 collections,Cash\Check Sales:Cash\Check Sales-13772,1,158,158,158,--
-
Sales Receipt,4/1/2006,16743,Brumfield-13772-16743 collections,Cash\Check Sales:Cash\Check Sales-13772,1,379,379,537,--
-
Sales Receipt,4/1/2006,22124,50228 Greene 22124,Cash\Check Sales:Cash\Check Sales-50228,1,134,134,671,--
-
Sales Receipt,4/1/2006,13513,mai 13513 11998,Cash\Check Sales:Cash\Check Sales-11998,1,241,241,912,--
-
Sales Receipt,4/1/2006,18160,Ward #18160 #16342,Cash\Check Sales:Cash\Check Sales-16342,1,93,93,1005,--
-
Sales Receipt,4/1/2006,19305,Muniz-16828-19305,Cash\Check Sales:Cash\Check Sales-16828,1,97,97,1102,--
-
Sales Receipt,4/1/2006,15304,Polson-13110-15304,Cash\Check Sales:Cash\Check Sales-13110,1,189,189,1291,--
-
Sales Receipt,4/1/2006,15310,Tabor-13110-15310,Cash\Check Sales:Cash\Check Sales-13110,1,93,93,1384,--
-
Sales Receipt,4/1/2006,15305,Stout-13110-15305,Cash\Check Sales:Cash\Check Sales-13110,1,215,215,1599,--
-
Sales Receipt,4/1/2006,15309,Williamson-13110-15309,Cash\Check Sales:Cash\Check Sales-13110,1,236,236,1835,--
-
Sales Receipt,4/1/2006,15307,Smith-13110-15307,Cash\Check Sales:Cash\Check Sales-13110,1,119,119,1954,--
-
Sales Receipt,4/1/2006,15306,Williamson-13110-15306,Cash\Check Sales:Cash\Check Sales-13110,1,100,100,2054,--
-
Sales Receipt,4/1/2006,15308,Greenroy-13110-15308,Cash\Check Sales:Cash\Check Sales-13110,1,155,155,2209,--
-
Sales Receipt,4/1/2006,15303,Floyd-13110-15303,Cash\Check Sales:Cash\Check Sales-13110,1,240,240,2449,--
-
Sales Receipt,4/1/2006,19311,Naqvi-16828-19311,Cash\Check Sales:Cash\Check Sales-16828,1,127,127,2576,--
-
Sales Receipt,4/1/2006,22717,stine 22717 50284,Cash\Check Sales:Cash\Check Sales-50284,1,58,58,2634,--
-
Sales Receipt,4/1/2006,22716,sergeant 22716 50284,Cash\Check Sales:Cash\Check Sales-50284,1,256,256,2890,--
-
Sales Receipt,4/1/2006,12582,Olivarez#11857#12582,Cash\Check Sales:Cash\Check Sales-11857,1,58,58,2948,--
-
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
8 2766
This is what the Access "Find Unmatched" wizard thew together for me... - SELECT ProfilerCSV.ID FROM ProfilerCSV
-
LEFT JOIN QuickbooksCSV
-
ON ProfilerCSV.rcpt_number = QuickbooksCSV.QB_num
-
WHERE (QuickbooksCSV.QB_num Is Null);
This is what the Access "Find Unmatched" wizard thew together for me... - SELECT ProfilerCSV.ID FROM ProfilerCSV
-
LEFT JOIN QuickbooksCSV
-
ON ProfilerCSV.rcpt_number = QuickbooksCSV.QB_num
-
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
NeoPa 32,556
Expert Mod 16PB
This is what the Access "Find Unmatched" wizard thew together for me... - SELECT ProfilerCSV.ID FROM ProfilerCSV
-
LEFT JOIN QuickbooksCSV
-
ON ProfilerCSV.rcpt_number = QuickbooksCSV.QB_num
-
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 :).
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.
I used the query posted but ColdFusion ends up throwing errors about thequery. -
[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
-
Called from D:\Inetpub\cashRec\index.cfm: line 14
-
Called from D:\Inetpub\cashRec\exceptions.cfm: line 3
-
Called from D:\Inetpub\cashRec\index.cfm: line 14
-
1 : <cfif #SESSION.auth.AccountType# IS "Administrator" OR #SESSION.auth.AccountType# IS "Manager">
-
2 :
-
3 : <cfquery datasource="#APPLICATION.datasource#" name="getData">
-
4 : SELECT ProfilerCSV.ID FROM ProfilerCSV
-
5 : LEFT JOIN QuickbooksCSV
-
-
SQL: SELECT ProfilerCSV.ID FROM ProfilerCSV
-
LEFT JOIN QuickbooksCSV
-
ON ProfilerCSV.rcpt_number = QuickbooksCSV.QB_num
-
WHERE (QuickbooksCSV.QB_num Is Null);
-
-
DATASOURCE: jhdsn
-
VENDORERRORCODE: -3010
-
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.
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
...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".
Cool! Thanks for the tip!
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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.
|
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...
|
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...
|
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,...
|
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,...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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....
|
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
| |