-
SQL new starter: Query design
I am completely new to SQL though I have been using MS Access for many years so have seen, but never used, the SQL design feature that is automatically generated.
I would now like to create a query that refines data from a large number of fields by searching for a single name in all those fields and then refining the result further by restrictinbg the date range. So far I have managed both parts separately but cannot combine them to produce the final date-specific range. My SQL goes like this:
SELECT Concerts.ConcertDate, Concerts.Choir, Concerts.[Concert hall/studio], Concerts.Place, Concerts.Conductor, Concerts.Service, Concerts.Works, Concerts.Soloists
FROM Concerts
WHERE (((Concerts.B2.Value) Like "*" & [Singer's name?] & "*")) OR (((Concerts.[B/B1].Value) Like "*" & [Singer's name?] & "*")) OR (((Concerts.B3.Value) Like "*" & [Singer's name?] & "*")) OR (((Concerts.B4.Value) Like "*" & [Singer's name?] & "*")) OR (((Concerts.[T/T1].Value) Like "*" & [Singer's name?] & "*")) OR (((Concerts.T2.Value) Like "*" & [Singer's name?] & "*")) OR (((Concerts.T3.Value) Like "*" & [Singer's name?] & "*")) OR (((Concerts.T4.Value) Like "*" & [Singer's name?] & "*")) AND ((ConcertDate) Between [Start date] And [end date]);
When I run this I can specifiy any part of the Singer's name just once and I can also specify the date range. What results is a correct list of all the instances of that singer from all the relevant fields, but the date range is ignored and I get all the instances from before and after the dates I specify.
Sorry that I am just a naive novice but what am I doing wrong?
I might as well carry on because I have another need. Eventually I want to create a report based on the result of the above query (when I've got it right) and I want to be able to put the resultant singer's name in the report header. How do I discover what name has been selected? It would be easy if I knew the full name at the start but one of the points of this is to be able to get a name from partial information.
-
Data structure is not normalized and causes difficulty.
Access throws in a lot of unnecessary parens and that can make it hard to read. Use of .Value is not needed. Simplified via SQLView window:
SELECT * FROM Concerts WHERE ConcertDate BETWEEN [start date] AND [end date] AND ([B2] LIKE "*" & [Singer name] & "*" OR [B/B1] LIKE [Singer name] OR etc);
Really should not use punctuation/special characters in object naming.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|