Trying to link or match two fields (foreign keys) across tables

[nhorton79]

Hi,

I am setting up a database which so far has a Client table, Contacts table and Job table.
Clients can have many Jobs and many Contacts, each Contact can have many Jobs but only one Client, each Job can only have one Client and one Contact.

In the Contacts table you can select the Client that the Contact refers to through a dropdown box.

In the Jobs table you can select the Client that the Job refers to through a dropdown box also.

I would like then to be able to select the Contact for the Job from a dropdown box, but only display Contacts that relate to that particular Client.

I tried creating a new field Job.JobContact and made it a list box with the following expression:

SELECT [Contacts].[ContactFirstName] & " " & [Contacts].[ContactLastName]
FROM Contacts
WHERE ((([Job].[JobClient])=([Contacts].[ContactCompany])));

I am only just learning Access so this might be wrong.
Every time I try and select the dropbox box it says "Enter Parameter Value"

Might this has something to do with the fact that the field for Job.JobClient is a lookup AND the field for Contacts.ContactCompany is also. When I look at these in DesignView it states that each field is a number (due to the lookup) - Would the "number" be the same between the different tables even though relating to the same overall Client?

Or is there a better way to do this?

HELP!

[ItsMe]

I think you need cascading dependent combo boxes

This seems like a sound example here
http://www.fmsinc.com/microsoftacces...cascading.html

[nhorton79]

Hi ItsMe,

Thanks for pointing me towards cascading combo boxes.

Will have a look through there and see whether I can use this.

[nhorton79]

Well. I looked into those cascading dependent combo boxes but it didn't quite seem to work.
They were also on Forms, whereas I'm trying to handle this on the table side using a query (if that makes a difference)

Anyway...it got me really looking into it and it was some slight errors in my syntax that were causing the issues - correct code below:

SELECT [tblContacts].[ContactFirstName] & " " & [tblContacts].[ContactLastName]
FROM tblContacts, tblJob
WHERE (([tblJob].[JobClient]=[tblContacts].[ContactCompany]));

Note: As I have been working through my database I have made some slight changes in table naming conventions since my original post (addition of "tbl")

Cheers for the help though.

Nick

[ItsMe]

...They were also on Forms, whereas I'm trying to handle this on the table side using a query (if that makes a difference)...

I am not following what you are trying to accomplish by "handle this on the table side". It is recommended that all data is accessed via forms. Microsoft Access is a tool to manage data. Access provides a design tool to create applications, applications that provide a user interface. If a user has access to raw data, they have an ability to violate Constraints. If constraints and or Referential Integrity is violated, the idea of managing data goes out the window.

[nhorton79]

Hi ItsMe, sorry about that. It possibly sounded like I was doing everything wrong (and I still might be ). I will be using forms to enter and access data, its just that I am in the early stages of the development and mostly all I have is tables at the moment - I will be adding forms for the entry of data etc, but I am still working on the table side at present creating queries and vba code to restrict data that will be entered into each field.

Should I be just creating blank fields/tables without any data types/restrictions/formats etc and getting the forms to handle the formatting and restrictions? Or am I right in doing this on the table side (and having the forms just receive the data)?

Cheers
Nick

[ItsMe]

It depends on how comfortable you are with VBA and what environment you will be using your application in. If you are going to be using it as a desktop database, I would use VBA. If you are looking to publish with SharePoint, I would be looking at Macros, and special fields in tables to manage constraints, referential integrity, calculations, etc.

I do not understand how to use Access with SharePoint. All I know is there are difference between 2013 and 2010. Regardless of which version you are using, if you are sharing your DB on SharePoint and or over a WAN, use a tools driven approach and employ Macros, special features at the table level, etc. Otherwise, use VBA.

Because I use Access as a desktop application/database, I am bias and will, almost, always advise VBA. I use VBA to manage all constraints, referential integrity, data validation, etc. That is not to say I ignore using the appropriate data types in tables. You want to make sure you pay close attention to things like where text fields are used, how many characters are allowed, which fields can be Integer and which fields need to be Long, etc. Data types in your fields are very important.

It sounds as though you are using lookup fields in your tables. This is usually frowned upon for desktop apps. Create another table, that's what I say. Some might use integers to represent things like Male, Female, Not Provided; Yes, No; Mr., Mrs., Miss, etc. Using constants to represents these types of selections is not a bad idea but, if you place them in a table, you know where they and what their values are. However, constants can be defined in a Standard Module.
http://www.access-programmers.co.uk/...d.php?t=138975

If you are in the beginning stages, it won't hurt to define your entities and how they relate to each other. This video is elementary but may help.
https://www.youtube.com/watch?v=-fQ-bRllhXc

Maybe you can look at this example (sample DB linked below) I uploaded for another member here. Consider what is being done with the Contacts table and then see if you can start to picture an Entity Relationship Diagram for the sample DB. Then, start thinking about how to create an ERD for your project.
.
UniversitySample.zip