Solved

Access Database: question on Conditional Statements for field population.

Posted on 2010-11-16
10
503 Views
Last Modified: 2012-05-10
Hello - I am creating this access database for one of my school's inventory/ equipment checkout.
Each piece of equipment has a unique asset tag on it.
What I am trying to accomplish is for when I assign a user a piece of equipment in the "Laptop Assigned" field in the users table, I want the "Assigned To" information to populate in the equipment table for the piece of equipment that has that asset tag.
Now there are multiple types of equipment that can be assigned to a user (PDA, Laptop, Camera etc) and the equipment table has a "Type" definition for each item.
I had been using a List Box but the problem with that ended up being that it listed out anyone who had any piece of equipment assigned to them, not just the user with that specific asset tag assigned to them.
I have attached an image that lists the 2 tables and the fields in them.
Basically this is what I want to have happen (if possible)...
If in Users I put an number (asset tag) in the "Laptop Assigned" Field, I want the "Assigned To" field in the Equipment Inventory Table to populate w/ that Users "Full Name" as long as Users "Laptop Assigned" = Equipment Inventory"Asset Tag" & Equipment Invenoty "Type" = Laptop
And then the same thing with PDA Assigned, then the type would be PDA etc.

Hope I made this clear enough!

Thanks in advance for any help!
tables.PNG
0
Comment
Question by:dnetsol
10 Comments
 
LVL 5

Expert Comment

by:tygrus2
ID: 34151292
Depend on which table you want to be the master for holding the link between equipment and user. Having the user listed in equipment and the equipment in the users table is redundant. Do you need to keep track have the history of use ? If so, then the "assigned" should be a separate table with the two ID's and the start and end dates.

You can use a query to join the tables for a report or a combo box for the set/lookup.

A relational database should enable you to query related and calculated information without having it explicitly saved in several places.
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 34152472
As I look into your Users Table, there are fields which need to be remove from it and move to another table. Laptop Assigned, PDA Assigned, etc. are duplications if those items are found already at Equipment Inventory.

If you can post a sample db, it would be useful for us to assist you better.

Sincerely,
Ed
0
 

Author Comment

by:dnetsol
ID: 34157897
tygrus2  - A history is not needed, I just need it to display who the current assigned user is.
Ed - On the Equipment Table there is a field for Type.  This is where the separate fields for Laptop Assigned PDA assigned etc come into play.
The goal is, if I put a Tag number in Laptop Assigned on My User form, which is the only place where the equipment assignment will be edited, I want the item in the Equipment table that matches Type "Laptop" and matches the Asset Tag to the number that was put in the field.
I have a lot of equipment types that the staff could sign out, I don't want to make a table for each of those as then  updating equipment lists would be a pain.

Thanks :)
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:dnetsol
ID: 34165471
Per request here is a "fake" version of it with just the fields associated with my issue.
Just to re iterate - What I am looking for is if from the User Form if I put an asset tag in the Laptop Assigned field, I want the users Full name to populate in the Equipment Inventory Table under the Assigned To Field for that specific laptop
And if I put a tag in in the PDA Assigned field in the users form, I want the same thing to happen on the Equipement Inventory Table for that PDA.
TestDB.accdb
0
 

Author Comment

by:dnetsol
ID: 34200545
any thoughts on this?
0
 
LVL 19

Accepted Solution

by:
MINDSUPERB earned 500 total points
ID: 34203296
This what I got. See attached file.

Based on what you want, this is how it works:

You enter the tag on either PDA or Laptop. Then after update of that box, the full name of the user will automatically update the Assigned To into the Equipment table.

Sincerely,
Ed


TestDB.accdb
0
 

Author Comment

by:dnetsol
ID: 34223874
Thanks Ed this is awesome. I have one more question for you though.  If the assignment changes.  IE say they had Laptop 1000 and now they have laptop 1002 can you tell me what the code to put in the query is to clear out the old field?  Or perhaps a query that checks upon form closing if that is easier.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36116648
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question