Data looks ok.....but is it? (1 Viewer)

[Groundrush]

Has anyone ever had this problem before and if yes how was it solved?.

I am comparing data from 2 differant databases, one of them being Access and the other is located through our ODBC Network
which is located on another Server some miles away

I am using Access to create the Union Qry which works fine

the problem is that the data from one table should match the other and when running the Union Qry it returns all matching results rather than removing them.

the qry works if I copy the data from the ODBC table and Paste it back using "Paste Special" and choosing "Text"

any Ideas

Thanks

 

[pdx_man]

What exactly are you looking to do. Do you know what a Union Query does? As long as the # of fields are the same and the datatypes are similar, a Union Query will return ALL the records from each table. That is what that type of query is supposed to do. If you want a single instance of when the records are the same, then you want to JOIN the tables in a SELECT query, joining on the fields that contain the information you wish to check similarity.

 

[Groundrush]

the qry I have compares and removes all the same entries and returns whats left

i.e table 1
DEFR4
DEFT2

table 2
DEFR4
DEFT1

The qry will return deft2 and deft1 as defr4 is a match

the problem is that on one of the tables that the qry uses has to be copied and pasted back into it for it to work

I think for some reason the data type must be different.

 

[pdx_man]

Please post the SQL of your query.


What is the datasource for the remote table? SQL Server, Oracle, Access? You can write a query that will convert the data to the desired datatypes, and then reference that query in your UNION query.

 

[Groundrush]

The Data source for one of the tables is from the SQL Server
the other is in Access

I have created qry's for both sources to sum all the totals and then to return the results in 2 make tables

I then use the union qry which is based on the 2 make tables
to compare each record, remove matches and return unmatched

to make things worse I had to create 3 qry's for the SQL Table

1) to return all records within a specified date
2) to sum all matching records
3) to remove all nulls

I know the qry works, but I have to copy the make table results and paste special them back into the tables

I cant think of why I'm having this problem apart from conflicting data types

Hope I havent scared you off......hello are you still there?

 

[pdx_man]

I'm still here. I've been responsive to you ... This query has issues. Lots of issues. (You are doing a UNION on two queries that do opposing LEFT joins where your joins are based on calculated values? Have you tried an INNER JOIN?) But, that is a discussion for another topic. I would say, yes, your issue is with your datatypes not being compatable. As I mentioned in my previous post, you may want to have a Pass-Through Query that CASTs your datatypes into those comparable to Access. When you paste them, Access makes a (good) guess as to what the datatypes should be and this is working ...

 

[Groundrush]

Sorry about that my sense of humour does not come across very well on computers, I guess you can say the same about my Access qry's

Thanks for you help

 

[pdx_man]

No prob, that is why we have the Smilies over here on the left.
.