Making Groups???

[puniverse]

Can someone please give me the best solution to making groups. I have attached the file I am working with to make it easier to understand. What I need is in the project "Test" when it opens there is a spot for company and customer. I want to only be able to select the names that are in the same company. Right now the way I have it set up it shows all the customers in my database? Help would greatly be appreciated.

 

[CJ_London]

Change the rowsource for your customer control to include the following

WHERE ((([Customers Extended].ID)=[Forms]![Project List]![Combo61]));

in the AfterUpdate event of the combo61 control put

Customer.Requery

 

[puniverse]

CJ where do I find combo61. I have looked through the project list and details and cannot find it.

 

[CJ_London]

It is the name of your company control

 

[puniverse]

Oh sorry. That was a typo should be Company. I did what you said but I get an error that says "microsoft access cannot find the object customer" any ideas.

 

[CJ_London]

None at all, I did exactly as I suggested to you and it works, so suggest you recheck you have followed what I said.

You will probably need to split customers from companies because the way you have it set up at the moment, you can only have one customer to one company (both fields are stored in the customer detail table), so not sure why you need to select a customer once you have already selected a company.

You will also see the name disappears once you move to a new line - this is a result of the way you have set your db up. Suggest you change your customer control from a combo to a text box, change your combo61 rowsource to also pull through the customer name and set your customer control source to

=combo61.column(2)

I've returned the db so compare what I have done with what you have done to find the problem you are having.

 

[puniverse]

I dont want to sound dumb but the database you returned is not doing what I need. I want to be able to select only customers that are from the company I select. Right now it shows all customers in the customer list. The reason behind it is that I have many customers at one company. and when I select the company I want to see the list of customers only for that company.

 

[CJ_London]

As I said in my post

You will probably need to split customers from companies because the way you have it set up at the moment, you can only have one customer to one company

As a further explanation, look at your table and you will see you have many records with the same company name, but they are all different records - so different companies

You need one table for companies and another for customers with a link between the two - however for a quick fix, this is what I posted

Suggest you change your customer control from a combo to a text box, change your combo61 rowsource to also pull through the customer name and set your customer control source to

=combo61.column(2)

 

[puniverse]

I am not access savvy at all. Sorry for all the questions but have been working on this all day and have repeatedly read your response. I am doing something wrong with your quick fix so I have decided to add a table for the companys. How will I be able to link the companies table to customers company field. Or do I have to make all the links in projects table.

 

[CJ_London]

Your structure needs to be something like this:

TblCompanies
CompanyID autonumber (PK)
CompanyName text
CompanyAddress text
etc


TblCustomers
CustomerID autonumber
CompanyID long (FK)
CustomerName text
CustomerPhone text
etc

You link the two together on tblCompanies.CompanyID=tblCustomer.CompanyID

So you have one company and many customers

It is called normalisation

here are some links for you to give more ideas

http://www.dummies.com/how-to/content/exploring-database-design-tips.html
http://www.databaseanswers.org/data_models/customers_and_addresses/index.htm

Also, have a look at the Northwind database, it is fairly basic but should help to explain

 

[puniverse]

Ok. So I followed your directions and It worked. I believe I have everything the way I want it. Took me a lot longer than expected but I believe I got it. I still have one small problem if you do not mind looking and letting me know what is wrong. In my customers table I have link the companys table but I cannot seem to get the Name to display instead of the ID. I turned the field value into a combo box but my row source is not working. It either pops up both number and name but then in forms only the number pops up. This also comes back to my original question for project details. When I select the company I only want to be able to select the customers in that company.

 

[CJ_London]

Re rowsource,

you need to bring through the name so something like

SELECT [Customers Extended].ID,  [COLOR=red][Customers Extended].[Customer Name]
[/COLOR]FROM [Customers Extended];

You also only need one ID - not sure whether this is supposed to be ID or CompanyID - I have assumed the former.

Also, not sure why you have a separate query it could simply be:

Select ID, [Customer Name] FROM Companies

But I presume you have your reasons.

 

[CJ_London]

sorry, missed the second part of your post.

This also comes back to my original question for project details. When I select the company I only want to be able to select the customers in that company

Again, not sure why you are using this query as a recordsource,

All it needs is:

SELECT Customers.ID, [First Name] & " " & [Last Name] AS Expr1
FROM Customers
WHERE (((Customers.CompanyID)=[Forms]![Project List]![Combo61]))

I'll also repeat what I said in the 6th post which still applies-

You will also see the name disappears once you move to a new line - this is a result of the way you have set your db up. Suggest you change your customer control from a combo to a text box, change your combo61 rowsource to also pull through the customer name and set your customer control source to

=combo61.column(2)

 

[puniverse]

IF I dont put a COMPANYID in customers how would I link it ?

Re rowsource,

you need to bring through the name so something like

SELECT [Customers Extended].ID,  [COLOR=red][Customers Extended].[Customer Name][/COLOR]
FROM [Customers Extended];

You also only need one ID - not sure whether this is supposed to be ID or CompanyID - I have assumed the former.

Also, not sure why you have a separate query it could simply be:

Select ID, [Customer Name] FROM Companies

But I presume you have your reasons.

 

[CJ_London]

my mistake I'm getting customers and companies mixed up

Your rowsource for your company control should be

Select ID, [Company Name] FROM Companys

 

[puniverse]

Can someone please help. I have finally continued this database and here is my problem. I added another Table name Customers Employees so that I could have multiple employees to one customer. I cannot seem to figure out how to link the two tables so that in my Project details when a customer is selected. My customer employees for that customer automatically show up. Als in the customer details I have added a subform for the customer employees but they do not seem to link together. Please help thanks I have attached the database. Also an explanation would be nice since I have spent 3 days trying to figure it out. Thanks.

 

[puniverse]

Alright so I was able to finally Figure out how to make all my tables work. I have a quick question. I am trying to use an Invoice Report but I cannot seem to figure out why the Text boxes dont link up. In Project Details if you click on the Repair tower project and then hit Print Invoice I cannot seem to have it display the Site or The Site address for that current Project ID. Also when I click on project list it opens to a new record so you have to push the back button. Please HELP I just want my Invoice report to show Customer Site and Customer SIte Address. Thanks.