Lookup Not to show resigned staff

[Harrold]

Hi

In salary table, employee names is lookup from employee details table. How can i to show the current employees only in the drop down list in salary table?

Thanks

 

[Brianwarnock]

http://access.mvps.org/access/lookupfields.htm

Read the above about the problems of lookup fields in tables

Brian

 

[Harrold]

Hi Brian,

I have read it and not really understand it fully. A main puzzle come to my mind. If i dont use lookup, what else can i use? This is what i understand how two tables relates to each other.

Thanks

 

[Brianwarnock]

A search on google for access table relationships will give a number of hits including
http://support.microsoft.com/kb/304466

Which show how table relationships are designed.

Brian

 

[Harrold]

Thanks Brian,

so far, i know how to use referential integrity with those required relationships. But how it applies to table really puzzle me. i still need to use lookup in the table to see the relationship, right?

 

[Brianwarnock]

. i still need to use lookup in the table to see the relationship, right?

Wrong

The two links that I have posted tell you not to use Table Lookups and how to define and display relationships.

Tables are for data storage not for user interaction, users use forms and reports powered by queries where the joins and criteria are applied.

Brian

 

[jdraw]

How do you identify/define a current employee?

 

[Harrold]

thanks Brian,

I get what you meant now.

Hi Jdraw,

Current employee means currently still under company employment, ie not yet resigned and i need to pay them salary.

 

[jdraw]

I guess by your answer you don't have some sort of IsActive flag or similar?

It sounds that once someone resigns (dies on the job, quits, leave of absence, sabbatical,...) they just get dropped from the company employment table.

 

[Harrold]

Hi Jdraw,

Do you mean like a checkbox to state whether that person is resigned or not? If so, i have it.

I am trying to design a basic payroll system. Hence, i have so many questions.

 

[Alansidman]

Make sure that the field holding the checkbox is included in your underlying query when you build your combo box in a form. In the criteria for the underlying query for the check box (which you will determine to be either checked for employed or checked for no longer employed) put either -1 for checked or 0 for unchecked depending on how you set it up.