list box with multiple criteria

[white_flag]

Hello

I have an table with some records based on [company name], [name of the person] and [date]
and if in one day [15.01.2009] I have to assign more persons to an company [name1; name2; name3]
It is possible to insert this in an list box:
ex:
Company1|Name1;Name2;Name3
Company2|Name4

and the name1,name2 etc... to be connected to an form to see all the detaills from that person?
see attachment

[Imdabaum]

Could you clarify? I am looking at your forms. You have a listbox that is unbound but the row source is tied to the [data] table.

When you click on a day in the calendar, it shows the number of companies that had work done?

Where do you want to add the data? When you double click the listbox, the frmPerssonel pops up. What should this form do?

[white_flag]

that form is to see the details of the person.. but What I try to made from this it is impossible (conform to my research on net ..etc)

I like to have an table with in one column companys and on the second one names of the persons that are assign to that companies. With the list box I can not assign some how to see one time the company name if this one is assign to more then one person.. to result like this
company 1| name1, name2 etc

any idea?

[Imdabaum]

Is the nume column holding the name of the employee or the employee id? If so then we can use that.

Have you considered using a linked combo box? So you select the company and the listbox then updates to show all persons that were assigned to that company?


*Edited*
---Sorry early in the morning--

If you want all the names displayed grouped by the company in one row, then you would most likely have to use some VBA. Create a temporary table that cycles through all records of companies with the associated employee (If nume column fits that need, then use that- so far I see no records tying any employee to the company [data] table)

You would have to modify the temporary table by adding a new field each time you had a new employee assigned to a company. This is not the most efficient way of storing data though. It defies all Normalization guidelines. But I understand that some companies will overlook quality for functionality so if normalization isn't a high priority, that's your way around it. I'd still recommend the combobox though.

[orange]

My suggestion is to create a data model of your tables and relationships. Verify your model against your requirements and adjust accordingly. I don't believe your table and relationships have been designed to meet your business requirement.

[white_flag]

The idea is that all info need to be displayed in(on) one page (click on the date) an then, the table will show on left, company's, on right, peoples. So that it is why the combo is not ok.

1. create an temporary table
2. loop records from that table
3. display results on an second table (form)

[white_flag]

tables:
1. company (name, location, tel ..etc)
2. persons (name, location, tel, foto ..etc)
3. date (CompanyID, personsID)

[company]1---multiple[date]multiple---1[persons]

this is the tables and relations.

those are ok?

[Imdabaum]

The closest thing I can come up with is a crosstab query that shows the employees as column headers and a 1 where the employee is assigned to the rows which show company and date

data--doesn't show properly because it gets trimmed.
CompanyName WorkingDate Fuller, Andrew Last1, Nancy Leverling, Janet Peacock, Margaret
Comp1 1/15/2009 1 1
Comp2 1/16/2009 1
Comp3 1/15/2009 1
Comp4 1/18/2009 1
Comp5 1/18/2009 1

Don't think that is your solution, but maybe it's something you can work with


TRANSFORM Count(Company_Employee_Link.CompanyName) AS CountOfCompanyName
SELECT Company_Employee_Link.CompanyName, Company_Employee_Link.WorkingDate
FROM Company_Employee_Link
GROUP BY Company_Employee_Link.CompanyName, Company_Employee_Link.WorkingDate
ORDER BY Company_Employee_Link.EmpName
PIVOT Company_Employee_Link.EmpName;

Where Company_Employee_Link is a query joining the data table with company and persons tables.


SELECT Company.CompanyName, [persons].[LastName] & ", " & [persons].[FirstName] AS EmpName,
data.WorkingDate FROM Company INNER JOIN (data INNER JOIN persons ON data.personID = persons.personID)
ON Company.CompanyID = data.CompID
ORDER BY Companies.CompanyName;