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
> Help To Update Relationship Between To Tables, Handing Out It Equipment Connected To A Profile., Access 2016    
 
   
behedwin
post Nov 17 2017, 02:17 AM
Post#1



Posts: 15
Joined: 29-October 17



Hi
I hope i can find someone that can help me solve this problem.

I have a access project running.
You can download it here:<<Removed Link to External File>>
In this file i have these tables
Profile_Table - stores names, adress etc.
ProfileLog_Table - store info about when a staff member was on vacation, dates etc.
IT_Table - store names of computers, serial numbers etc.
ITLog_Table - store dates and actions done on computers, like repairs.
(then there are some others that are not in use yet).

To this i have some querys and then some forms.
I have a form where i select profile to open : selectProfile_Form
i have a form to display the profile: profile_form
i have a form to select IT equipment: selectIT_form
i have a form to display the IT equipment: IT_Form
then there are a few others that should not matter right now...

What i want to do now is to create a relationship between a IT Equipment and a profile
But i dont know how really....

I when i go to a profile, i want to see what IT equipment have been assigned to that profile.
When i got to a IT equipment i want to see what profile have been assigned to that IT equipment.

I have created a relationship between profile_table and IT_table
where profile_id (primary key in profile table) is connected to profile_id_sk (secondary key) in it_table.
then i can view what profiles are connected to each IT equipment (it_id).

in the form IT equipment i created a listbox displaying a query and filtering on the IT_ID ([Forms]![IT_Form]![IT_ID])
Then i get a nice result, in the listbox in IT_form. It displays the user that is connected to that it equipment.

However i want now to be able to change this information. For example, go into the it equipment and change the owner of that it equipment.
How do i do this?
I guess i need to tell access to change the Profile_ID_SK value in IT_Table
But i want the user to pick a name from a combobox.

Also in the profile_table i can view the same thing. But instead the listbox in profile_form is showing the it equipment that is connected to that profile.
Here i also want to be able to change the it_equipment connected to that profile.
How do i do this?
Go to the top of the page
 
projecttoday
post Nov 17 2017, 02:55 AM
Post#2


UtterAccess VIP
Posts: 8,683
Joined: 10-February 04
From: South Charleston, WV


Is the profile about the computer model or is there one for each and every computer? Or both?

Can you give some example of what you mean?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
behedwin
post Nov 17 2017, 03:08 AM
Post#3



Posts: 15
Joined: 29-October 17



Sure, ill try!

Profile Table contains this:

Profile_ID, FirstName, LastName, Adress and several other columns.
This is the profiles of the staff working at my company. So data about a person.

IT Table contains this:

IT_ID, Profile_id_sk (secondary key to profile_id in profile_table), computername, computer model, computer serialnumber.
This is the table where i store data about each computer device.

I have a form so the user can view the data in each table.
User can look at only computers and user can look at only the profile (staff).

Then i have a listbox in profile_form where i display what computer is connected to that profile (staff member).
And in the it_form i have a listbox displaying what profile (staff member) is connected to that computer.

My problem now is that i want to be able to update this information.
For example:
Staff Member: JOHAN have ID = 1 in table profile_table
Johan have a computer named Computer44 that that is listed in the IT_Table. So connect them johans id number 1 is also stored in the secondary key (profile_id_sk) in it_table.
This works great.

So now when i want to change this... i might want to change so Johan is no longer the owner of computer44.
Maybe a staffmember (profile) with ID 2 needs computer44... how to update this in the form... i could go to the IT_table and just change the value in profile_id_sk and change it to number 2 and be done with it. But i want the user to see a list of available profiles, select one and that will update the ID in the profile_id_sk column.


Hope this makes sense laugh.gif
Go to the top of the page
 
projecttoday
post Nov 17 2017, 03:25 AM
Post#4


UtterAccess VIP
Posts: 8,683
Joined: 10-February 04
From: South Charleston, WV


If I understand you correctly then maybe a combo box on the Profile table.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
behedwin
post Nov 17 2017, 03:52 AM
Post#5



Posts: 15
Joined: 29-October 17



Ok, that could be a thing.... but not sure i know how to do it.

I did this

1. Create a new column in Profile_Table called ITunit
2. Make that column to a combobox / valuelist using the access wizard basing it off two columns from IT_Table
3. I open the Profile_Table and i can click the new column and select from all the values from IT_Table

So far it seems to work... i can select that xxx computer belongs to yyy profile

But now to implement this into Profile_Form.

I create a new combobox.
I then select the column in properties and make the combobox a valuelist.
But when i try it and open the profile_form and opens the new combobox i just see SQL in it, "SELECT [IT_Table].[Enhetsnamn]"
Go to the top of the page
 
projecttoday
post Nov 17 2017, 04:13 AM
Post#6


UtterAccess VIP
Posts: 8,683
Joined: 10-February 04
From: South Charleston, WV


I think that's a bug in Access. It's supposed to be a fieldname. Just change it.

You should not have to change the table. The purpose of the combo box is to insert the profile id into the table. Why a new field?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
behedwin
post Nov 17 2017, 04:28 AM
Post#7



Posts: 15
Joined: 29-October 17



Do not understand what you mean now

What do you mean by "why a new field?" should i not create anything new in the table?
You said "maybe a combobox on the profile_table"
I thought id create a new combobox in the profile table??

what is a bug in access and what should i change?


atm i feel all of this goes just above my head....

i want to be able to track from two places
profile_form: what computer is owned by current open profile form
it_form: what profile is connected to current open it_form.

to make this i assume i need to have a relationship between profile_form and it_form where profile_id in profile_table is connected to profile_id_sk in it_table.
this i have.

but the question is how to set things up in the form.
I can view everything in tables where it works just fine.
But i want it to be editable in the form aswell wich is the hard part i assume...
Go to the top of the page
 
projecttoday
post Nov 17 2017, 05:01 AM
Post#8


UtterAccess VIP
Posts: 8,683
Joined: 10-February 04
From: South Charleston, WV


The profile id is in the table. The combo box is on the form. The combo box allows you to enter any profile id you select into the I.T. table. You do not need to add a field to the I.T. table.

So each computer in the I.T. table is related to a profile because there is a profile id field in the I.T. table.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
behedwin
post Nov 17 2017, 05:20 AM
Post#9



Posts: 15
Joined: 29-October 17



Yes
But how do i display this in the form?
How do i setup a combobox in the profile form where i can select a computer for that profile
How do i setup a combobox in the IT form where i can select a owner?
Go to the top of the page
 
projecttoday
post Nov 17 2017, 05:23 AM
Post#10


UtterAccess VIP
Posts: 8,683
Joined: 10-February 04
From: South Charleston, WV


You do not put the combo box on the profile form. The relationship only goes 1 direction. To put the combo box on the i.t. form, just go into design view and drop a combo box on the form. A wizard will come up. Follow the prompts.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
orange999
post Nov 17 2017, 08:04 AM
Post#11



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


Perhaps I'm misunderstanding, but it seems you are dealing with this set up.

tblPeople ----contains People/profile info
tblITEquip --- contains info about each piece of IT equip

and you may be missing

tblPeopleAssignedEquip ---- contains PeopleID and ITEquipID (and other relevant info assignedDate, ...) This is a junction/linking table with records for each assignment of Equip to Person. It would provide history of Equip/People assignments.

Please see this free data model to see if it and the relationships apply.

Again, I may have misunderstood the posts so far.

--------------------
Good luck with your project!
Go to the top of the page
 
projecttoday
post Nov 17 2017, 08:24 AM
Post#12


UtterAccess VIP
Posts: 8,683
Joined: 10-February 04
From: South Charleston, WV


I think there is an equipment file and a person file only. behedwin please confirm this. So we want a person id in the equipment file.

In my own app (which you can download by clicking on "My Company's Website") I have equipment and models files. So you have a model id in the equipment table instead of a person id. The person's name is simply typed in in a field.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
behedwin
post Nov 17 2017, 09:10 AM
Post#13



Posts: 15
Joined: 29-October 17



Yes. I only have two tables

One containing people information (name, adress, etc) witch is controlled by Profile_ID. This table is called: Profile_Table
And another table containing IT equipment information (computer name, model, serialnumber) witch is controlled by IT_ID. This table is called: IT_Table.

So if i understand your suggestion here.
It is to create a Third table. That contain Profile_ID (secondary key), IT_ID (secondary key) and for example Hand Out Date and a Primary Key (AssignedIT_ID)

It sounds good.

But what would next step be.
How to make use of this in the Profile_Form and in the IT_Form

I want to be able to edit the assigned equipment from both sides.
Open the Record of a Equipment and assign that equipment to a person (profile.
Open the Record of a Person (profile) and assign that person to a equipment.
Go to the top of the page
 
projecttoday
post Nov 17 2017, 09:16 AM
Post#14


UtterAccess VIP
Posts: 8,683
Joined: 10-February 04
From: South Charleston, WV


You would only create the third table if a piece of equipment can be assigned to more than 1 person at a time. Otherwise, keep it the same.

I'll be gone for awhile.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
GroverParkGeorge
post Nov 17 2017, 09:18 AM
Post#15


UA Admin
Posts: 31,235
Joined: 20-June 02
From: Newcastle, WA


Have you studied the materials on Relational Table Design here in our Newcomers Reading List? If not, this would be a good time to do that.
There are two possible scenarios here and we really need to be clear about what your situation requires.

You have people.

You have equipment.

You want to assign each piece of equipment to people who will use it.

I assume that each person can have one or more pieces of equipment assigned to them at any given point in time.

I further assume that each piece of equipment can be assigned to only one person at a time, or it can be assigned to two or more people at the same time.

In other words, Person A can be assigned a monitor, a printer, and a PC. Moreover, the monitor is assigned exclusively to Person A, as are the PC and the printer. That's one scenario.

In the other scenario, each monitor is assigned exclusively to one person, but a printer is shared among two or more people.

To handle this sort of relationship in a relational database, you need either TWO or THREE tables. If the assignments are all exclusive--one piece of equipment to one and only one person, you need two tables. If the assignments are shared--each piece of equipment is assigned to one, or to more than one, person, you need three tables.

The first table is the Person table. It is the table where relevant details about each person are stored: First Name, Last Name, and so on. The exact fields in this table depend on what information you must track for each person. For example, if Date of Birth is relevant to this database, you'll need a field for that, but if it's not relevant, you don't have that field.

The second table is the Equipment table. It is the table where relevant details about each piece of equipment are stored: Type (printer, PC, etc.), manufacturer, model, Serial Number, and so on. Again, the exact fields depend on the information you must track for equipment. Color, for example, may or may not be useful. This can get to be pretty complicated, as some kinds of equipment have different attributes than others. RAM applies only to the PC, not the monitor. So, this is going to be its own design problem, but for this part of the discussion, we'll just leave it at that.

If the assignment is exclusive, wherein only one person can be assigned for a piece of equipment, you need a field in the Equipment table identifying that person. This is called a Foreign Key. It is the Primary Key from the Person table that tells you which person has that equipment. This is where a combo box on a form--never in a table, please--is used.

Now, the third table is the key to getting your assignments of equipment to people right in the case of shared equipment. This table allows you to assign each piece of equipment to two or more people, and to assign one or more pieces of equipment to each person.

It contains, at a minimum, the Foreign Key field from the Person table and the related Foreign Key field from the Equipment table. It will normally contain other fields, such as a Primary Key or DateAssigned fields. This part is flexible and depends somewhat on your business rules.

It will require a subform to handle this properly.

So, before we get too far down the road of form design, let's make sure we have a good picture of the relationships needed and the way your business operates.

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 09:57 AM