Access Queries

[njwilli3]

Hi ive wrote a few queries in sql and they include nested select statements. The queries work fine, but I would like some way of displaying all these queries on the same screen, in maybe a nice form or report (not just 1 datasheet for each query) - like a summary page

Each query returns a datasheet with a single figure e.g. CountOfCompanies as the column title and 1562 on the row directly beneath it.

I can create a form using a wizard to bind a particular query to it and display the figure but how do bind other query results to other textboxes on the same form (if done using a form).

Any ideas or other methods would be hugely appreciated.

[Br1an_g]

You can use subforms (check help section in Access)

or you can use code:

dim intVariable1
dim intVariable2

intvariable1 = DLookup("FieldName", "TableName", "Criteria - if needed")

txtControl = intVariable1

or you could use:

Dim dbs As Database
Dim rst As Recordset
Dim varRecords As Variant

'set database to current
Set dbs = CurrentDb

Set rst = dbs.OpenRecordset(QueryName)

varRecords = rst.GetRows(rst.RecordCount)

txtControl = varRecords(0, 0)

just a couple of examples..there are other ways, im sure someone will be along shortly to suggest them

[Ecniv]

Use subqueries....


Its similar to the saving of a query then referencing it, but its all in one.

Example:

Select [qrySub].[F1], [qrySub].[F2] From
(Select [T1].[F1], Count(T1.F1) as [F2] From [T1] GROUP BY [T1]) as [qrySub]

So you make up the sub queries that do the counts attached to a main table or with IDs. Copy the Sql statement into a main Sql Query renaming similar to the above bit.
Repeat for each sub query, naming differently of course!
Then change the view of the query back to design and if you have done it right you can see the sub queries as tables.
Grab the fields as required and (save and) run as required to test.

Its another way... depends on how you count and how much data you are using.

[Br1an_g]

Use subqueries....

is this workable in all Access versions? It doesnt seem to like it in Access 97

[Br1an_g]

Just tested in Access 2002 and it worked fine.

Sub Queries do not work in Access 97.

[njwilli3]

Firstly Brian,
Tried using the 2nd lot of code but I cannot initialise the dbs variable as a database type on line:

Dim dbs As Database

There is no type called Database, the next best thing is DataAccessPage??
So I tried the subforms which work ok.

Secondly Ecniv,
Not sure exactly what you mean, 1 of my queries looks like the following:

SELECT Count([Delegate].[ID]) AS [Total No Of Male Beneficiaries Helped]
FROM Delegate
WHERE Delegate.ID in ( SELECT Delegate.ID FROM Delegate INNER JOIN Activity ON Delegate.ID = Activity.ID
WHERE ((Delegate.Gender)="Male")
GROUP BY Delegate.ID, Delegate.Client, Activity.Activity
ORDER BY Activity.Activity;
);

Do either of you know how I can speed up my queries as they take around 30 seconds to open. I have over 1500 records??

[Br1an_g]

Firstly Brian,
Tried using the 2nd lot of code but I cannot initialise the dbs variable as a database type on line:

Dim dbs As Database

There is no type called Database, the next best thing is DataAccessPage??
So I tried the subforms which work ok.

you probably need to add the relevant reference file:

whilst in your vba module-code area

click Tools>References

in my current 97 database i have:
Visual Basic for Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.51 Object Library - this is the one needed for database declarations.
Microsoft Office 8.0 Object LIbrary.

[Ecniv]

Firstly Brian,
Secondly Ecniv,
Not sure exactly what you mean, 1 of my queries looks like the following:

SELECT Count([Delegate].[ID]) AS [Total No Of Male Beneficiaries Helped]
FROM Delegate
WHERE Delegate.ID in ( SELECT Delegate.ID FROM Delegate INNER JOIN Activity ON Delegate.ID = Activity.ID
WHERE ((Delegate.Gender)="Male")
GROUP BY Delegate.ID, Delegate.Client, Activity.Activity
ORDER BY Activity.Activity;
);

Do either of you know how I can speed up my queries as they take around 30 seconds to open. I have over 1500 records??

1500 records? Is that all?????

try:

SELECT Delegate.Gender, Count([Delegate].[ID]) AS [Total No Of Beneficiaries Helped]
FROM Delegate LEFT JOIN Activity ON Delegate.ID = Activity.ID 
GROUP BY Delegate.Gender
HAVING (not (activity.id is null))

Isn't that what you want? Not sure...
You can add to the having bit or put in a where clause.
Not sure why your delegate.ID should be joined to Activity.ID...? Have you got the right fields?