[Bradybunch]

Im having difficutly trying to work out how to bring together 3 different types of infomation.
I have a Mobile Device list table called TblMobileDeviceID which be used to input new Mobile Devices as and when we buy them. I also have a TblMobileSimID, which again, when having a new number it is in the list of sim's so we know what we have.
I then have the User infomation. I have a table with all the Users with Office they are allocated too. TblUserID

Think i need to Create another table with the UserID, MobileDeviceID, SimID...however i just dont know.

I already have the Sim and Device info in my Tables. I want to allocate Jo Bloggs with a particular mobile Device from my list, and Sim. How do i do it?

Would be greatful for some guidence please

[OBP]

I would have the Many to Many table with all 3 IDs in it if the sim, phones and users can be mixed .
If only one sim card is allocated to a phone prior to the phone going to a user then you could just have the sim card in the phone table and only have phoneID & userID in the many to many table.
However if the phone is identified by a code and the code is unique and only one phone goes to one user you only need to have the phoneID in the user table.

So how does the system work?

[Bradybunch]

I am not totally Clued up on what Many to Many means? Ive attached two Pictures, so that you can see how my relationships are at the moment, and how/what i have in my tables which are relavant.

The Mobile number always stays with the user until the user leaves. The phone will get allocated to the user. The phone will have its Model, and Device IMEI - Which makes it Unique. The Sim number can get transferred to another sim should there be a problem with the Sim. At the moment, we have a spreadsheet, but it all needs to go into a DB, better controlled and storage.

End result - I want a list of Phones, Sims, and a list of users allocated to the phone and Sims

Thanks

[OBP]

That looks good, but as I said if a sim stays with a phone the simid could go in the phone table and be removed from the MobileUserID table.

[Bradybunch]

The sim doesnt always stay with the phone, because the phone could break, then replacing the phone with another one. The phone needs to be made changeable. I also need to keep the phone in the list for archive history, on whats wrong with it etc and why its not being used. I also have unallocated Sims, as well as phones. So in my head im thinking...i have a user, and i want to select that number, and that phone. However...the User may have two or even three devices. Mayhave a Bar phone, Smartphone, and data Stick.

[OBP]

In that case keep the table as it is.
The joins should go from the one side User, Phone, Sim to the Many table tblMobileUserID.

[Bradybunch]

Okay, so i have ameded the relationships to the Picture attached,

Where do i start with my query/querys? Ive attached a DB with the tables to make this whole thing easier. See attached

[OBP]

Create a Main form either for the User or the Phone.
Create a query of the tblMobileUserID.
Create a subform from the query.
If the user is on the mainform link the subform via the UserID
on the sub form create a Combo to select a Phone and a combo to select a Sim.
If the phone is on the mainform link via PhoneID and create a combo for the user.

To bring all the data back together create a query from thetblMobileUserID table and add the other 3 tables and add whatever fields are required.

[Bradybunch]

Okay i have my self in a right pickle! ha! Thinking about this...I need to The user info at the top of a form, so i can select the UserID. I then need to associate that user with a number or two. So ill need to have a list. So the subform has to be list view. I then need to associate a particular phone to one of the sims.

UserID
Office ETC

Number: 0789999999 Model: Nokia
Number: 0789666666 Model:HTC

Think thats how i need to have it, but i dont how to do it :s

[OBP]

As I said in post #8, you create the mainform based on the User and UserID, you can use a find combo to find the required user.
The master child link to the subform (based on the tblMobileUserID or a query) is the UserID, that takes care of that entry in the subform.
In the subform you need a combo based on the phone table (or preferably a query as you can sort the phones) using the PhoneID as it's control source, that takes care of the phone selection.
You do exactly the same for the SIM.

[Bradybunch]

I have been playing with this now, and i have come up with the following. You would select the Office and then select user to go with the office, which in turns puts the referrances needed further down the line in the database. When adding the sub form, it make the sub form a new as apposed to it selecting and filling the the fields. when i opened the sub form on it own it worked fine, but adding into the main form its just not doing it ? See attached

Thanks

[OBP]

OK, I need to clean some tables up first, does the Qube refer to the Office or the User?
As the data should not be in either of those 2 tables, only the QubeID.

[Bradybunch]

In TblOfficeID the office and Qube related info are associated together. I need the Qube info to be in each record...which when done will not be visible...but will be referance to bring up all Hardware, telphone line mobile number to do with the one office. So Qube is with the Office. The User is also associated with an office in the TblUserID - This is so when selecting the office only the users associated with that office will appear in the drop down.

[OBP]

OK, but the data should no be in the other tables, the Office table should have the QubeID in it to get the required data, but only displayed not stored in the Office.
I have created a query & subform based on the TblMobileUserID table.
Changed the Mainform to be based on the UserID and added the subform.
I have added 2 phones and Sims to the 2nd & 3rd users.
I have removed the Data Entry = Yes so that you can see the records.
I prefer to work with all the records and have a "New Record" button for adding data, that way you can change someones phone using the same form.

[Bradybunch]

Okay...

Ive attached the DB again, with another Table called TblSiteLocationID. This table has the list of properties the company i work for own. There are about 600. There is a QubeRef assigned to each one. Every new Site added a new QubeRef is made and assigned to it. In the TblOfficeID i have all the offices we have around the country about 48. I combo boxed the QubeRef in TblOfficeID to the TblSiteLocationID for the list of QubeRef. In TblOfficeID the office gets assigned a QubeRef. In the TblQubeID i have made it a combo list look up table. I can then select the data i need in the QubeSchedule,Fundtype,Description. The idea is then i can select the office and it will be associated to all the data needed.

Ive looked at the Form, and i see how you have done it, but how do you then add a new user?