Subquery with date range

[rbennion]

Hey all,

I'm pulling my hair out trying to learn the nuances of Access SQL for a new job and i'm not used to it. I am trying to do a nested query that should be quite simple but everything i try is a dead-end. I have a Count in the original SELECT statement which might be throwing me off. See code below. The first one runs fine, but doesn't allow me to specify for a date range. The second code is with my nested query that will not run, giving me an error stating "The SELECT statement includes a reserved word or an argument name that is misspelled, missing or the punctuation is incorrect". No matter where i move it in the query (after the FROM) it gives me errors.

SELECT tbl1.TranFacName, Count(tbl1.TranFacName) AS NumOfTransfers
FROM tbl1
WHERE tbl1.TranFacName Is Not Null
GROUP BY tbl1.TranFacName;

now the subquery:

SELECT tbl1.TranFacName, Count(tbl1.TranFacName) AS NumOfTransfers
(SELECT tbl1.ArrivalDate FROM tbl1
WHERE tbl1.ArrivalDate BETWEEN #1/1/2015# AND #12/31/2015#)
FROM tbl1
WHERE tbl1.TranFacName Is Not Null
GROUP BY tbl1.TranFacName;

I just need to be able to edit the date range for the NumOfTransfers attribute. Is there just a better way to do this entirely?


Thanks in advance!

[aytee111]

The first one runs fine, but doesn't allow me to specify for a date range

Why not? Does it give an error?

[ranman256]

It looks like you have 2 table queries..
Q1 should pull data in the date range.
Q2, uses Q1 to count.

Q2: select item,count(item) from Q1

[rbennion]

I can't put the ArrivalDate in the original query because its not in the aggregate (Count), right?

[aytee111]

No, just add ArrivalDate as an additional field and select "WHERE" in the totals line.

[rbennion]

HAHAH i'm such a novice. thanks. That worked fine, i just built it in Design View and gave the bird to SQL View. Code looks as follows and runs fine:

SELECT tbl1.TranFacName, Count(tbl1.TranFacName) AS NumOfTransfers
FROM tbl1
WHERE (((tbl1.TranFacName) Is Not Null) AND ((tbl1.ArrivalDate) Between #1/1/2015# And #12/31/2015#))

I want to keep using SQL View cause i want to continue learning that skill but Design View is soo much more user friendly sometimes.

Thanks again