Quering and re-Quering aint working !

[irfanali]

This is an issue that i was unable to find a resolution to..The Logic seems simple but am unable to translate it into Practicality..
Well the issue is as follows :

I have a table called "Order Data" with data containing the following columns
- Team Member Name
- Order Amount
- Order Date
- Order Number

These 4 columns are updated on a daily basis.The Table currently has close to 500,000 line items.

However, there are only 4 teams and the Team members in these teams.
I have stored them in a table called "Team & User Names".
Now the challenge is when the user is trying to extract "Order Data" based on Team name.

Well the Logic is simple,

Step 1 : Get the desired 'Team Name' from the user.

Step 2 : Query the Team name on the table, "Team & User Names" to get the Team member names from the Desired 'Team'

Step 3 : Run another query based on "Team Member Name" Results, to get all the Order Details booked by these Team Members.

This is the Logic as simple as it can get..

I am not able to get past Step 2, i am not able to run the Step 3 Query !!
Please help as i ve spent all my energies in permutation and combinations for a resolution, and took my system to the verge of a potential Crash as well

Thanks & God Bless

 

[DrSnuggles]

If like you say the Team Member Name in the table 'Team & User Names' are the same and used in table 'Order Data', I would do the following:
1. Let them choose from a drop down - this way you qualify that the name is correct.
2. You can refer to this drop down in yout query in Step 3.

Advice though for the future, best to use numeric IDs instead of text ones. That way if a Team Name changes it won't matter as referential integrity will hold.

Hope that helps!

 

[irfanali]

Thanks a ton...DrSnuggles..got the Logic into Reality Translated !! Cheers !