Results 1 to 2 of 2

Thread: SQL new starter: Query design

  1. #1
    Join Date
    Jul 2020

    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.

  2. #2
    Join Date
    Nov 2020
    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