Mysterious Prompt to Insert Sundatasheet on Table with Many-to-Many Relationship

[applehugger]

In the attached database, I have three tables. The "Buildings" and "Departments" tables have a many-to-many relationship with the "DeptBldJunction" table functioning as the junction table.

I am quite the beginner with Access, so I am sure I am missing something simple. When I created these relationships yesterday, clicking the + next to the Building (in the "Buildings" table) or Department (in the "Departments" table) would show the associated department(s) and building(s), respectively.

Today, when I opened the database, when I click on the +, I get a popup box that prompts be to insert a subdatasheet. I checked and the subdatasheet property on all three tables is set to auto. Nothing changed between yesterday and today, so I am completely at a loss as to why this is happening.

Any help is much appreciated!



For Forum.accdb

[Micron]

Your junction table has no primary key. It does have a composite index though. This means that without a pk, there is no way to uniquely identify a record, so you're getting the prompt. Your tables are not really properly constructed. You should have autonumber pk's rather than use real data as keys. Then the related field in the other table needs to be long data type, not text.

EDIT - forgot to ask why you have put Departments in the relationship twice?
You probably should review db normalization?

EDIT 2 - I for one don't even want to see sub datasheets. Users should not be using tables to view information so they are not really of much use at all. Last, if tables are only for lookups in forms then they don't even need to be inside of the relationships. No harm though, I suppose.

[applehugger]

I did not put departments in the relationship twice. Access keeps adding it, but I don't know why.
I apologize but I just need a bit of clarification on a few points as I try to work through this:

Should I have auto number set for the Departments, Buildings, and Junction table, or does only the Junction table need a primary key? I am working through a college Access course right now, and the chapter on relationships stated to use a composite key for the junction table. I can't say I know exactly why.

I am not sure which "other table" needs to have a long data type instead of text. Do you mean the junction table, or the Departments and Buildings table?

I apologize for all of the questions. I am quite new to database design. I'm much more proficient with Excel, but cannot use it for this purpose.

[Micron]

Well, I guess you have to do what you're told. If you research this (and I suppose other forums) you will find a lot of dissenters when it comes to composite keys vs composite indexes, and this isn't the only aspect that we find is being incorrectly taught.

This is how I understand the difference. If I'm wrong, I'm sure to be called out on this and that's ok.
If you are familiar with the concept of a phone directory, consider how it relates to a db and its keys but I'll only consider first and last names, not initials or addresses for duplicate names. In the phone directory you can have
smith, allan
smith, john
smith, joe
etc. Now imagine there are thousands of smiths. You scan every one as quick as you can, trying to skip to the list portion that contains the first name you want. Not too bad. However, the db must evaluate every pair of values (each column) in every record to find all smiths and then the one that is also "john". It is made worse if the primary value (smith) is in the second field of the composite index. Also, they're less efficient in joins and AFAIK, you could only join on fields with the same values, which can lead to having data repeated. You can only find smith in another table field if that field also contains "smith" whereas many designers would store the pk (autonumber) value as the foreign key. That means less space is taken up by avoiding repeating text (or other space consuming data) instead of a simple long integer. Last twist I have on the issue is, unless you create relationships and enforce referential integrity, when Janet Smith gets married and becomes Janet Brown you have a problem. Not so if Janet Smith's uniqueness was created by an autonumber primary key.

In summary, I suppose you should do as the course material or instructor says and you should be fine for that scope of work, but I would usually avoid composite indexes and use autonumber pk fields. If I need multiple fields to guarantee uniqueness over those fields I'd use a composite index instead.

Give me a few minutes to create pics of suggestions (for the real world, not academia).

[applehugger]

Thank you for your help and your detailed reply. It is so strange that this database worked perfectly yesterday without the subdatasheet prompt, but stopped working overnight. I really just need a way to show the relationship between departments and buildings, which will become important for queries later when a user wants to see all contacts within a department or all contacts within a building. The database needs to know everyone who works in the building including all departments and everyone who works in a department including all buildings where they are stationed.

I'm beginning to think that your first reply may have identified that I don't need this relationship at all as long as each contact has their department and building defined. I guess I'm just stuck on solving the problem because it worked yesterday and was a nice way to show all departments in each building and vice-versa.

[Micron]

This is what the data would look like with that design

BldngDeptID	Department	Building
1	1	12
2	2	6
3	2	12
4	3	3
5	3	11
6	4	6
7	4	12
8	5	12
9	6	12
10	7	2
11	7	4
12	7	5
13	7	7
14	7	8
15	7	9

Note that the junction table as posted (not in its entirety) really doesn't make a whole lot of sense unless you have other fields in it that provide data to relate to. Otherwise it's just a table of possible combinations. Also as mentioned, tblDepts and tblBuildings seem to be intended to provide lookups for form/report data and inputs. They don't need to be part of the relationships.

Let the debate begin!

[applehugger]

That's what I was afraid of - a bunch of numbers make no sense as the user needs to look up the building or department to which that number corresponds. However, at least now I better understand how a many-to-many relationship works. It's so bizarre that it worked for me yesterday.

I think in my case, I don't need the relationship at all, as you first suggested. Queries can simply look at the user's assigned department and building and provide data accordingly. Thank you so much, @micron, for helping me to better understand database design and elucidating how this type of relationship works! I really appreciate it :-)

[Welshgasman]

That's what I was afraid of - a bunch of numbers make no sense as the user needs to look up the building or department to which that number corresponds.

That is just the data behind the scenes. The user would see the relevant description from the relevant table.
If you (God forbid) used table lookups, you would see those descriptions then and not the real data.

Edit. here is my Links table and the form that creates/edits the records.

[Micron]

Yes, that is how the data is related, not how it is presented. You really should research db normalization else you will only struggle more. Surely they covered this topic??
Here's a start
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

[Micron]

If you said "show me all of the buildings for the Community Residential department" I would show you this (minus the dept id, but I would use it for the criteria). How does the 7 get into the query? One way would be from your form dept combo, which shows the text name but stores the dept id and is hidden from view because it means nothing to the user.

DepartmentID	Department	Building
7	Community Residential	Bellman
7	Community Residential	Carbondale
7	Community Residential	Dalton
7	Community Residential	Dickson City
7	Community Residential	Elmhurst
7	Community Residential	Gouldsboro
7	Community Residential	North Everett
7	Community Residential	Olyphant
7	Community Residential	River Street
7	Community Residential	South Irving
7	Community Residential	Taylor

EDIT - that is from the data format I posted and not your original format.

[applehugger]

Yes, that is how the data is related, not how it is presented. You really should research db normalization else you will only struggle more. Surely they covered this topic??
Here's a start
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

These are very useful links; thank you so much for sharing this information! Reading through it now.