Need advice on query design... 1

[davidd31415]

Hi,

I have a query that uses the SUM function and I'm wondering if there's a way that I can display more than one field that isn't summed? I get an error since the field is not calculated in the aggregate function.

I'll try to clarify...

I have a Transactions table that has the following information in it:

Test_Request_Number, Employee_Name, Regular_Hours, Overtime_Hours, Total_Hours

I currently have a query that looks like this:

Request Number Reg. Hrs Overtime Hrs Total Hrs

test request #1 x x x
test request #2 x x x

and so on...

The test request number comes from a TestRequests table. The TestRequests table also has a field called ProgramID which references records in the Programs table. The Programs table has a field called ProgamName.

What I would like to do is have the query also show the ProgramName for each test request. Although the Transactions table has many entries, each test request is always related to only one ProgramID. The final query should look like this:


Request Number Prg Name Reg. Hrs Overtime Hrs Total Hrs

test request #1 Prg a x x x
test request #2 Prg b x x x

If it helps in answering, here is the SQL I am using for the query I do have working right now:

SELECT [TestRequests.TestRequestNumber] AS [Test Request Number], Sum([Transactions].[Hours]) AS [Total Regular Hours], Sum([Transactions].[OvertimeHours]) AS [Total Overtime Hours], Sum([Transactions].[Hours]+[Transactions].[OvertimeHours]) AS [Total Hours]
FROM TestRequests, Transactions
WHERE ((([TestRequests].[TestRequestID])=[Transactions].[TestRequestID]) And Not [TestRequests].[TestRequestNumber]='N/A')
GROUP BY [TestRequests].[TestRequestNumber]
ORDER BY [TestRequests].[TestRequestNumber];

Thanks for any assistance,

David

 

[davidd31415]

Microsoft Access 2000, BTW.

 

[PHV]

Something like this ?
SELECT R.TestRequestNumber AS [Test Request Number], P.ProgramName, Sum(T.Hours) AS [Total Regular Hours], Sum(T.OvertimeHours) AS [Total Overtime Hours], Sum(T.Hours+T.OvertimeHours) AS [Total Hours]
FROM (TestRequests AS R
INNER JOIN Transactions AS T ON R.TestRequestID = T.TestRequestID)
INNER JOIN Programs AS P ON R.ProgramID = P.ProgramID
WHERE R.TestRequestNumber <> 'N/A'
GROUP BY R.TestRequestNumber, P.ProgramName
ORDER BY R.TestRequestNumber;

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

 

[davidd31415]

Thanks yet again PHV. I'll try that out tomorrow and hopefully understand inner joins better afterward.

Dave

 

[PHV]

and hopefully understand inner joins better
Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

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

 

[davidd31415]

That explains the different joins well. I see I needed to group by more each field that was not summed.

I now have the table I mentioned above, which looks like:

Request Number Prg Name Reg. Hrs Overtime Hrs Total Hrs

test request #1 Prg a x x x
test request #2 Prg b x x x

I also have another query that I have been using which looks like this:


Prg Name Reg. Hrs Overtime Hrs Total Hrs

Prg a x x x
Prg b x x x

I was hoping I could create a subreport in the second report using the first report to get a report that looked like this:

Prg Name Reg. Hrs Overtime Hrs Total Hrs

Prg a x x x
test request 1 x x x

Prg b x x x
test request 2 x x x

so that ony the test requests associated with a given project are listed under it.

Now I'm realizing that I don't understand sub-reports because when I added the sub-report and set Link Child Fields and Master Fields to Program Name I don't get the results I expected.

My master report ends up showing totals that are no longer correct (hours by program name). The sub report shows all test requests, not just the test requests for the given program. Any advice on sub-reports? Is it possible to do what I'm trying to do with them?

 

[davidd31415]

Also,

I am able to produce a report very similar to what I desire by using the query that I was working on when I first posted this (grouping by project, then request number) but I lose the totals for the entire program- perhaps there's an easier way to just add the subtotals to a report based on that query/report?

 

[davidd31415]

Nevermind, I stumbled across the "summary options" button in the report wizard. I'd like to eventually figure out how to do this on my own (I hate relying on wizards) but for now this looks like it does exactly what I want.