Forms with multiple subforms and/or Queries

[temo]

Hello
I’m fairly new to access, and have run into a problem with designing a small app. I have 3 tables that I have linked from one table with field: cusCustomersID
tblCustomers
cusCustomersID
cusCustomersName
cusCustomersAddress
cusCustomersPostalCode
cusCustomersCity
cusCustomersContact
cusCustomersPhone
cusCustomersE-mail

tblEmployees
empEmployeesID
empCustomersID
empEmployeesFirstname
empEmployeesLastname
empEmployeesAddress
empEmployeesPostalCode
empEmployeesCity
empEmployeesPhone
empEmployeesE-mail
empEmployeesTitle

tblConstructionSites
conConstructionSitesID
conCustomersID
conConstructionSitesAddress
conConstructionSitesPostalcode
conConstructionSitesCity
conConstructionSitesPhone
conConstructionSitesE-mail

I now want to show:
Customers, then Employees, then Construction sites, this for each Customers and each employee. One customer can have many employees and many sites.

I probably could link: customers 1-M employees 1-M sites, but I don’t want to do that because I think it’s better to let customers have the ownership to the employee and the sites.

Any help on how to this would be appreciated.
Yours Terje Moe

[TheSmileyCoder]

You didn't exactly ask a question so its kinda hard to answer, but im guessing its the many to many relation of the Employees to Sites that got you stumped. Here is the relations as I see them:

Customers 1-M Sites
Customers 1-M Employes
Employes M-M Sites

To make a M-M relation you need to make a seperate table. I prefix my relationship tables with rel so the table name would be (for me) :
relTbl_SitesEmp
This table would then contain 2 fields: conConstructionSitesID, empEmployeesID

[temo]

@TheSmileyOne
Hello

Thank you for this!

I will try this out, but one Q, you say two fields, do you meen two fields pluss relTbl_SitesEmpID?

Yours Terje Moe

[TheSmileyCoder]

You dont need a unique ID for this table. You can set the primary Key to be the combination of the 2 fields, and that combination will always be unique (Since a worker can only work once on the same site).

In case you don't know how:
If you select both fields in design view, and hit the primary Key button, Access will make the combination of the 2 fields the primary key.

And welcome to Bytes

[temo]

Ok

I'm not so fam. with makeing a linking table, but I think I can do this.

I see you are from Denmark, I'm from Norway, so I guess we both are stranded for some days, but at least we can drive!

Thank you again.

[TheSmileyCoder]

Well I wasn't planning on going anywhere myself, but a friend of mine was going to visit from Greenland, but had to cancel :(

The linking table can take a bit getting used to the first few times, but its quite powerfull (and the only way you can make Many-Many relations)

[temo]

Hello again.

I didn’t quite get this to work.

First of all I don’t seem able to make a many too many rel. As the submitted photo “Relasjoner” shows, Access only let me make 1-M rel. between the tree tables on the right.

As the photo “Cus_Con_Emp” shows I have made a form with two sub forms. Mainform handles Customers, upper subform handles Constructionsites and the lower form handles Employees. They are linked: cusCustomersID-conCustomersID and frmLinConstructionSitesDelskjema].Form![conConstructionSitesID]-linConstructionSitesID

When I navigate the main form, the subform shows correct, but when I navigate the upper subform the lover sumform do not follow.

I can add one or many new sites, but I cannot allocate workers to the new site.

I can though do this manually in the tables and it then show up correct in the nested forms.

Any help on this matter?

[temo]

Sorry, but I don't know why the photos turn out so bad, when I post them, they are approximately 300 bytes etch

[TheSmileyCoder]

I don't know about the pictures. I have experienced similar issues myself. I just try to edit them in Paint so they are as small as possible before posting them.

What I can see from your picture is that you have all the relations defined at the same time, I.e. they must all be fullfilled simultaneously.

What you need to do, is first take your customers, employes and sites tables. Define the relation between customers and sites, and between customers and employes.

Now add the Sites table and employes table again, think access names them "tblEmployees_1" and "tbl_ConstructionSites_1". Now add your relTbl_SitesEmpl table, and define the relation between relTbl_SitesEmpl and tbl_Employees_1 and tbl_ConstructionSites_1.

Now I have noticed that sometimes access will rearrange your relations if you close and open the relations window, Thats not a problem, im just saying so you dont wonder whats happening, I guess its access way of making the relations in the smartest way.

[temo]

Hello

Thank you again, I will try this.

[temo]

Hello

Thank you again, I got this working now

Yours
Terje Moe