Relationships without primary key

[Yuliaresh]

Hello, dear community members!


I am very new to Access and actively learning via Youtube Tutorials, online master classes etc.

Have a question, answer on each I haven't found.

All sources strongly emphasize that, in order to create relationships between tables, each table should have Primary key defined.
I am, however, working with tables, where I cannot define Primary Key. There is no column with unique field, i.e. all data in columns can be seen several times in the table. If I manually create AutoNumber field, it will not make sense. Each of the tables has max 3 similar fields.

So, no Primary key, however, when I am designing a query, I can make several (between these common fields) connections between tables. And it seems to be working.
Question: Is it possible to make connections between tables without primary key? What are the consequences? Will the data in the result of the query be unreliable? What would be optimal way in my situation?

Thank you in advance for your time and help!

[rpeare]

1. Yes it is possible to link tables without a primary key
2. The consequences are that any field that is modified by any user in any of your linking fields will result in orphaned records (for instance if you have a customer saved as 'customer X' and in your customer orders table you have 'customer X' 3 times and you change the text value in your customers table from 'customer X' to 'customer X1' all the records in the customer orders table will have valid reference to the customer table.
3. Yes, they will, for the same reason as #2 if 'Customer A' becomes 'Customer B' and 'Customer B' becomes 'Customer A' all the data that is linked by the customer name will now be transposed.
4. No good way to tell you without an example of the data, where you are getting it from (is it a text file or spreadsheet you are importing rather than a set of tables you have control over).

[JoeM]

If I manually create AutoNumber field, it will not make sense.

Still not a bad idea to put one on all your tables, even if you don't think you have an immediate need for it. It allows the database to distinguish the records.
That can be helpful in doing things like Aggregate Queries, where you want to return the First or Last record.

[orange]

If none of your tables has a unique feature, you may have missed some critical property of the entity/table.
Can you please tell us about these tables where no field or combination of fields is unique? I'd like to hear more.
A primary key is basic to relational database.

[Yuliaresh]

4. No good way to tell you without an example of the data, where you are getting it from (is it a text file or spreadsheet you are importing rather than a set of tables you have control over).

Thank you for your reply, rpeare, the files I am working with are imported from excel, I do not have control over them, as they represent a report from ERP system.

Unfortunately, I cannot publish tables directly here, as information is private, but I will post an example later on.

Thank you!

[June7]

Whether single field is primary key or multiple fields compose a compound primary key, relational database requires keys.

[ssanfu]

Here is a little info on primary keys and autonumbers.......

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp


Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm

[Yuliaresh]

4. No good way to tell you without an example of the data, where you are getting it from (is it a text file or spreadsheet you are importing rather than a set of tables you have control over).

Hello! Please see attached Excel file with different tabs - representing different files.
EXAMPLE.zip

If not clear, please let me know.

May be, I should come back to manipulation with MS Excel, rather then step in unfamiliar territory of MS Access:-D

Thank you in advance!

[rpeare]

Back to your basic question.

In this case you do not have a lot of options other than linking by a non PK field. These are external sources of data over which you have no control. Further, I assume that your database will only be used to look up information, it is not being used for data entry. I would just make sure that you lock and/or disable all fields on any forms you create with this data or you will have to re-import the information. It looks to me like you get periodic updates and you are going to be overwriting data which makes trying to maintain a PK/FK very cumbersome.

[Yuliaresh]

rpeare,
thank you very much for your reply!
Yes, you are right, there won't be any data entry in this case. And yes again, Access DB should be linked to the source files, because they are getting update on a weekly basis. Is there any risk that I might lose some information when create forms and connect them with queries?

Thank you!

[rpeare]

If you are going to link your data to the excel files make sure you have copies of your original excel files just in case someone accidentally changes a piece of data. You should not lose any data if you make sure that all your fields on your forms are locked/disabled (enabled property set to NO)

[June7]

Cannot edit spreadsheets from Access through a link.

[rpeare]

hah, shows how often I work with spreadsheets!