Lookup field (1 Viewer)

[ekje]

Hi,

It's about the error message after using the Lookup Wizard (-> third attachment).
The idea was to make the field "Agreement_Number" in the table "tblProjects_payments_from_2022-10-01" (-> second attachment)
a field which gets the value (Agreement Number) from the table "tblProjects_Number_Name_use_from_2022-10-01" from its field "Agreement_Number.
(-> first attachment).
After using Lookup Wizard I got the error message (-> third attachment).
Why does the error message contain wrong information about a valid name: why does the error message show a invalid name which is a concatenation
of the two tables (-> attachments 1+2)?
Please reply me as soon as possible.
Thank you very much for your quick reply!

 

[Gasman]

I am on my phone, so not downloading attachments that could just as easy be pictures.

 

[mike60smart]

Hi
You should not be using the Lookup Wizard in a table.
Google "The Evils of Lookup Fields in Access Tables"
The field "Agreement_Number" should be a Number Data Type
Then when you create your Data Input Form to enter data you would create a Combobox to Lookup the List
from a separate table.

Also your ProjectNumber table is created wrong. You should have a PK named ProjectID and set this field as an Autonumber.
You should not be using Agreement_Number as a Primary Key.

 

[ekje]

Hi,

Than you very much for you reply.
The field "Agreement Number" must be a text type because agreement numbers contain numbers, letters and hyphens.
The agreement number is unique and is at the same time the unique project number/ID.
Why Agreement Number could not be used as Primary Key?
By the way, maybe it is not a good Primary Key, because Agreement Number has numbers, letters an d hyphens?
Please reply to me as soon as possible.
Thank you for your quick reply!

 

[mike60smart]

Hi
You are OKhaving a field named "Agreement_Number" which is a Text Data Type but it should not be used as the PK.
I don't know your process but I will assume that you are using the AgreementNumber to identify an Agreement with
someone/something?

Most developers use a PK Autonumber to identify a unique record in a table, and they would normally not show
this on the Data Input Form.

 

[tvanstiphout]

It is not a good field for PK because it is untidy (longer than a PK needs to be) and contains human-readable information (which may cause changes in the future if the next manager wants a new format).
When used as a FK in other tables (e.g. StaffMembersOnProjects) it requires cascade updates.
All in all these are good reasons to use an Autonumber: small, no human information. Of course you will keep a unique index on AgreementNumber. Note how I dropped the space in that field: spaces and other funny characters may cause notational difficulties.

 

[Mike Krailo]

Why Agreement Number could not be used as Primary Key?

It can be used as a primary key, but it is not recommended as best practice is to use an auto number field. You can still index your Agreement Number field to retain the uniqueness of the data. Its much easier to identify a pure Number in code than text data.

Here are the screen shots that the OP attached so Gasman can see it.

Those table names are not very good at all. Try to stick to shorter names that contain only alphanumerics and underscores only. No spaces or special characters. I don't know what the length of the table name limit is exactly but if you pick shorter names for tables, it won't ever be an issue.

 

[Pat Hartman]

The human friendly "key" does need a unique index though to enforce the business rule of uniqueness.

 

[ekje]

Hi
You are OKhaving a field named "Agreement_Number" which is a Text Data Type but it should not be used as the PK.
I don't know your process but I will assume that you are using the AgreementNumber to identify an Agreement with
someone/something?

Most developers use a PK Autonumber to identify a unique record in a table, and they would normally not show
this on the Data Input Form.

Hi,

Thank you for your reply!

The agreement number is created by European Union authorities.
The projects are funded by EU.
Private businesses are implementing these projects in practise.
Just a check question: while all the primary keys in these tables should be Autonumbers, I must change the indexed property
of the field "Agreement Number" into "Yes/No Duplicates " to ensure that all the Agreement Numbers are entered only once ?
Is this enough to ensure the uniqueness?
Thank you for your reply!

 

[Pat Hartman]

Yes, that is what I said

 

[Pat Hartman]

It is especially important to never use as a PK a value that YOU don't control. If the issuing body decides to change the code from 8 to 10 characters, no problem, just increase the size of the field in the main table. However, if you have used the field as a PK, then you need to change ALL the tables where the field is used as a FK also.

 

[ekje]

It can be used as a primary key, but it is not recommended as best practice is to use an auto number field. You can still index your Agreement Number field to retain the uniqueness of the data. Its much easier to identify a pure Number in code than text data.

Here are the screen shots that the OP attached so Gasman can see it.
View attachment 108579
Those table names are not very good at all. Try to stick to shorter names that contain only alphanumerics and underscores only. No spaces or special characters. I don't know what the length of the table name limit is exactly but if you pick shorter names for tables, it won't ever be an issue.

Hi,

Thank you for your reply.
Yes, your are right about the table names and autonumber fields.

 

[ekje]

It is especially important to never use as a PK a value that YOU don't control. If the issuing body decides to change the code from 8 to 10 characters, no problem, just increase the size of the field in the main table. However, if you have used the field as a PK, then you need to change ALL the tables where the field is used as a FK also.

Hi,

Thank you for your information about how to create a primary key.

 

[Pat Hartman]

You're welcome