I just started a new job as a dbase admin intern, and due to the database being very large, it is better to do queries in design view, rather than hardcoding the sql.

That being said, I cant seem to figure out how to whittle this query down properly using subqueries.

So, I am trying to find the users who have an invoice in Quarter 1 of every year since 2012.

I created 4 subqueries containing the user ids that have an invoice for 2012,13,14,15.

After that, I want to now create a main query where it shows the IDs for every entry that has an invoice for every single Q1 of those years.

So, what I did was created a one to many join from my main table to every single subquery and then had a different parameter for each subquery where the condition "is not null"

I keep coming up with zero entries though, and I know that there are foundations that have invoices for every month of every year, so there is something wrong in my execution.

Any ideas as to what I am doing wrong?

EDIT - the text of the query is

SELECT dbo_Name.ID 
FROM (((dbo_Name LEFT JOIN Q_invoiceQ1_2014 
  ON dbo_Name.ID = Q_invoiceQ1_2014.ST_ID) LEFT JOIN Q_invoiceQ1_2013 
  ON dbo_Name.ID = Q_invoiceQ1_2013.ST_ID) LEFT JOIN Q_invoiceQ1_2012 
  ON dbo_Name.ID = Q_invoiceQ1_2012.ST_ID) LEFT JOIN Q_invoiceQ1_2015 
  ON dbo_Name.ID = Q_invoiceQ1_2015.ST_ID 
WHERE (((Q_invoiceQ1_2014.ST_ID) Is Not Null) 
  AND ((Q_invoiceQ1_2013.ST_ID) Is Not Null) 
  AND ((Q_invoiceQ1_2012.ST_ID) Is Not Null) 
  AND ((Q_invoiceQ1_2015.ST_ID) Is Not Null)) 
GROUP BY dbo_Name.ID;
  • 1
    The text of the SQL would be helpful. – Vérace Jun 9 '15 at 20:47
  • You can delete the SQL text - I put it (formatted) into the question. You should take the time to learn how to use the formatting tools (above the question). Also, take the tour and take a look at the "help us to help you" blog - both at the bottom left of the page. – Vérace Jun 10 '15 at 14:52
  • I was trying to work it, I am new with this formatting system, and I was editing as I went by. Is there a smoother tutorial than the one given by stack exchange as to how to properly format? – Pewkie Jun 10 '15 at 14:53
  • Click on the question mark to the right of the window when you are editing for help. Google "markdown html" - and see here. – Vérace Jun 10 '15 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.