Table design lookups with where clause?

[steveh2]

How do you reference the current row in a where clause on a table design?

As an example, let's say we have three tables, company, address and employee:

* addresses belong to companies and have a CompanyID
* An employee has an AddressID to indicate the address at which they reside and a CompanyID to indicate which company they work for.


In the table design we want the dropdown for the addressID to be limited to the addresses matching the Employees CompanyID

So, for the design of the lookup clause in Employee, how do I reference my own CompanyID?

Steve
PS Before there are loads of "don't use lookups in table design..." we're prototyping a system and want to quickly be able to produce sample forms and modify data by clicking on tables!

 

[johnherman]

The easiest solution would be to separate the Company Addresses from the Employee Addresses, since Employees will not live at their company address (most likely) and companies will not be at the employee address (again most likely).

So, even though relational DB theory says an address is an address, these two addresses are sufficiently distinct as to justify two separate entities and physical tables.


Sometimes the grass is greener on the other side because there is more manure there - original.

 

[lupins46]

>PS Before there are loads of "don't use lookups in table >design..."

Don't use lookups in table design.... especially in this case since you cannot refer to another field in the same table in the design of a field.
What you are attempting requires use of a form.

 

[steveh2]

The adresses are the same, a company can have multiple addresses, and employees work at one of them.

Ok, it can't be done, no problem there, we'll just use the form wizard then quickly edit that.

Thanks for the replies, it's much appreciated, at least I know I'm not going crazy trying to do it!!!

Steve

 

[johnherman]

Sorry for my confusion:

Employee Table
Employee Key (PK)
Company Key (FK)
Company Location Sequence (FK in combination with Company Key - compound or concatenated foreign key)

Company Table
Company Key (PK)

Company Location Table
Company Key (FK) (PK*)
Company Location Sequence (PK*)
* compound or concatenated primary key



Sometimes the grass is greener on the other side because there is more manure there - original.

 

[willir]

Steve

For a Contaact database, I usually setup a one-to-many relationship between entitiy and address, and a many-to-many relationship between the entity and phone number (I include the email address as a "phone number") for the very reason you have listed.
- A company can have more than one address. I use SITE Code to track this - HOU = Houston, NYC = New York City, etc.
- An address can have more than one company. (Example: Company A outsources to Company E; Company B employees work on the premesis) Usually rare occurence.

CompanyTbl
CompanyCode - primary key
CompnmayName


SiteTbl
SiteCode - primary key
CompnayCode - foreign key to company table
Address

Contacts are assigned to a site...

EmployeeTbl
EmployeeID - primary key
EmployeeLN
EmployeeFN
SiteCode - foreign key to the site
CompanyCode - foreign key to vender since an employee may support Company A but works for Company B.

This seems to be similar to your design.

How does answer your query on the WHERE clause?

After assigning an employee to a company, then you just have to choose the site to get the address information.

You can reduce the number of address choices by repopulating your SiteCode combo box.

For the After Update CompanyCode combo box, the event could look like the following...

Private Sub cboCompanyCode_AfterUpdate(Cancel as Integer)

Dim strQ as String, strSQL as String
'Note: Bothe company and site codes are text / strings

strQ = CHR$(34)
strSQL = "Select SiteCode, CompanyCode from SiteTbl"

If Len(Nz(Me.cboCompanyCode),"") > 0 Then
    strSQL = strSQL & " Where CompanyCode = " & strQ & Me.cboCompanyCode & strQ
End If

Me.cboSiteCode.RowSource = strSQL
Me.cboSiteCode.Requery

End Sub

(I started wirting this post this AM, but got side tracked until late PM.

 

[steveh2]

Using the form was not the problem, it was how to do it at the table design level, it can't be done, we've used forms.

Steve

 

[willir]

Steve

I am a little puzzled...

In the table design we want the dropdown for the addressID to be limited to the addresses matching the Employees CompanyID

Is this your objective?

Then you state...

Using the form was not the problem, it was how to do it at the table design level, it can't be done, we've used forms.

If you are stating that you can not use a table design to accomplish your objective, I beg to differ.

Here is a scenario...
International Company A has addresses around the world.

They outsource their IT to Company B and C. Focusing on Company B, Company B would have some addresses that co-exist with Company A plus other addresses used for other companies they support plus their corporate and regional addresses.

You can achieve this several ways. The design I presented achieves this with the CompanyCode and SiteCode assigned to an employee.

Who works at this specific site?
Query all employees matching the SiteCode regardless of company.

Who works at for Company A at this specific site?
Query for a match on Company A and the SiteCode.

Where do all employees at Company A work?
Query for SiteCodes for a match on the CompanyCode.
In fact this answers your post ...

In the table design we want the dropdown for the addressID to be limited to the addresses matching the Employees CompanyID

Note that once you have the site code, you have the address.

On the SiteTbl, I include the CompanyCode. I forgot to mention that this would refernce who "owns" the site, and does not restrict the site to one specific company.

By changing the RowSource "on the fly" for the Address combo box, the combo box will depict only addresses where the Company has addresses. (My bad for not mentioning this the first time around)

Did I miss something?


...Moving on to the Table Design.
You can setup the LookUp at the table level. The issues are...
- That the combo box is loaded when the form is loaded. You have to reload the record set assigned to the combo box each time the company code changes. You have to do this at the form level...

Me.cboSiteCode.RowSource = strSQL
Me.cboSiteCode.Requery

- Next, you can not / should not use a parameter query (where Access looks for a specific site codes to match a company code - is this what you want to do?) in the LookUp field. Any time the site code field was referenced, it would have to know what the CompanyCode is, and where specifically to go and get the CompanyCode. It is oner thing to force integrity and restrict CompanyCodes and SiteCodes to their respective table and field; it is quite another to drill down to the record level.


I appreciate the convenience of setting up LookUps at the table level - sure fascilatates the creation of the forms. However, I invite you to read (perhaps you already have) the Ten Commandments of Access by Dev Ashish and Arvin Meyer...

http://www.mvps.org/access/tencommandments.htm

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

Good luck on your project.

 

[steveh2]

So I think you agree with me, it can't be done, you cannot have a dynamic parameter query at the table level in the lookup tab that limits the contents of a dropdown, you have to do it at the form level.

Steve