Search Query

[gehrenfeld]

I have a football database that has several tables for stats. I have a form that has 4 unbound text fields. Team, Player, Pos, Exp. You can enter a search criteria in one or multiple fields and it will run a query that returns data from the player table.



SELECT Player.Team, Player.Name, Player.Pos, Player.Exp
FROM Player
WHERE (((Player.Team) Like "*" & [Forms]![SearchF]![Teams] & "*") AND ((Player.Name) Like "*" & [Forms]![SearchF]![Players] & "*") AND ((Player.Pos) Like "*" & [Forms]![SearchF]![Pos] & "*") AND ((Player.Exp) Like "*" & [Forms]![SearchF]![Exps] & "*"));

I have tables by position like QB, DB, REC and so on.

I want to do a query where when I enter something into one of the fields in the search query that not only returns the data from the player table but will also pull the data from their position table. Have no idea how to set that up.

Thanks for the help.

[ranman256]

Your sql is limited to the AND and will only work if you enter something in every box.
You should allow for the option of inputing only 1 box. Then you must cycle thru all boxes to see what got entered an use only that one.

You cant use form boxes in a query if there's nothing in them..so..
Test all controls for a possible filter then build the where clause.

if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value


    'remove 1st And
sWhere= mid(sWhere,5)


sSql = "SELECT * FROM tblCompany WHERE " & sWhere


     'save the sql as a qry or open the sql
set qdf = currentdb.querydefs("qsResults")
qdf.sql = ssql
qdf.close
docmd.openquery qdf.name

you can make queries for each table (like above)
then open them all at the end once you attach the where clause.

[gehrenfeld]

Sorry they are or and not and. typo on my part. Query works fine. But I want to have the stats of that player to show up in the subform. If he is a DB then show his stats as a DB. If he is a QB show those stats. I have several tables by position. In the DB table there is players name and all their stats. When the I enter data into the search form what to also pull the data from the stats table.

Hope I am making sense.

[June7]

Can't build query that dynamically changes the tables to use. Join all the stat tables to the players table and all related info will be available, assuming not more 255 fields.

Can build form/subform arrangement. A form for each position table and place them on pages of a tab control. Subforms would be linked to the main form bound to player table. Records will synchronize.

Stat table for each position because the stats are different? There should be a way to include all stats for all positions in one table.

[gehrenfeld]

These are great suggestion. I went with the combine all tables into one big one.

Thanks