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 |
#21
|
|||
|
|||
Drop down list box colour
Hi Crystal,
No worries about the delay...it really is very kind of you to help out, so no worries! I hope to play around with this next week...I will let you know how it goes! Again thanks for your really fantastic help--you ARE AWESOME! Have a great weekend---we have a long weekend here...WAHOOOO! Billiam "strive4peace" wrote: Hi Billiam, sorry for the delay in responding, we had some issues... Personally, I would create: Statuses - StatusID, autonumber - Status, text, 15 (ie: Active, Suspended) It appears that the statuses you listed relate to people that are available for work ... so you might have: EmpStatus - EmpStatID, autonumber - EmpID, long -- FK to Employees - StatusID, long -- FK to Statuses - StatDate, date -- date that Status is effective If, for instance, StatusID = 1 -- Active: you would use a combobox similar to the following to choose from employees who are currently active: RowSource: SELECT Employees.EmpID, [EmpLast] & ", " & [EmpFirst] AS Employee FROM Employees INNER JOIN EmpStatus ON Employees.EmpID = EmpStatus.EmpID WHERE ((EmpStatus.StatusID=1) AND (EmpStatus.StatDate=DMax("StatDate","EmpStatus","E mpID=" & [employees].[EmpID]))); Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * Billiam wrote: "strive4peace" wrote: Hi Billiam, no, Status is dynamic. If you have no need to know when particular status happened, then keeping it in the table is ok Yes, I agree Status is dynamic,however, I do not need to know when their Status changed, and so I keep the current status in a field in the table. "I decided against using a lookup table as the options have never changed" I do not understand... What I mean is that the list of Status choices is very small and they have never changed over the organzation's history of 20+years. I understand that that does not mean they will never change ;-) As I have several other lookup tables, I decided not to go with a seperate Lookup Table of Statuses to keep the structure more simplistic. If you think the payoff is worth it, I believe it would not be too hard to move them to a lookup table...But I still am unclear where the yes/no would come in? I believe I would simply have a table called Status, StatusID, Status, and under the Status Field, I would list the 7 status types. As far as the form is concerned, I would use a combobox to choose the current Status from, and store the Status ID in my table...am I doing this right? I asked questions about [Active] and [Suspended] -- but you answered with a question about [Status] -- are your issue resolved? When you ask a question with square brackets around an item [Active] and [Suspended] I think you are asking about fields or tables...and this is confusing me. I only have 1 field called Status under which the status choice is listed (Ie Active, Suspended) and the status choice is selected from combobox. The datatype of the Status field is Text, and so all of the choices are also text (Active, Suspended etc) So I thought when I told you that the Field Status was datatype=Text, I thought that would make it clear that the Status choices listed under that field would also be text. I hope I am explaining my reasoning properly, but it seems I must be missing something? Best regards, Billiam Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * Billiam wrote: Hi Crystal, "strive4peace" wrote: What is the data type for [Active] and [Suspended] ? If those conditions aren't working, then they are probably text, not yes/no (as they should be) I have a Field called "Status", text datatype, which is where the Instructor's status is recorded from the bound combobox which supplies the options. I decided against using a lookup table as the options have never changed, and an Instructor can only have one status at a time. Do you still think that I need to use Yes/No, and if so, would I have a field then for each option in a lookup table??? Have a great day, and of course many thanks for your help! Billiam |
#22
|
|||
|
|||
Drop down list box colour
thank you, Bill happy to help
enjoy your weekend smile Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * Billiam wrote: Hi Crystal, No worries about the delay...it really is very kind of you to help out, so no worries! I hope to play around with this next week...I will let you know how it goes! Again thanks for your really fantastic help--you ARE AWESOME! Have a great weekend---we have a long weekend here...WAHOOOO! Billiam "strive4peace" wrote: Hi Billiam, sorry for the delay in responding, we had some issues... Personally, I would create: Statuses - StatusID, autonumber - Status, text, 15 (ie: Active, Suspended) It appears that the statuses you listed relate to people that are available for work ... so you might have: EmpStatus - EmpStatID, autonumber - EmpID, long -- FK to Employees - StatusID, long -- FK to Statuses - StatDate, date -- date that Status is effective If, for instance, StatusID = 1 -- Active: you would use a combobox similar to the following to choose from employees who are currently active: RowSource: SELECT Employees.EmpID, [EmpLast] & ", " & [EmpFirst] AS Employee FROM Employees INNER JOIN EmpStatus ON Employees.EmpID = EmpStatus.EmpID WHERE ((EmpStatus.StatusID=1) AND (EmpStatus.StatDate=DMax("StatDate","EmpStatus","E mpID=" & [employees].[EmpID]))); Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * Billiam wrote: "strive4peace" wrote: Hi Billiam, no, Status is dynamic. If you have no need to know when particular status happened, then keeping it in the table is ok Yes, I agree Status is dynamic,however, I do not need to know when their Status changed, and so I keep the current status in a field in the table. "I decided against using a lookup table as the options have never changed" I do not understand... What I mean is that the list of Status choices is very small and they have never changed over the organzation's history of 20+years. I understand that that does not mean they will never change ;-) As I have several other lookup tables, I decided not to go with a seperate Lookup Table of Statuses to keep the structure more simplistic. If you think the payoff is worth it, I believe it would not be too hard to move them to a lookup table...But I still am unclear where the yes/no would come in? I believe I would simply have a table called Status, StatusID, Status, and under the Status Field, I would list the 7 status types. As far as the form is concerned, I would use a combobox to choose the current Status from, and store the Status ID in my table...am I doing this right? I asked questions about [Active] and [Suspended] -- but you answered with a question about [Status] -- are your issue resolved? When you ask a question with square brackets around an item [Active] and [Suspended] I think you are asking about fields or tables...and this is confusing me. I only have 1 field called Status under which the status choice is listed (Ie Active, Suspended) and the status choice is selected from combobox. The datatype of the Status field is Text, and so all of the choices are also text (Active, Suspended etc) So I thought when I told you that the Field Status was datatype=Text, I thought that would make it clear that the Status choices listed under that field would also be text. I hope I am explaining my reasoning properly, but it seems I must be missing something? Best regards, Billiam Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * Billiam wrote: Hi Crystal, "strive4peace" wrote: What is the data type for [Active] and [Suspended] ? If those conditions aren't working, then they are probably text, not yes/no (as they should be) I have a Field called "Status", text datatype, which is where the Instructor's status is recorded from the bound combobox which supplies the options. I decided against using a lookup table as the options have never changed, and an Instructor can only have one status at a time. Do you still think that I need to use Yes/No, and if so, would I have a field then for each option in a lookup table??? Have a great day, and of course many thanks for your help! Billiam |
Thread Tools | |
Display Modes | |
|
|