Unrelated Queries from different tables

[saraann422]

I'm starting with this SQL query in Access and will add on if I get what I need out of this.

This Union All query looks like this when it's run:

Name Totals
QC'er #
TimeAway #

I want to be able to use these totals in calculations in a report and to format them there. So, is there a way to make the query look more like this?

QC'er TimeAway
# #

THANK YOU!!
Sarah

SELECT 'QCd' as [Name], Count([QC Information].[QC'er]) AS [Totals]
FROM [QC Information]
HAVING ((([QC Information].[QC'er])=[First Last]) AND (([QC
Information].[Today's Date])>=[StartDate] And ([QC Information].[Today's
Date])<=[EndDate]) AND (([QC Information].[Case
Status])="Expedite/Re-QC" Or ([QC Information].[Case Status])="Expedite"
Or ([QC Information].[Case Status])="Re-QC" Or ([QC Information].[Case
Status])="New"))

UNION ALL SELECT 'TimeAway' as [Name],
Sum(IIf([Employee]=[Employee],((([Attendence/CYA]!Hours*60)+[Attendence/
CYA]!Minutes)/60),0)) AS Totals
FROM [Attendence/CYA]
HAVING ((([Attendence/CYA].Employee)=[First Last]) AND
(([Attendence/CYA].[Date of Absence])>=[StartDate] And
([Attendence/CYA].[Date of Absence])<=[EndDate]));

 

[dhookom]

I think you would create a crosstab query from your final query. I would definitely change the alias of [Name] to a name that wasn't also a property of everything in Access.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Typed, untested:

SELECT Count([QC'er]) AS QCd
,(SELECT Sum(((Hours*60)+Minutes)/60) FROM [Attendence/CYA]
   WHERE Employee=[First Last] AND [Date of Absence] Between [StartDate] And [EndDate]
) AS TimeAway
FROM [QC Information]
WHERE [QC'er]=[First Last] AND [Today's Date] Between [StartDate] And [EndDate]
 AND ([Case Status] IN ('Expedite/Re-QC','Expedite','Re-QC','New')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[saraann422]

I tried the cross tab query, but it doesn't come over into the reports like that. I need to be able to manipulate the numbers and move them around the page.

I tried the new query, but it's returning with an error, "Missing ), ], or Item in query expression '[QC'er]=[First Last] AND [Today's Date] Between [StartDate] And [EndDate] AND ([Case Status] IN ('Expedite/Re-QC', Expedite','Re-QC','New')'.

Thanks for everyone's suggestions! I'll try whatever it takes to get this right. I'm very new to Access.

 

[lespaul]

add a paren to the end:

'New')[b])[/b]

that's where it's missing....
HTH

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[saraann422]

FABULOUS!!!! Ok, this may be a silly question, but would I add the next Select for my query? Say I wanted to grab the field Total from a Table called Cases.