Solved Problem with creation of Form (Database of User and their equipment) (1 Viewer)

Da_Panda

New member
Local time
Today, 11:51
Joined
May 29, 2020
Messages
3
Hello,


creating a database where one would be able to look a user (using an username) and from there one could see the equipment that has been assigned to them (like monitor, pc, etc.).

But am having some trouble with the Form.

Currently I have created 4 Tables: Users, Monitors, PCs, Extra (eg. mouse, keyboards). Have then defined the Relationship through Username in the Tables (each table has a username field). The Relationship is then set up for '1-to-many' with username in Users being Primary key and short-text, and the rest just short-text.

I can then create a Form with data from 2 tables (eg. Users and PCs). And then have PCs as an underform to Users. From there and can search for any username in Users table and if they are also written in the Username field in under PCs it will show which PC are asigned to them. And if none are asigned it will leave it blank.

But I would like to have 1 Form with then multiple underforms (PCs, monitor, etc). Where it would then for each user display if they have a that equipment. And if not, leave it blank.
But when I tried to add the monitor to the Form as an underform. The it would continue to show data, even if the user didn't have any.
Also tried to create a Form, with adding data from all the tables. But then it would only show the users who where written into all the tables.

Am I missing something in the creation of the Form? Should I maybe make use of Queries? Or is there maybe something with the Relationship of the tables?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:51
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF!

I would suggest combining all equipment into one table and then use a junction table to assign the equipment to the users. You will then be able to easily find/view which equipment were assigned to a user.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:51
Joined
May 7, 2009
Messages
19,169
sample table and form.
 

Attachments

  • UserEquipments.zip
    39.2 KB · Views: 236

Da_Panda

New member
Local time
Today, 11:51
Joined
May 29, 2020
Messages
3
Thank a lot! @arnelgp and @theDBguy.
I got to create a working idea out from both of your idea.
Just decided to go with 2 Tables. One with users and their information, and one for all equipment. And then created a 'one-to-many' relationship (also found out my previously 'one-to-many' relationship was setup wrong 😅).
And then from there. when I write in either a new user, new product for user or change product information in, it is also changed across the tables.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:51
Joined
Oct 29, 2018
Messages
21,357
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom