CODEWITHSUNDEEP
sqlms-access
Esben Jensen
Esben Jensen

Reputation: 49

How can i make a MS access form from query in MS access

Have a Access query like the one below, i want to make a form from it so i can edit the fields but Access, comes with this error: "Microsotf Access can't reprensent the join expression m1.user_id AND m1.meta_key ='first_name'" in Design view.

In table view the data is correct!

SELECT wp_usermeta.meta_value
FROM wp_users INNER JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id;
SELECT
    u1.id,
    u1.user_login,
    m1.meta_value AS firstname,
    m2.meta_value AS lastname
FROM ((wp_users u1
INNER JOIN wp_usermeta m1 ON (m1.user_id = u1.id AND m1.meta_key = 'first_name'))
INNER JOIN wp_usermeta m2 ON (m2.user_id = u1.id AND m2.meta_key = 'last_name'))

Upvotes: 0

Views: 31

Answers (1)

Erik A
Erik A

Reputation: 32682

This is expected behaviour. Design view can never represent joins that aren't simple joins (not Table1.Column = Table2.Column).

If you're using only inner joins, and need to use design view, you can rewrite the query to the following:

SELECT
    u1.id,
    u1.user_login,
    m1.meta_value AS firstname,
    m2.meta_value AS lastname
FROM wp_users u1, wp_usermeta m1, wp_usermeta m2
WHERE m1.user_id = u1.id AND m1.meta_key = 'first_name' AND m2.user_id = u1.id AND m2.meta_key = 'last_name'

Access can represent this, but won't show the joins because now there aren't any.

Note that this only influences design view for queries. The query is perfectly functional, and you can use it in forms if you wish.

Upvotes: 1

Related Questions