Add records with specific ID number

[Alpi]

Hi!

I attached the zip file as described. Now what I want is to create a final table or query with all the tables and queries like in the Union Query (Final_Site). Let me know if it's anything wrong.

Thanks!

 

[Alpi]

vbaInet hi!

I was wondering if you had the chance to take a look at the zip file I send you in my last post. I understand that you might be busy..

Cheers

 

[vbaInet]

I just had a quick look. To be honest all your tables need to be normalized. I can see that some of your tables can be merged into one and the repeated fields should actually be junction tables.

Unfortunately, I won't be able to look at your db into more depth to be able to give you some useful info, so the best thing is to create a new thread in the TABLES section just asking for some help on normalizing your data and I'm sure someone will be able to go through the steps with you.

 

[Alpi]

Ok thanks a lot!

 

[vbaInet]

No problem!

I will keep an eye out for your thread and if I have something to add I will post a reply.

Good luck with the rest of your project.

 

[Alpi]

vbaInet hi!

Sorry I'm coming back with my normalization problem. I'm trying to do the procedure you suggested in the previous posts with the expression Nz([UnionQueryName].Lat, [CH to Lat-Lon].Lat) in a new query and the problem is that it returns only the records of the 'CH to Lat-Lon' table. It should return all the records of the 'Union query' as I select in the join properties 'All the records of the Union query and only those of the 'CH to Lat-Lon' where the joined fields are equal. Any ideas what could be wrong?
Thanx

 

[vbaInet]

Let me see the SQL statement.

 

[Alpi]

SELECT FINAL_SITE.Site_Information, FINAL_SITE.Date, FINAL_SITE.[CH-X], FINAL_SITE.[CH-Y], DATA_FROM_GIS.Lon, DATA_FROM_GIS.Lat, FINAL_SITE.Precision, FINAL_SITE.[Field Elevation], DATA_FROM_GIS.DEM_Elevation, FINAL_SITE.Comment
FROM FINAL_SITE LEFT JOIN DATA_FROM_GIS ON FINAL_SITE.Site_Information = DATA_FROM_GIS.Site_Information
WHERE (((DATA_FROM_GIS.Lon)=Nz([FINAL_SITE].[Lon],[DATA_FROM_GIS].[Lon])) AND ((DATA_FROM_GIS.Lat)=Nz([FINAL_SITE].[Lat],[DATA_FROM_GIS].[Lat])));

The "FINAL_SITE" is the union query and the "DATA FROM GIS" is the query with the Lat-Lons.

Let me know if you see anything strange..

Thanks a lot!

 

[vbaInet]

I don't think I mentioned anything about setting criteria. What I meant by the last bullet point is replace the field name with the Nz(..) calculation.

So remove the criteria.

 

[Alpi]

Haven't got that right. It works now.
Thanks!

 

[vbaInet]

Good to hear!