|
Microsoft Access gimps.
|
View this Thread in Original format
| DJ Mikey Mike |
Access.
I have been tasked at work with creating a relational database to log all company equipment in the business (which includes 3 offices and a bucket load of remote users working from home) such as PC's - and all software on that particular PC - Users, Monitors, Printers, Scanners, Docking Ports, etc. And I have to use Microsoft Access. I don't like Access. I'm not that familiar with Access. And like most Microsoft programs, it's full of holes and is rather .
So to start things off I needed to create a main form, and I decided to make this based on location; and so, for my primary key I chose 'Location ID' that is automatically generated every time I enter in the location. After that I created all my subforms on to this Location Form - PC details sub-form, User details sub-form, Monitor details sub-form, etc. (all of which had the field 'location ID' in the table so that I could link these sub forms to my main location form's primary key.)
Which is all fine and Dandy. But what I want to start doing now is start linking fields between some of these sub-forms. In particular, the user sub-form. For example, I eventually want PC details to be linked primarily to a location - because this will always be fixed - but also to a user within that location (ie. My user sub-form.) How the do I do that? I can't seem to create these relationships between sub-forms once the forms have been made. The way I have
Alternatively, it's more than likely I have over complicated things for myself here since I don't have very good knowledge with this program. Is there a better way I could have approached things?
Reading this back, I don't think I could have explained this any worse if I tried. So if I need to elaborate on anything, just ask.
Cheers guys. |
|
|
| dj_bas |
Let me see if I have your database set up right. You have table with locations, each location has PCs, users, and monitors. You have another table with details for each PC, user, and monitor. So you have 4 tables so far correct?
No what you want to do is link PCs to user, based on location? |
|
|
| DJ Mikey Mike |
Bas, these are all my tables (listed a little further down the page.) I have based my main form based around the location table, and added all the other tables as sub forms via the form wizard (apart from Software, which I have created as yet another sub-form but this time placed it within the PC sub-form!)
So at the moment, the following relationships are in place.
Location -> User
Location -> Computer
Computer -> Software
Location -> Monitor
Location -> Printer
Location -> Scanner
Location -> Telephone
Location -> Docking Station
But What I want is to also be able to link a User to a Computer, a User to a Monitor, a User to a Printer, and so on. And possibly even add a Desk number assigned as well in to the equation.
tblLocation
Location ID
Location
tbluser
User ID
User
tblcomputer
Computer ID
Make
Model
Serial Number
Barcode
CPU
Memory
HDD
Network
Sound
tblsoftware
Software ID
Software Name
Software Version
tblmonitor
Monitor ID
Make
Model
Serial Number
Barcode
tblprinter
Printer ID
Make
Model
Serial Number
Barcode
tblscanner
Scanner ID
Make
Model
Serial Number
Barcode
tbltelephone
Telephone ID
Make
Model
Serial Number
tbldockingstation
Docking Station ID
Make
Model
Serial Number
Have I approached this in a bit of a way? Is there a better way of creating the relationships between the tables without doing it through a form wizard? Am I making any sense? :p |
|
|
| kid nyce |
Mikey, I think you've over complicated things
3 tables
Location ID
User Info
Equipment
Location ID relationship with User Info
User Info relationship with Equipment
Neg values for users without info (no phone number)
Neg values for users without certain equipment
Then your result querys become contingent on certain relationships.
How many users in location ID with scanners
Expr1: IIf(table + logic + table, true value, false value)statement will generate your results
The best way is to keep the tables to a min, but your queries to be complex. If you have simple queries that shoot across multiple tables, you'll find out how many nested queries you'll have to attain one simple results.
I could be completely off and not understand what you're getting at. I've only had a bit of hands on experience with access and forms. |
|
|
|
|
