Multiple select query

[rudyz]

Hello Everyone,

I have a table called tbl.Shows. In that table are several columns e.g. WinnerMoneyWon, RunnerUpMoneyWon, ThirdPlaceMoneyWon, etc (all the way to EigthPlaceMoneyWon). Each record can contain values for any or all of those columns. The values are currency values. I am trying to run a query that will return every record where any of the colums has a value within a specified range. On my form I have two textbox's named qMoneyWonFrom and qMoneyWonTo. The values are entered by the user to search the entire table for any record that has a value that matches the specified range. For example qMoneyWonFrom is 1.00 and qMoneyWonTo is 6000.00. I would like to have every row return where the value of any of the cloumns contain a value in that range.

This is the statement I have now:

SELECT *
FROM tblShows
WHERE ((tblShows.Date) Between forms!frmPlayerSearch.qstartdate And forms!frmPlayerSearch.qenddate) And ((tblShows.ShowFormat) Like forms!frmPlayerSearch.qShowFormat & "*") And ((tblShows.NumberofPlayers) Between forms!frmPlayerSearch.qplayersfrom And forms!frmPlayerSearch.qplayersto) And ((tblShows.WinnerMoneyWon) Between forms!frmPlayerSearch.qWinnerMoneyWonfrom And forms!frmPlayerSearch.qWinnerMoneyWonto) or (tblShows.RunnerUpMoneyWon) Between forms!frmPlayerSearch.qWinnerMoneyWonfrom And forms!frmPlayerSearch.qWinnerMoneyWonto) or (tblShows.ThirdPlaceMoneyWon) Between forms!frmPlayerSearch.qWinnerMoneyWonfrom And forms!frmPlayerSearch.qWinnerMoneyWonto) or (tblShows.FourthPlaceMoneyWon) Between forms!frmPlayerSearch.qWinnerMoneyWonfrom And forms!frmPlayerSearch.qWinnerMoneyWonto) or (tblShows.FifthPLaceMoneyWon) Between forms!frmPlayerSearch.qWinnerMoneyWonfrom And forms!frmPlayerSearch.qWinnerMoneyWonto) or (tblShows.sixthPlaceMoneyWon) Between forms!frmPlayerSearch.qWinnerMoneyWonfrom And forms!frmPlayerSearch.qWinnerMoneyWonto) or (tblShows.SeventhPlaceMoneyWon) Between forms!frmPlayerSearch.qWinnerMoneyWonfrom And forms!frmPlayerSearch.qWinnerMoneyWonto) or (tblShows.EighthPlaceMoneyWon) Between forms!frmPlayerSearch.qWinnerMoneyWonfrom And forms!frmPlayerSearch.qWinnerMoneyWonto)) And ((tblShows.Guest1BountyTaker) Like forms!frmPlayerSearch.qBountyTaker & "*") Or (tblShows.Guest2BountyTaker) Like forms!frmPlayerSearch.qBountyTaker & "*" Or (tblShows.Guest3BountyTaker) Like forms!frmPlayerSearch.qBountyTaker & "*" Or (tblShows.Guest4BountyTaker) Like forms!frmPlayerSearch.qBountyTaker & "*" Or (tblShows.Guest5BountyTaker) Like forms!frmPlayerSearch.qBountyTaker & "*"
ORDER BY tblShows.Date;

The result when I run this query is that every row in the table is returned regardless of the value specified in my form.

Any help is greatly appreciated.

 

[dmonney]

Have you thought of storing this information in a table instead of forms? it would be alot easier to manage

 

[rudyz]

Hi dmonney,

I'm not sure I follow your question. The information is in a table. I have a form that I am using only to search the table. Basically its a search form where the user can search for players that have won money. The form calls a query thats run against the table to extract the records that match the search criteria.

 

[neileg]

You have a design problem - your structure is not normalised. Instead of a series of columns for the place money, you should use a related table that has a record that identifies the race, the place and the amount. Your query them becomes so easy.

 

[rudyz]

Hi Neil,

I agree the design is not ideal, but unfortunately redesign is not an option at the moment. The database will be completely re-worked next year and migrated to Oracle. However in the meantime I need to be able to report on money won by players. Each event (record) pays players up to 8 places. I need to be able to query the table for records that match a specific player to a specific money amount. The statement I have shown performs a similar search with regard to bounty takers. It searches multiple columns within the same table for any record that matches a value entered into the search form. This opertion is returning the records as expected. I am using the same logic to search for money won, the only differnce is the criteria for the money search is BETWEEN two numbers and not LIKE a name as used later in the statement.

I'm sure this is possible and shouldnt be difficult, but I am not very experienced writing SQL and cant seem to work out the logic and syntax.

 

[dkinley]

Since the database will be redesigned and timeliness of your results are of the essence ... how about extracting all the data into Excel and then sorting in there?

-dK

 

[neileg]

I suspect the issue is to do with the bracketing of your AND and OR statements. You could try building this query up one field at a time.

Alternatively, you could use 8 slect queries and UNION them together to give you a normalised dataset and work with that.

 

[rudyz]

Since the database will be redesigned and timeliness of your results are of the essence ... how about extracting all the data into Excel and then sorting in there?

-dK

That is an option if I cant get it to work, but hopefully I can sort this out so the report and be generated as needed.

I suspect the issue is to do with the bracketing of your AND and OR statements. You could try building this query up one field at a time.

Alternatively, you could use 8 slect queries and UNION them together to give you a normalised dataset and work with that.

Could you give me a basic example how select queries are "unioned"?

 

[Rabbie]

Could you give me a basic example how select queries are "unioned"?

The Access help files will show you how