1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access Query Help

Discussion in 'Business Applications' started by Bradybunch, Aug 9, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Bradybunch

    Bradybunch Thread Starter

    Joined:
    Sep 10, 2009
    Messages:
    211
    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 :)
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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?
     
  3. Bradybunch

    Bradybunch Thread Starter

    Joined:
    Sep 10, 2009
    Messages:
    211
    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
     

    Attached Files:

  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
     
  5. Bradybunch

    Bradybunch Thread Starter

    Joined:
    Sep 10, 2009
    Messages:
    211
    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.
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
     
  7. Bradybunch

    Bradybunch Thread Starter

    Joined:
    Sep 10, 2009
    Messages:
    211
    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
     

    Attached Files:

  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
     
  9. Bradybunch

    Bradybunch Thread Starter

    Joined:
    Sep 10, 2009
    Messages:
    211
    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
     
  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
     
  11. Bradybunch

    Bradybunch Thread Starter

    Joined:
    Sep 10, 2009
    Messages:
    211
    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
     

    Attached Files:

    • db1.zip
      File size:
      125.5 KB
      Views:
      9
  12. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
     
  13. Bradybunch

    Bradybunch Thread Starter

    Joined:
    Sep 10, 2009
    Messages:
    211
    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.
     
  14. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
     

    Attached Files:

    • db1.zip
      File size:
      125.5 KB
      Views:
      12
  15. Bradybunch

    Bradybunch Thread Starter

    Joined:
    Sep 10, 2009
    Messages:
    211
    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?
     

    Attached Files:

  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1064459