Combining two queries.

[yeppy12]

I have a query called Spans1 that calculates the difference between dates:

SELECT [Rasp].[Issue Number], [Rasp].Issues, [Rasp].Status, [Rasp].Date_Received, [Rasp].Date_Resolved, DateDiff("d",[Date_Received],[Date_Resolved]) AS Span
FROM [Rasp]
WHERE ((([Rasp].Status) Like "*" & [Enter status]));

I have another query called Average_Span that gives me an overall average number of days between the dates in the above query:

SELECT Format(Avg([Span]),"0.0") AS Expr2
FROM Spans1;

Is there a way to combine the 2 so that I get the spans and an overall average? Any help is greatly appreciated.

 

[ajetrumpet]

Is there a way to combine the 2 so that I get the spans and an overall average?

Yeppy, if you want both data in one query, then you're gonna have to see the average number in every record that is returned by the first query. How about using the DAverage() function for an additional column in your first query? Like this:

SELECT [Rasp].[Issue Number], [Rasp].Issues, [Rasp].Status, 
   [Rasp].Date_Received, [Rasp].Date_Resolved, 
      DateDiff("d",[Date_Received],[Date_Resolved]) AS Span
         Format(Avg([Span]),"0.0") AS Expr2

FROM [Rasp]

WHERE ((([Rasp].Status) Like "*" & [Enter status]));

See if you like the outcome of that...

Reference: You do not have to create a new query in order to pull data from a temporary column (in this case, the column called Span)

 

[yeppy12]

It does not work. For some reason, Access keeps giving me the error that says I did not include "Issue Number" as part of an aggregate function.

 

[ajetrumpet]

Sorry Yeppy! I said the DAVG() function, but I wrote an aggregate instead. Oops!

Write this instead:

SELECT [Rasp].[Issue Number], [Rasp].Issues, [Rasp].Status, 
   [Rasp].Date_Received, [Rasp].Date_Resolved, 
      DateDiff("d",[Date_Received],[Date_Resolved]) AS Span
         Format([color=red]DAvg[/color]([Span]),"0.0") AS Expr2

FROM [Rasp]

WHERE ((([Rasp].Status) Like "*" & [Enter status]));

Sorry about that! Tell me what you think of the outcome. It won't be pretty, but at least it will give you the numbers you're looking for.

 

[yeppy12]

Well, I cannot seem to get the query to work. I keep getting a syntax error that says something is wrong with the select statement or that I have not entered enough arguments for the DAvg function.

 

[ajetrumpet]

Yeppy, the following should work just fine. Give it a try:

SELECT [Rasp].[Issue Number], [Rasp].Issues, [Rasp].Status, 
   [Rasp].Date_Received, [Rasp].Date_Resolved, 
      DateDiff("d",[Date_Received],[Date_Resolved]) AS Span
         Format([color=red]DAvg("Span", "Name of THIS query")[/color],"0.0") AS Expr2

FROM [Rasp]

WHERE ((([Rasp].Status) Like "*" & [Enter status]));

You should see the redundant data now.

 

[yeppy12]

Adam,

Thanks so much for your help. It works now...I think the problem was I had a comma in the wrong spot.