Query Problems

[trainsteve]

I have a table of ecards that is populated from a website and includes
receiver, sender, and Team leader. The problem I am having is names can be
entered into the table like this "Tom Thumb", but Team leader is a dropdown
menu that is constant and lists name like this: "Tom S. Thumb". When I make a
query to find all the members of Tom S. Thumb's team, I get everyone except
Tom Thumb, who is member too. Tom Thumb has his own higher team leader, so I
can't change that. How do I get Tom Thumb to be included when I enter "Tom S.
Thumb" as Team Leader?

 

[Steven Chicago,Illinois]

Dear "trainsteve",

Why are you saving the Team Leader Name instead of a index value that can be
used to get the Team Leader Name from a Team Leader Table? Save storage space
and eliminate the chance of spelling errors.

You say you have a drop down for Team Leader, is it driven (getting it's
source data from) from a Team Leader Table?

Maybe you need to look at your database design and do some more work on that
to resolve this problem?

If each Team Leader had a unique(index) number, than you could select based
on that number (no spelling to worry about).

Steve

 

[Daniel]

I have a table of ecards that is populated from a website and includes
receiver, sender, and Team leader. The problem I am having is names can be
entered into the table like this "Tom Thumb", but Team leader is a dropdown
menu that is constant and lists name like this: "Tom S. Thumb". When I make a
query to find all the members of Tom S. Thumb's team, I get everyone except
Tom Thumb, who is member too. Tom Thumb has his own higher team leader, so I
can't change that. How do I get Tom Thumb to be included when I enter "Tom S.
Thumb" as Team Leader?

Steve's advice about table structure is very important. It will save
you much grief in the future.

Following that, the way to structure your query would be something
like (air code...)

SELECT employeeID
FROM tblEmployees
WHERE leadID = forms!dummyFormName!dummyComboBoxName.column(0)
OR employeeID = forms!dummyFormName!dummyComboBoxName.column(0)

The source of your combobox should be a query which just shows the
team leads from the employees table. This could be done with a query
something like (again, air code) - note that this goes in your
combobox's rowsource

SELECT DISTINCT employeeID, firstName & " " & lastName
FROM tblEmployees
WHERE employeeID IN (SELECT teamLeadID FROM tblEmployees)

Here, the teamLeadID is actually an employeeID, and hence points to
one of the entries in tblEmployees. This second query is not
particularly fast, so someone else may have a better suggestion. If
you have a table listing team leads (which you probably should) then
you should use that as your combobox's rowsource. Preferably, that
other table would use the same employeeID field as tblEmployees as its
key, though there could be a good reason not to do that.

Hope this helps,
Daniel