Share via

Join query performance vs calculated field

Anonymous
2017-12-08T21:03:11+00:00

Hi,

In my first table, I have a look up field that this field is primary key of another table. This look up field is a compound field and it is look like this "601_5500025".

I could use calculated field in my first table and break look up field to 601 and 5500025 or I could use join query and directly get result from second table or I could use a query, based on first table and add calculated field to query and get the result.

I want to know what are these ways have better performance than other. 

Thanks in advance.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Tom van Stiphout 40,211 Reputation points MVP Volunteer Moderator
2017-12-09T02:17:59+00:00

Forget about lookup fields - they seem like tasty morsels but leave a bad aftertaste if you want to do anything more substantial. Rather use standard relational tables.

Compound fields are a violation of proper database design. Don't go there. Concatenate in a query or on a form or report if you want, but not in a table.

Once you fix these issues, hopefully the answer becomes apparent, or if not post the Relationships diagram and some more background info about the purpose of the tables.

Was this answer helpful?

1 person found this answer helpful.
0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more