UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Multiple Many-to-many Relationships, Access 2016    
 
   
duffyt60
post Jun 19 2019, 02:11 PM
Post#1



Posts: 15
Joined: 19-June 19



I have created tables for the following and add junction tables.

Users
User_ID (Pkey)
Full_Name
User_Name

Departments
Dept_ID (Pkey)
Department_Name

Applications
App_ID (Pkey)
Application_Name

App_Dates
Date_ID (Pkey)
Access_Date
Release_Date

JUNCTION TABLES

UserDepartment
User_ID
Dept_ID

UserApplication
User_ID
App_ID

UserAppDate
User_ID
Date_ID

Trying to create a query and/or form to add or select records for Users who could be assigned to more than one department in their employment time and who would have access to various applications. So my Department, App_Date, and Applications tables are all many-to-many.
Go to the top of the page
 
tina t
post Jun 19 2019, 02:17 PM
Post#2



Posts: 5,982
Joined: 11-November 10
From: SoCal, USA


what's the purpose of the following table?

App_Dates
Date_ID (Pkey)
Access_Date
Release_Date

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
nvogel
post Jun 19 2019, 02:18 PM
Post#3



Posts: 966
Joined: 26-January 14
From: London, UK


Hi, welcome to UA!

Do you have a specific question? It's not quite clear to me from what you have described.
Go to the top of the page
 
orange999
post Jun 19 2019, 02:29 PM
Post#4



Posts: 1,926
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Further to the responses by others, can you give us a plain English description of the "business" this proposed database is intended to support?
A clear description of the business/purpose/requirements of the database would hlp put your information into context.

--------------------
Good luck with your project!
Go to the top of the page
 
duffyt60
post Jun 19 2019, 07:40 PM
Post#5



Posts: 15
Joined: 19-June 19



The AppDate table is to track when a user gains access to an application and when they lose access to the application. It is possible for a user to have this happen more than once.

So a User can have multiple applications. A user can have multiple departments. A user can have multiple access and release dates for an application. A department can have multiple users. A department can have multiple applications.

When I try to use the junction tables and create a query or a form to either see data or update data for a user, I get a blank screen.
Go to the top of the page
 
tina t
post Jun 20 2019, 04:25 PM
Post#6



Posts: 5,982
Joined: 11-November 10
From: SoCal, USA


QUOTE
When I try to use the junction tables and...

well, let's try to get the tables and relationships right, first.

QUOTE
The AppDate table is to track when a user gains access to an application and when they lose access to the application. It is possible for a user to have this happen more than once.

ok, got it. the tables App_Dates and UserAppDate are not useful. go ahead and get rid of those. the real-world situation is that at some point a specific user may be granted access to a specific app. and at some point that specific assignment may be revoked (released). so, change table UserApplication as follows:

UserApplication
UserAppID (autonumber, Pkey)
User_ID
App_ID
Access_Date
Release_Date

each instance of a user being granted access to an app is one record, with the access date included. when the user's access to that app is revoked, then the release date is entered. if a specific user has use of a specific app granted/revoked 10 times (or 100, or...) then each instance of the access being granted is a new record.

QUOTE
A department can have multiple applications.

i don't see that relationship supported in the existing tables. is it important that it be tracked, for your business process? if so, what's the real-world scenario?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
duffyt60
post Jun 21 2019, 11:48 AM
Post#7



Posts: 15
Joined: 19-June 19



The reason for the many-to-many relationship for department and applications is that my boss requests a report on what applications are associated with what department.

Would I just create a junction table for DeptApplication and add the Dept_ID and App_ID fields?
Go to the top of the page
 
MadPiet
post Jun 21 2019, 12:09 PM
Post#8



Posts: 3,171
Joined: 27-February 09



Sounds right.

CREATE TABLE DeptApplications (
ApplicationID INT,
DepartmentID INT
CONSTRAINT pkDeptApps PRIMARY KEY (ApplicationID, DepartmentID)
FOREIGN KEY ApplicationID REFERENCES Application(ApplicationID),
FOREIGN KEY DepartmentID REFERENCES Department(DepartmentID)
);

Because it's a primary key, both ApplicationID and DepartmentID are required.

The foreign keys are pointing back to their related table primary keys.
Go to the top of the page
 
duffyt60
post Jun 22 2019, 01:24 PM
Post#9



Posts: 15
Joined: 19-June 19



So I now have the tables setup as provided by Tina T and MadPiet.

However, since Applications is field out with data, why won't the application name show in the sub-form? I get the ID number instead. I even tried to set the properties for that entry to Column 2 and it still will not show the name of the application. do I need to do something in the main form for the sub-form relation that corrects this?

Both Department and Applications are pre-filled table fields so the correct information can be selected from Drop-Down boxes on the form.

Thank you again everyone!
Go to the top of the page
 
moke123
post Jun 22 2019, 09:06 PM
Post#10



Posts: 1,361
Joined: 26-December 12
From: Berkshire Mtns.


QUOTE
I even tried to set the properties for that entry to Column 2

A combo is zero based so if the name is the 2nd column its column(1) and be sure to check the column count and column widths in the properties.
With 2 columns in your rowsource you would set the widths to 0,1. The ID would be hidden and the name would show.
This post has been edited by moke123: Jun 22 2019, 09:11 PM
Go to the top of the page
 
tina t
post Jun 23 2019, 05:34 PM
Post#11



Posts: 5,982
Joined: 11-November 10
From: SoCal, USA


QUOTE
Both Department and Applications are pre-filled table fields so the correct information can be selected from Drop-Down boxes on the form

sounds like you're using Lookup fields in one or more of your tables. if so, that's a bad idea. Lookup fields cause numerous problems, and most experienced developers avoid them like the plague. note: combobox controls in forms are fine, and present no problems at all.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
duffyt60
post Jun 24 2019, 09:16 AM
Post#12



Posts: 15
Joined: 19-June 19



Thank you for all your help.

I am attaching a sample of my database as I still can't get forms to work correctly.
Attached File(s)
Attached File  Test_Apps.zip ( 62.66K )Number of downloads: 1
 
Go to the top of the page
 
duffyt60
post Jun 26 2019, 10:57 AM
Post#13



Posts: 15
Joined: 19-June 19



I have corrected part of my issue by creating a new Junction table with User_ID, App_ID, Dept_ID, Access_Date, and Release_Date contained in the table. This appears to work until I create a form.

When adding the sub-form and making the Control Source the Junction Table, I can't get the Application and Department drop-down boxes to display the names rather than the ID number. If I try to use a name from the main tables (Applications or Departments) I get an error that the data type is not correct.

Attached is a new sample for review.

Any help is appreciated.
Attached File(s)
Attached File  NewTest.zip ( 65.61K )Number of downloads: 4
 
Go to the top of the page
 
moke123
post Jun 27 2019, 06:21 AM
Post#14



Posts: 1,361
Joined: 26-December 12
From: Berkshire Mtns.


change the bound column of the combos to 1
Go to the top of the page
 
duffyt60
post Jun 27 2019, 07:23 AM
Post#15



Posts: 15
Joined: 19-June 19



First, THANK YOU to everyone for your help!

Second, I finally got my form and sub-form to work correctly. I ended up creating a new Junction Table that contains User_ID, App_ID, and Dept_ID and I made them PKEYs to their connecting table PKEYs. I then added the Access and Release Date fields to this new Junction Table. I then created two queries. One for Applications and another for Departments. Once that was done, I created a form and sub-form. I first created the sub-form for the fields I wanted. I liked the Control Source to the new Junction Table and then set the Row Source to the new queries. In the queries, I added both the name and ID fields. I then bound the combo-boxes I created to column 2 of the queries. I then created the main form using the User table. Then I drug the junction table form into the main form to create my sub-form. Everything works exactly as I expected.

Relationships are:
User table User_ID to Junction Table User_ID
Applications table App_id to Junction Table App_ID
Department table Dept_id to Junction Table Dept_ID

Again, thank you everyone!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th July 2019 - 05:06 AM