Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Access Query Help


(!)

Bradybunch's Avatar
Bradybunch Bradybunch is offline
Computer Specs
Member with 211 posts.
THREAD STARTER
 
Join Date: Sep 2009
Location: Midlands UK
Experience: IT Support Analyst
09-Aug-2012, 10:14 AM #1
Solved: Access Query Help
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
09-Aug-2012, 10:23 AM #2
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?
__________________
OBP
I do not give up easily
Bradybunch's Avatar
Bradybunch Bradybunch is offline
Computer Specs
Member with 211 posts.
THREAD STARTER
 
Join Date: Sep 2009
Location: Midlands UK
Experience: IT Support Analyst
09-Aug-2012, 10:44 AM #3
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 Thumbnails
Solved: Access Query Help-access.jpg   Solved: Access Query Help-relationships.jpg  
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
09-Aug-2012, 11:05 AM #4
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's Avatar
Bradybunch Bradybunch is offline
Computer Specs
Member with 211 posts.
THREAD STARTER
 
Join Date: Sep 2009
Location: Midlands UK
Experience: IT Support Analyst
09-Aug-2012, 11:12 AM #5
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
09-Aug-2012, 11:16 AM #6
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's Avatar
Bradybunch Bradybunch is offline
Computer Specs
Member with 211 posts.
THREAD STARTER
 
Join Date: Sep 2009
Location: Midlands UK
Experience: IT Support Analyst
09-Aug-2012, 11:39 AM #7
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 Thumbnails
Solved: Access Query Help-relationship1.jpg  
Attached Files
File Type: zip db1.zip (26.3 KB, 1 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
09-Aug-2012, 11:51 AM #8
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's Avatar
Bradybunch Bradybunch is offline
Computer Specs
Member with 211 posts.
THREAD STARTER
 
Join Date: Sep 2009
Location: Midlands UK
Experience: IT Support Analyst
09-Aug-2012, 12:27 PM #9
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
09-Aug-2012, 12:57 PM #10
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's Avatar
Bradybunch Bradybunch is offline
Computer Specs
Member with 211 posts.
THREAD STARTER
 
Join Date: Sep 2009
Location: Midlands UK
Experience: IT Support Analyst
10-Aug-2012, 06:48 AM #11
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
File Type: zip db1.zip (125.5 KB, 3 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
10-Aug-2012, 07:04 AM #12
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's Avatar
Bradybunch Bradybunch is offline
Computer Specs
Member with 211 posts.
THREAD STARTER
 
Join Date: Sep 2009
Location: Midlands UK
Experience: IT Support Analyst
10-Aug-2012, 07:12 AM #13
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
10-Aug-2012, 07:21 AM #14
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
File Type: zip db1.zip (125.5 KB, 7 views)
Bradybunch's Avatar
Bradybunch Bradybunch is offline
Computer Specs
Member with 211 posts.
THREAD STARTER
 
Join Date: Sep 2009
Location: Midlands UK
Experience: IT Support Analyst
10-Aug-2012, 08:11 AM #15
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
File Type: zip db1.2 (2).zip (125.8 KB, 1 views)
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑