Importing problem

[dzibril]

Hello,

I have a specific problem with a database. It is a rather simple database with only two tables. The first, called the Header contains general info of certain archaeological objects like its dimensions, type, context number and image for instance. The second, called location contains the object's location within a coordinate grid consisting of x,y,z numerical values. The tables are connected via a secondary key [called Header_ID] in the Location table that duplicates the value of the appropriate primary key [called ID] field in the Header table.

Someone before me has input the details in the Header table, whilst the locations were left to be input after the coordinate data were downloaded from a total station. Now, I have this locational data organised in an excel table containing x,y,z values and the context id number, and would like to import it somehow into the location table in such a way so that they get linked with the adequate input in the header table (i.e. context 365 descriptive data to match context 365 location data). The only way I see how is to tell it to place the data in respect to the value of the context number which is the same for the header table input and location table input. Now, I am not sure how to do that without manually inputting each of the 300+ values. Any help on this?

 

[MikeLeBen]

Hello and welcome to the forums.

This sounds like something you could easily handle with a query.
First off: have you imported on an Access table the data from the Excel file? There is an easy default feature to do that, in my version (2010) it is under the section "External Data" (or something, mine is in Italian).

Once that is done, you can use the query design tool to create an append query to insert the records using the criteria you specify, specifically that a foreign ID Key must be added in the proper field for each record.

 

[dzibril]

I have imported the data into a new access table within the database. Afterwards I have tried to use the append query, but when I run it, it always says you're about to import 0 records into table... and I can't seem to find a reason why.
the query is as follows

INSERT INTO Lokacija ( x, y, z )
SELECT Edm2012.x, Edm2012.y, Edm2012.z
FROM Edm2012, Lokacija
WHERE ([Lokacija].[EDM_Br]=Edm2012.EDM);

So, is this query worth anything?

 

[MikeLeBen]

you know what could help lots?
Please post the names and structures of your tables.

I think if the foreign key is already there you might not need an append query at all.

 

[dzibril]

table 1.

Inventar - contains descriptive data
fields:
ID - primary key
Inventory_num
Date
Sector
Spit
Description
Length
Width
Diameter
Dating
Housing
Drawing

table 2
Lokacija - contains location of the item
ID - primary key
ID_Inv - foreign key
X
Y
Z
EDM_num

table 3
EDM2012 - imported Excel data
EDM_num
x
y
z

 

[dzibril]

I need to import data from table 3 into table 2 in such way so that locational data (x,y,z fields) of a specific EDM_num in table 2 matches the descriptive data associated with the adequate Inventory_num in table 1 (i.e. EDM_num=Inventory_num)

 

[MikeLeBen]

Right, so you only need to "move" the data, like this?

INSERT INTO Lokacija ( x, y, z, EDM_num )
SELECT Edm2012.x, Edm2012.y, Edm2012.z, Edm2012.EDM_num
FROM Edm2012

 

[dzibril]

I need to move the data into specific rows, not append them. So in table 2 I already have the foreign key data and edm field filled, now I need to insert the data from table 3 in such a way so that each location field (x,y,z) data from table 3 matches the adequate edm already present in table 2. For example I need to import coordinate 1,1,1 of edm 1 from table 3 to edm 1 row in table 2 and not as a new row as append does.

 

[dzibril]

How I solved it, using a workaround.
I created a new table using the following query

SELECT Lokacija.ID, Lokacija.[ID Inventar], Edm2012.x, Edm2012.y, Edm2012.z, Edm2012.EDM INTO new
FROM Lokacija, Edm2012
WHERE Lokacija.Edm_Br=Edm2012.EDM;

The query put in the new table the fields that I needed from table 2 and fields that I needed from table 3 if they met the criteria of edm number equality.

After checking to see that the data were copied correctly, I deleted the Location table and renamed the new table to Location (as it was in fact the same in structure).

Not the best of solutions, but it worked.

 

[DaveBerube]

You should be able to do an update query linked to EDM to update the coordinates in Lokacija to the Coordinates in Edm2012