CODEWITHSUNDEEP
sqlms-accesssql-insert
PythonF
PythonF

Reputation: 496

Access Append Query compare with table

I am currently rebuilding a messy Access Database and I entcountered the following problem:

I've got a Table of facilities which contain a row called district. Those Rows contain a number linked to another table which just contains the numbers and names of districts. I added a lookup Column with the Name of the district displayed.

I now want to change the new column for every row depending on the data in the old row.

Facilities

NAME|..|DISTRICT_OLD
A   |..|           1
B   |..|           2
C   |..|           1
...

DISTRICTS

ID|NAME
1 |EAST
2 |WEST
...

I would like something like the following: Facilities

NAME|..|DISTRICT_OLD|DISTRICT
A   |..|           1|EAST
B   |..|           2|WEST
C   |..|           1|EAST
...

The District Field (lookup) gets its Data like follows SELECT [DISTRICTS].ID, [DISTRICTS].NAME FROM DISTRICTS ORDER BY [NAME];

(Thanks to Gordon Linoff) I could get the query but I do now struggle with the insert. I can get the Data I want:

SELECT [DISTRICTS].NAME FROM Facilities INNER JOIN DISTRICTS ON Facilities.DISTRICT_OLD = [DISTRICTS].ID;

If I try to INSERT INTO Facilities(DISTRICT) It says Typerror. How can I modify the data to be compatible with a lookup column? I guess I need to select the ID as well which isnt't a problem but then the error says to many columns.

I hope I haven't mistaken any names, my Access isn't running the english language.

Can you help me?

Fabian

Upvotes: 1

Views: 78

Answers (1)

trevor
trevor

Reputation: 267

Lookup columns are number (long integer)

with a relational database, you only need the single column containing the ID (as you always lookup the district.name with a query) so:

INSERT INTO Facilities(DISTRICT) SELECT 4

where 4 is the ID of the record in the lookup table that you want, or better still:

INSERT INTO Facilities(DISTRICT) 
    SELECT ID FROM DISTRICTS 
        where District.Name = "Name you want the ID for"

Upvotes: 1

Related Questions