Creating multi relationships between multiple tables.

[Mike_charlie]

Hi all,



Worked with databases in a basic end for a long time but creating one for my company and got everything looking good pretty much other than this one issue. I know the solution is in the back of my head somewhere but I have been struggling with it for days now and need some assistance.

So this database needs 3 tables. 1 for customer business names, a second one that links actual employees at these companies that we talk to. We typically can chat with 3 - 4 customers at each business so would need a relationship between them tables. Finally a project table that basically incorporates all the details about a given project including the business name and the person that we will talk to at that company about a given project. I also have been asked by the boss to put first name and last name separately so I created a query to put the names together for the forms dropdown.

So far I have got the employees linked to the business names no problem. The issue is when I go to select the employee name in the project table it is not selecting only employees from the given business. It is bringing up any and all names.

The query includes the id number of employee, the full name put together and the company name.

Anybody able to help will be much appreciated as currently I am stumped.

[pbaldy]

It sounds like you are describing cascading combos:

http://www.baldyweb.com/CascadingCombo.htm

[orange]

Perhaps

[Mike_charlie]

It sounds like you are describing cascading combos:

http://www.baldyweb.com/CascadingCombo.htm

That look exactly like I thought. Thanks I think I would of lost the last of my hair trying to solve this on my own. I will give it a go and update.

[pbaldy]

That look exactly like I thought. Thanks I think I would of lost the last of my hair trying to solve this on my own. I will give it a go and update.

Happy to help! Post back if you get stuck.

[Mike_charlie]

So it sounds similar to this but it needs to be inside the actual table that the changes can be made and not just in forms. I have tried to take this into affect and just can't fathom it.

What I have so far is this

SELECT [Employee].[employee_id], [Employee].[name] FROM Employee Where [Employee].[business_id] = [business_name] ORDER BY [name];

But this brings back an empty set of data.

[pbaldy]

Most of us never let users work directly in tables or queries, only forms and reports. You have no real control in the table, and to my knowledge you can't filter like that in a table.

[Mike_charlie]

I might just have to put my foot down and have the people be annoyed by it. It is a shame but they will get over it.

[Mike_charlie]

Yep fully decided on this, turning off table access for users and setting up cascading combo using macros. Thanks for the help

[ssanfu]

Be aware that "Name" is a reserved word in Access and shouldn't be used for object names. "Name" of what? A project? The company pet?
If the name of employee, you should have "FirstName" and "LastName" fields (or fields named similar). It is easier to concatenate strings rather than split them.

[pbaldy]

Yep fully decided on this, turning off table access for users and setting up cascading combo using macros. Thanks for the help

No problem. I think you'll be happier in the long run. Like I said, you have a lot more control over what the user sees and can do in forms/reports. It's a free-for-all if they get into the tables directly.

[orange]

I agree with pbaldy totally. With direct access to tables the unfamiliar can hit the wrong key or combination or more and screw things up. But a disgruntled employee or malcontent can really raise havoc beyond belief.

The question to ask is "How important is good, accessible, reliable data/info to your business? For a viable business that suggests managing the data as a resource; and that says managed access to the data/database via Forms.

I might just have to put my foot down and have the people be annoyed by it.

Hmmm, I think that is the job of data management/info management/database management. I'm sure the finance people don't give everyone access to the cash drawer. They manage finances with procedures and policy. The data side doesn't always get that respect, but more and more business is recognizing the value of the data to their business.

For certain "they will get over it".
Good luck with your project.