If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access 2003 "IF" function question
I'm not sure where to post this so if it's in the wrong place please let me
know and I will repost. How do I enter an "IF" function into a form? For example If the surname from a particular record is selected the first name auto populates. I currently have a table with Employee Details in it and one with Computer details in it, I want another table with the Employees name and which computer they have. So I want a form where I can pick the last name (from combo box, which I can do) the first name is auto populated and then I can pick which computer. I have a number of other places in my database that this would prove useful. Thanks |
#2
|
|||
|
|||
Access 2003 "IF" function question
Hi Nadihaha
You don't want to use an "IF" for this. You need (as I think you have already) 2 tables. 1 = Employee details 2 = Computor details There are a number of reasons for this. 1st you may have more than 1 employee with the same name, some employees may have the same type of computor. It may be that you have an inventory number for each computor. This will still need 2 tables. You need to lin the tables like this. tblEmployees EmployeeID = AutoNumber ComputorID = Number Employee1stName = Text Employee2Name = Text EmployeeAddressLine1 = Text EmployeeAddressLine2 = Text EmployeeAddressLine3 = Text etc etc tblComputors ComputorID = AutonNumber ComputorType = Text ComputorMake = Text ComputorOperatingSystem = Text ComputorPurchasedDate = Date/Time ComputorSerialNumber = Text etc etc Next link these 2 tables in the relationship window. Next either create a query based on 2 tables and then a form based on that or Create a form based on tblEmplyees and another based on tblComputor (ensure that the employeeID is on both forms. Open the form based on tblEmployees in design view and add the other form a subform - use the employeeID and the linking field When you look at a record of an employee the subform will show the computor that person has. hope this helps -- Wayne Manchester, England. "Nadihaha" wrote: I'm not sure where to post this so if it's in the wrong place please let me know and I will repost. How do I enter an "IF" function into a form? For example If the surname from a particular record is selected the first name auto populates. I currently have a table with Employee Details in it and one with Computer details in it, I want another table with the Employees name and which computer they have. So I want a form where I can pick the last name (from combo box, which I can do) the first name is auto populated and then I can pick which computer. I have a number of other places in my database that this would prove useful. Thanks |
#3
|
|||
|
|||
Access 2003 "IF" function question
Just had a thought - I forgot to say.
On your form you don't need to have all the fields from the table(s) shown. You could just a have EmployeeID, Name, Deptartment, etc ComputorID, EmployeeID, ComputotrType, etc If you add a combo (use the wizard to crete it for you) then you could just have a very simple form. It "may" be a good idea to have an option after the you have selected the Emplyee (so you can see the comptutor detals) that you can go to the full form which will show more details - ComputorType, ComputorMake, ComputorOperatingSystem, ComputorPurchasedDate, etc etc -- Wayne Manchester, England. "Nadihaha" wrote: I'm not sure where to post this so if it's in the wrong place please let me know and I will repost. How do I enter an "IF" function into a form? For example If the surname from a particular record is selected the first name auto populates. I currently have a table with Employee Details in it and one with Computer details in it, I want another table with the Employees name and which computer they have. So I want a form where I can pick the last name (from combo box, which I can do) the first name is auto populated and then I can pick which computer. I have a number of other places in my database that this would prove useful. Thanks |
#4
|
|||
|
|||
Access 2003 "IF" function question
The Problem is still if I select Employee ID in my field I have no idea who that relates to, likewise with Computer ID. I still need an If function to say if I select this ID auto populate the Employee Name fields appropriately. I'm trying to make this so that others can use the database, If I use the Employee ID as an identifying feature I may as well not use the form as they are going to have to use the table to figure out what the Employee Numbers relate to. "Wayne-I-M" wrote: Just had a thought - I forgot to say. On your form you don't need to have all the fields from the table(s) shown. You could just a have EmployeeID, Name, Deptartment, etc ComputorID, EmployeeID, ComputotrType, etc If you add a combo (use the wizard to crete it for you) then you could just have a very simple form. It "may" be a good idea to have an option after the you have selected the Emplyee (so you can see the comptutor detals) that you can go to the full form which will show more details - ComputorType, ComputorMake, ComputorOperatingSystem, ComputorPurchasedDate, etc etc -- Wayne Manchester, England. "Nadihaha" wrote: I'm not sure where to post this so if it's in the wrong place please let me know and I will repost. How do I enter an "IF" function into a form? For example If the surname from a particular record is selected the first name auto populates. I currently have a table with Employee Details in it and one with Computer details in it, I want another table with the Employees name and which computer they have. So I want a form where I can pick the last name (from combo box, which I can do) the first name is auto populated and then I can pick which computer. I have a number of other places in my database that this would prove useful. Thanks |
#5
|
|||
|
|||
Access 2003 "IF" function question
That and this is only part of my database.
I currently have a Hardware table - Containing all the Computer related Hardware. And a Software table with all of our software. I am going to have a table that combines the software with the hardware (ie what software's installed on which PC) So what I'm aiming for is a report that states who has what software. "Wayne-I-M" wrote: Just had a thought - I forgot to say. On your form you don't need to have all the fields from the table(s) shown. You could just a have EmployeeID, Name, Deptartment, etc ComputorID, EmployeeID, ComputotrType, etc If you add a combo (use the wizard to crete it for you) then you could just have a very simple form. It "may" be a good idea to have an option after the you have selected the Emplyee (so you can see the comptutor detals) that you can go to the full form which will show more details - ComputorType, ComputorMake, ComputorOperatingSystem, ComputorPurchasedDate, etc etc -- Wayne Manchester, England. "Nadihaha" wrote: I'm not sure where to post this so if it's in the wrong place please let me know and I will repost. How do I enter an "IF" function into a form? For example If the surname from a particular record is selected the first name auto populates. I currently have a table with Employee Details in it and one with Computer details in it, I want another table with the Employees name and which computer they have. So I want a form where I can pick the last name (from combo box, which I can do) the first name is auto populated and then I can pick which computer. I have a number of other places in my database that this would prove useful. Thanks |
#6
|
|||
|
|||
Access 2003 "IF" function question
On Wed, 1 Jul 2009 00:38:04 -0700, Nadihaha
wrote: The Problem is still if I select Employee ID in my field I have no idea who that relates to, likewise with Computer ID. Not if you use the tools that Access provides. I still need an If function to say if I select this ID auto populate the Employee Name fields appropriately. No, you do not. I'm trying to make this so that others can use the database, If I use the Employee ID as an identifying feature I may as well not use the form as they are going to have to use the table to figure out what the Employee Numbers relate to. There is a tool called a Combo Box that you can use on a Form. This can (and will, by default, if you use the toolbox wizard to create it) *DISPLAY* the person's name, but *STORE* the ID. See Crystal's video and the other resources here. Access will do a LOT (sometimes even too much!) to help you; you don't need to program *anything* to get a lot of user-friendliness built in. Jeff Conrad's resources page: http://www.accessmvp.com/JConrad/acc...resources.html The Access Web resources page: http://www.mvps.org/access/resources/index.html Roger Carlson's tutorials, samples and tips: http://www.rogersaccesslibrary.com/ A free tutorial written by Crystal: http://allenbrowne.com/casu-22.html A video how-to series by Crystal: http://www.YouTube.com/user/LearnAccessByCrystal MVP Allen Browne's tutorials: http://allenbrowne.com/links.html#Tutorials -- John W. Vinson [MVP] |
#7
|
|||
|
|||
Access 2003 "IF" function question
On Wed, 1 Jul 2009 00:43:01 -0700, Nadihaha
wrote: That and this is only part of my database. I currently have a Hardware table - Containing all the Computer related Hardware. And a Software table with all of our software. I am going to have a table that combines the software with the hardware (ie what software's installed on which PC) So what I'm aiming for is a report that states who has what software. Since a given piece of Software could be installed on many Computers, and each computer will have many different pieces of software, you need three tables for this: Computers ComputerID information about the computer as a thing in itself Software SoftwareID ProgramName Manufacturer other information about the program itself SoftwareInstalled ComputerID what computer was it installed in SoftwareID what got installed InstallDate other info about this installation, e.g. version, employee ID of installer, etc -- John W. Vinson [MVP] |
#8
|
|||
|
|||
Access 2003 "IF" function question
Hi John Yes this is what I have, as well as a few others. I'm using this
learning curve to get rid of a few unmanageable spreadsheets. "John W. Vinson" wrote: On Wed, 1 Jul 2009 00:43:01 -0700, Nadihaha wrote: That and this is only part of my database. I currently have a Hardware table - Containing all the Computer related Hardware. And a Software table with all of our software. I am going to have a table that combines the software with the hardware (ie what software's installed on which PC) So what I'm aiming for is a report that states who has what software. Since a given piece of Software could be installed on many Computers, and each computer will have many different pieces of software, you need three tables for this: Computers ComputerID information about the computer as a thing in itself Software SoftwareID ProgramName Manufacturer other information about the program itself SoftwareInstalled ComputerID what computer was it installed in SoftwareID what got installed InstallDate other info about this installation, e.g. version, employee ID of installer, etc -- John W. Vinson [MVP] |
#9
|
|||
|
|||
Access 2003 "IF" function question
Thanks John,
This will work for now we don't have any last name duplicates. However I forsee a problem. I currently have the last name and the first neame stored in separate fields (everything I've read so far about access says this is what I should do) However what will happen when we get bigger and do encounter name duplicates? I would rather counteract this problem now. My thinking is to select by last name and if there is only one with this last name store the ID but if there is more than one record give me a choice of first names....is this at all possible? The other problem I have is when I run my report it displays the ID's not the name so the report is useless without being able to access the database to check the ID's. Gah I feel like this is a bit to hard, but I know it will be better in the end. I just want it to be basic to use, so that it will be utilised!! "John W. Vinson" wrote: On Wed, 1 Jul 2009 00:38:04 -0700, Nadihaha wrote: The Problem is still if I select Employee ID in my field I have no idea who that relates to, likewise with Computer ID. Not if you use the tools that Access provides. I still need an If function to say if I select this ID auto populate the Employee Name fields appropriately. No, you do not. I'm trying to make this so that others can use the database, If I use the Employee ID as an identifying feature I may as well not use the form as they are going to have to use the table to figure out what the Employee Numbers relate to. There is a tool called a Combo Box that you can use on a Form. This can (and will, by default, if you use the toolbox wizard to create it) *DISPLAY* the person's name, but *STORE* the ID. See Crystal's video and the other resources here. Access will do a LOT (sometimes even too much!) to help you; you don't need to program *anything* to get a lot of user-friendliness built in. Jeff Conrad's resources page: http://www.accessmvp.com/JConrad/acc...resources.html The Access Web resources page: http://www.mvps.org/access/resources/index.html Roger Carlson's tutorials, samples and tips: http://www.rogersaccesslibrary.com/ A free tutorial written by Crystal: http://allenbrowne.com/casu-22.html A video how-to series by Crystal: http://www.YouTube.com/user/LearnAccessByCrystal MVP Allen Browne's tutorials: http://allenbrowne.com/links.html#Tutorials -- John W. Vinson [MVP] |
#10
|
|||
|
|||
Access 2003 "IF" function question
On Wed, 1 Jul 2009 16:59:00 -0700, Nadihaha
wrote: Thanks John, This will work for now we don't have any last name duplicates. However I forsee a problem. I currently have the last name and the first neame stored in separate fields (everything I've read so far about access says this is what I should do) However what will happen when we get bigger and do encounter name duplicates? Store a unique ID, the last name, the first name, and some other field or fields that would let you identify the person: nickname, department, position, phone number, whatever works in your situation. You can display up to ten fields in a combo box; and you can and should concatenate the actual name fields into one, e.g. a query like SELECT PersonID, [LastName] & ", " & [FirstName] & (" " + [MiddleName]) AS FullName, [Department], [Position] FROM peopletable ORDER BY LastName, FirstName, MiddleName; Adjust the field and table names and use this as the rowsource of a combo box. I would rather counteract this problem now. My thinking is to select by last name and if there is only one with this last name store the ID but if there is more than one record give me a choice of first names....is this at all possible? The above is simpler and lets the user see and select the firstname. The other problem I have is when I run my report it displays the ID's not the name so the report is useless without being able to access the database to check the ID's. Do not base the report on the Table. Instead base it on a Query joining your table to the "people" table by ID. Pull the person's name from the people table, and the other data from your main table. You're using a relational database, not a spreadsheet! You DO NOT need to (nor should you) put everything in one table to display it; use Queries to combine data from all of the relevant tables, and base your report *on that query*. Gah I feel like this is a bit to hard, but I know it will be better in the end. I just want it to be basic to use, so that it will be utilised!! To a certain extent, the more work you do up front in the design of the database, the less work your users will need to do in using it. And you'll only be building the database once, and your users will hopefully use it for a long time. -- John W. Vinson [MVP] |
|
Thread Tools | |
Display Modes | |
|
|