asked on

Combine Two Datasets

Dear all,

I'm trying to combine two datasets, tables LocationData and MonitoredData based on matching values in field2 and field3. All field types are string. Sorry for the names I decided to make it simple to explain it.

Does the below query look like it will work? Its not returning any results.

SELECT [LocationData].Field1, [LocationData].Field4, MonitoredData.Field2, MonitoredData.Field3 INTO MappingTable
FROM [LocationData] INNER JOIN MonitoredData ON [LocationData].Field3= MonitoredData.Field3
WHERE ((([LocationData].Field1) Is Not Null) AND ((MonitoredData.Field2)=[LocationData].Field2) AND ((MonitoredData.Field3)=[LocationData].Field3));

Open in new window

Thanks

Gustav Brock

It could be reduced to:

SELECT
[LocationData].Field1,
[LocationData].Field4,
MonitoredData.Field2,
MonitoredData.Field3
INTO
MappingTable
FROM
[LocationData],
MonitoredData
WHERE
[LocationData].Field1 Is Not Null
AND
MonitoredData.Field2 = [LocationData].Field2
AND
MonitoredData.Field3 = [LocationData].Field3;

/gustav

HainKurt

Its not returning any results.

of course it will not... it is inserting into some table...

remove into statement...

SELECT l.Field1, l.Field4, m.Field2, m.Field3
FROM [LocationData] l 
INNER JOIN MonitoredData m ON l.Field3= m.Field3 AND m.Field2=l.Field2 AND l.Field1 Is Not Null

Open in new window

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial