help a newbie.. conditional search in table

[BlueAvatar]

i have three tables, one has a list of companies and staff
like so:

companies  staff
comp1      staffmember1
comp1      staffmember2
comp1      staffmember3
comp2      staffmember1

etc...
one has a list of companies (same ones as the comp & staff)

comp1
comp2
comp3

etc..

and the other is another is like this

company                 user     problem
(listbox from table2) (listbox)  (text)

i managed to get the company listing in the third table easily, but i cant get the user listbox to load the users from the company that has been selected in the previous field.

is that a clear description?
i think its some kind of SQL query that i need, but i cant seem to get the syntax correct so its not working.

 

[tomschafer]

One problem would be that you are trying to reference a ListBox's value directly, which cannot be done directly. You would need to use some VBA code to be able to do this. To make things easier for you- a "newbie"- change your list boxs to combo boxs. It will be much easier. So then you can use a query for your User combo box like...

select
staff
from
tablename
where
companies=CompanyComboBox

However I see some other problems. Your schema as posted is Normalized- meaning you are doubling up on data. i.e.
If Table1 has companies and staff. And Table2 only has companies you are wasting resources. I see this a lot of this when trying to help a beginner understand the theory behind a De-Normalized Relational Database.
What I believe you are trying to do is create a list of unique companies for use in forms/data entry. Now you have 2 options.
Option 1: Instead of referencing Table2.Companies in your db to get that unique list, use a query-(select distinct companies from table1). This uses a little bit more disk space than the following example but it is relativley easy to understand.
Option 2: Use a completely different schema. i.e. Table1 (companyid long integer, staff text(255)), Table2 (companyid auto number, company text(255)). Then you have a join between the 2 tables where companyid is equal in each. Example:

Table1
companyid staff
1 john doe
1 jane doe
2 bob smith

Table2
companyid company
1 microsoft
2 bob smith's pizza

Meaning john doe and jane doe work for microsoft and bob smith own's his own pizza place.

Option2 is a little trickier but it's what you will see when playing with bigger db's. Like SQL Server.

Hope I didn't confuse you too much. Good Luck!!!

 

[BlueAvatar]

Thanks very much mate,
that helps alot. I know a bit of VB, so if i can figure out where to put it i should be ok. and yes, i know that i was doubling up on data, but i hadnt thought of using a query to get it out.. thats a good idea.

 

[BlueAvatar]

ok, i made a query and its a heaps better way to get alist of companies. now my problem is that i dont know how to find out the name of the combo box that is in my "Problems" table. the field name is "Company"

SELECT [Companies and Staff].Staff
FROM [Companies and Staff]
WHERE ((([Companies and Staff].Company)=[Problems].Company))
ORDER BY [Companies and Staff].Staff;

i dont know where all those brackets came from. i didnt put them in. access decided it needed them and added them itself. what happens when i run this is i get prompted to enter a value for problems.company... does that indicate a syntax error?

thanks.
-Blue

 

[tomschafer]

I don't think this is possible inside a table, as the join ((([Companies and Staff].Company)=[Problems].Company)) is trying to grab all records from [Companies and Staff] where records in [Problems] are equal. And there is no way of creating the join on the new record. You can definately do this with a form though.

 

[BlueAvatar]

yeh, i found that out the hard way. thanks though. i havent quite figured out how to do it on the form yet. I'm working on it.
-Blue

 

[tomschafer]

Add a combo box to your form right-click on it and go to properties. Then click the data tab, click on the RowSource field, at the end of the field will be a button with "..." on it, click that to start the query builder.

 

[BlueAvatar]

yes, i got that far.. can i just use
SELECT [Companies and Staff].Staff
FROM [Companies and Staff]
WHERE ((([Companies and Staff].Company)=[Problems].Company))
ORDER BY [Companies and Staff].Staff;

somehow i doubt it. i dont know how to access the selection of the company from the other control. is it something like company.selection ?

 

[tomschafer]

Try this, Note- Change the blue to match the names of the controls on your form....

SELECT [Companies and Staff].Staff
FROM [Companies and Staff]
WHERE ((([Companies and Staff].Company)=[Forms]![FormName]![ComboBoxName]))
ORDER BY [Companies and Staff].Staff;