Add records with specific ID number

[Alpi]

Hi everyone. Sorry if this already exists somewhere in the forum. I have a 'union query' with three columns. First column is 'ID number' , the second and the third are coordinates Lon and Lat. In this query I have about 600 ID numbers but only the half Lon and Lat. Now I have the rest of the coordinates in another table with the ID number and I want to add them in the query. This means I have to add in the query the records in the Lon, Lat columns according to the ID number. How can I do that?

Thanx
Alpi

 

[vbaInet]

Before we proceed, can you please justify why the union query? And show is the SQL statement of the union query and the other query you want to join it against.

Enclose them in Code tags:

http://www.access-programmers.co.uk/forums/showthread.php?p=1009015#post1009015

 

[Alpi]

Probably there was another way of doing it but I wanted to join different tables with more or less same fields. So the SQL for the Union query is

SELECT [Site_Information],[Date],[CH-X],[CH-Y],[Lon],[Lat],[Precision],[Field Elevation],[DEM Elevation],[Comment]
FROM [Latzin_2009_Comment_extraction]

UNION ALL SELECT [Site_Information],[Date],[CH-X],[CH-Y],[Lon],[Lat],[Precision],[Field Elevation],[DEM Elevation],[Comment]
FROM [Latzin_2010_Comment_extraction]

UNION ALL SELECT cstr([samp]) AS  'Site_Information',[Date],[CH-X],[CH-Y],[Lon],[Lat],[Precision],[Field  Elevation],[DEM Elevation],cstr(Bemerkung) AS 'Comment'
FROM [SF_2009]

UNION ALL SELECT cstr([sampled]) AS  'Site_Information',[Date],[CH-X],[CH-Y],[Lon],[Lat],[Precision],[Field  Elevation],[DEM Elevation],[Comment]
FROM [LF_2009]

UNION ALL SELECT [Site_Information],[Date],[CH-X],[CH-Y],[Lon],[Lat],[Precision],[Field Elevation],[DEM Elevation],[Comment]
FROM [Sammelorte SE_2010]

UNION ALL SELECT [Site_Information],[Date],[CH-X],[CH-Y],[Lon],[Lat],[Precision],[Field Elevation],[DEM Elevation],[Comment]
FROM [JS_2009];

So I didn't have all the [Lon], [Lat]. Now I have the rest of them in a table (not in a query):

numberfield 1 = Site_Information
numberfield 2 = Lon
numberfield 3 = Lat

I want to join them according with the [Site_Information] (instead of ID number I called it in my previous tag).

I hope it's not a mess..

Thanx

 

[vbaInet]

In the union query, are they all queries or tables? Or mixture of both?

 

[Alpi]

It's a mixture of both. [Latzin_2009_Comment_extraction] and [Latzin_2010_Comment_extraction] are queries and the rest are tables.

 

[vbaInet]

Right, you have a normalization problem then. What are the differences between [SF_2009] and [LF_2009]?

 

[Alpi]

Their fields had different names than the others if you mean that. I probably could rename them from the beginning..

 

[vbaInet]

The field names are different but the data is pretty similar?

 

[Alpi]

Yes the data are the same

 

[vbaInet]

So why did you split them in the first place?

 

[Alpi]

No I mean it is the same type. They have different records

 

[vbaInet]

Ok, same type of data but different records. This still doesn't justify why you split them into separate tables. Do you know about normalization?

 

[Alpi]

No sorry maybe I don't describe it well. They were originally different files all of them. They were excel files which I imported them in access. Then I made the union query as we said.

I don't know what the normalization is.

 

[vbaInet]

You could have imported all your data into the same table.

Anyway, what is the name of the query that has all the remaining Lats and Lons?

 

[Alpi]

It is 'CH to Lat-Lon'

 

[vbaInet]

* Create a new query and include your union query and the CH to Lat-Lon query in it.
* Join these two queries via the appropriate field.
* Change the join to an OUTTER JOIN so that ALL the records are pulled from the union query. If you are not clear about this let me know.
* Drop the required fields on the query
* In each of the Lat and Lon fields use Nz. For example:

 Nz([[COLOR=Red]UnionQueryName[/COLOR]].Lat, [CH to Lat-Lon].Lat)

Amend the red part.

 

[Alpi]

Thanks vbaInet. It worked fine. I guess there is no way just to add them directly in the Union query? And one last thing: Is it possible to tell me in a few words how I could have added the data in one table as you said? Maybe I could do it now.
Thanks a lot.

 

[vbaInet]

You're welcome!

Nope there isn't.

I will need to see your tables to be able to advice. If you have confidential data you can get rid of it and input some test data.

 

[Alpi]

Ok I will attach a database here, right?

 

[vbaInet]

Yes, that's right. Here's how to do it:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587