Query Help

[zero3ree]

I have a query named projectsQRY that populates fields from a table named projectsTBL. I also have a query set up with a list of employees named empList. I have a couple fields in projects QRY that get names from empList. What I need to do is build an event for each name to populate in the projectsQRY. The fields that are populated with names are called projectMGR, stationLead, stationSuper. I imagine needing to make 3 seperate fields that populate the email address from empList based on the name that is in those three fields. I am not sure how to do this any help would be great.

[alansidman]

I am a little bit confused. Which fields are in which table? Why don't you list the tables you have and the fieldnames for each. Then tell us which fields from each table you wish to have in the query. Identify your primary keys and your foreign keys in each table.

With all this we should be able to give you a satisfactory answer.

Alan

[zero3ree]

Sorry about that I know that was a bit confusing. It was confusing to me to type it . Basically If there is a name of an employee or contact in the fields, I want contact info to show up at the bottom of the page in the Project View form.

I though the best way to make this happen would to have the query look in the various fields and place records from the employee and contact query based on the name in each field.

The final goal is to have a list of people associated with the project populate at the bottom of the project view form.

I just realized I can't attach my db so here is a list

Querys:

contactQuery: full name, id, email

employeeQuery: full name, id, e-mail address

project Query: Station Supervisor, Station Lead, PnC Lead, Designer

Form:

Project View: here i need a the email and name to populate if a name from employee or contact is in any of the fields from project Query.

I made a simplified version if there is a way to send it.

[ssanfu]

I stole this from June7's sig:

To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post.
Attachment Manager is below the Advanced post editor window.

[zero3ree]

Project.zip

Cool hope that works. Thanks.

[alansidman]

I'm looking at the three tables. I can see no relationship between the project, the employees and the contacts. What is the common field to join these tables on? What is the relationship. Once you establish the relationships then you can populate the form.

Your db looks relatively flat. Suggest you read this white paper on developing a relational database. This may help to pull the data you want easily.

http://www.deeptraining.com/litwin/d...aseDesign.aspx

Alan

[ssanfu]

I agree with Alan. While you could leave it as is, you lose a lot of the power of Access. Your structure should be in at least 3NF.

Additionally, object names in Access should only be letters, numbers and the underscore. (bad - has spaces and the @ sign -> Status @EASi-AEP)
Read http://access.mvps.org/access/tencommandments.htm
and
"The Evils of Lookup Fields" http://access.mvps.org/access/lookupfields.htm

You also might Google "Normalization"

[zero3ree]

The actual dat6abase is relational and normalized. I "dumbed" it way down just so you could see what it is I was trying to accomplish in a simple version. I was hoping that there was a easy solution to what I was trying to have the form do.

[ssanfu]

It really doesn't help to post a dB that is totally different than your actual structure.

As far as your question, not knowing how the tables are related or what projectsQRY looks like, you could add a new column to your query:

EMails: iif(not isNULL([projectMGR]),[projectMGR_email],"") & vbcrlf & iif(not isNULL([stationLead]),[stationLead_email],"") & vbcrlf & iif(not isNULL([stationSuper]),[stationSuper_email],"")

This field would be bound to a text box.

Or you could write a UDF, passing the [projectMGR], [stationLead] & [stationSuper] names, opening a recordset to get the email addresses. Something like

Function GetEmail(pMGR, pLead, pSuper)
   'code goes here
end function

You could have 3 columns in the query that is then bound to 3 text boxes on the form. The UDF would have only one name passed

Function GetEmail(pWhatName)
   'code goes here
end function

(the prefix "p" is what I use to indicate a parameter)

[zero3ree]

I apologize for all of the confusion and I appreciate your time. The code above would work except that the the names in the contact query could be any of the title. What I mean is that the email can't be specific to project manager or station lead and so on. The contacts on the form need to be populated simply by saying "if a name is located in any of the fields then go out to the employees query and contact query and list it". Maybe a sub form could work. I just don't know how to set it up.

[ssanfu]

It would still work with a little tweaking.

The contacts on the form need to be populated simply by saying "if a name is located in any of the fields then go out to the employees query and contact query and list it"

Since I can't see the SQL of the query, how about a few examples???

email can't be specific to project manager or station lead a

So if there is a name in the "project manager" control, what email should be returned?
If there are names in both "project manager" AND "station lead', what email should be returned???

[zero3ree]

Sorry for the delay. The email should match the name that shows up in the "project manager" control and so on. So in the project_tbl query The names that are in the various title fields should generate a list at the bottom of the Project View form. This info comes from the contacts query and employee query. On any given project a different employee or contact could be place in those fields. So if Joe Smoes names is in any of those fields his name and email should populate on the Project View form where it says "Contacts". The best way to explain it would be like in excel with the vlookup command.

[ssanfu]

I looked at your dB last night. I wrote some notes, but left them at home, so I'll do the best I can.

As I see it, the main problem is that your table structure is not normalized. Why do you have two tables for names & email addresses? they should be in one table with maybe an additional field to say "Admin" or "Employee".

In the main table, I would store the PK form the "Contacts" table rather than the actual name.
I would have the form's record source a query. It is easier to sort/filter a query than a table.

Storing names instead of a PK from the "Contacts" table will (could) cause problems.... what do you do if you have two people named "John Smith"?


As is, the only way that I can see to get the email addresses is to write a UDF to search both tables for the name in each of the controls on the form for the email that you want to display in the control at the bottom of the form.

[zero3ree]

Thanks for the input. The two tables are different because contacts is for an outside company we jointly do projects with and the employee table is our people. I hadn't thought about two names being the same that brings up a good point. I have actually been trying to create udf with a
"dlookup" but I can't figure out how to make it load when I want. Right now I have it "on click" which is annoying. What would I use to make it run when the user picked a project from the drop down menu on the "Project View" form.

[ssanfu]

I would start by using the after update event of each of the names where you want the email address added to the control.

I would use a recordset rather than DLookup() (my preference). You would be able to skip the look up of the email address if the control was NULL.


Two tables for names/email address is harder (more work) than one table. You would have an additional field that could have Internal/External as the values. If you have two tables and you are trying to find an email address, you have to have search two tables. Twice the work.