A follow on question - multiple queries?

[addy]

OK, please stay with me on this one!

I need to do a report on the information I have in my database and I am struggling to decide how best to provide the record source for the report.

I have ProjectCodes, each of which can have more than one Contractor assigned to them. For each contractor, they are given a budget, and each budget can be approved or not.

For my report, I need to be able to display a count of the number of ProjectCodes a specific Contractor has been assigned to (as an example, let's call it ACME). I then need a count of how many of ProjectCodes ACME have been assigned to where they have been given a budget, and a count of how many ProjectCodes ACME have been assigned to where they have also been given a budget and where this budget has been approved.

The first part of this (Count how many ProjectCodes ACME is assigned to) is covered by my following query:

SELECT Count([01tblProjectControl].ProjectNumberCode) AS CountOfProjectNumberCode
FROM 01tblProjectControl INNER JOIN 10tblProjPropEstimateOriginalEQs ON [01tblProjectControl].ControlNumber = [10tblProjPropEstimateOriginalEQs].ControlNumber
WHERE ((([10tblProjPropEstimateOriginalEQs].Contractor)="ACME"));

However, in order to get the next two 'Counts', it seems to me I need to use two more queries as I have tried to do it all in the same query and it doesn't seem to work.

I will then have the problem of trying to create a report based on 3 queries and I am not sure how to do that!

The other way I thought of doing it was creating some VBA code which would run series of SQL queries (similar to the one above) to obtain my values, insert these into a temporary table using an SQL statement and then base my Report of that table.

Does anyone have any thoughts on this? The VBA / SQL method seems clumsy but how else am I going to create my report if I need multiple queries to provide my data? Or, can I obtain all the 'Counts' I need from one query?

Tek-Tip gurus - over to you.

Thanks.

 

[PHV]

Why not letting the report doing the math in each group level ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[addy]

PHV

Thanks for your help. Do you mean I should use the SQL SELECT statement above as the datasource for an unbound text box on my report?

I have tried that but all I get is #Name? displayed in the text box?

 

[addy]

OK

I have created a solution to this but I feel it could be better.

I created a table which I will use as the recordsource of my report. I then have a button which populates the table using a series of SQL statements which perform the count calculations etc. as described above. There are 34 calculations in total and these are added to the table using the INSERT INTO statement.

This unfortunatley means that each value gets added to a new row in the table and so in my report, I use the DMax function on each unbound text box so that it finds the record which contains my value and displays it in my report.

So, my report works OK. But could I have done this a better way?

Many thanks.