Ms Access question

Duff Man Jr.

This is probally a simple question but its driving me mad.

I have a table called applicants with ref as the primary key. I need a query to count the number of records.

Using {Count: (SELECT Count(ref) FROM Applicants)} There are 100 records, but the query returns 100 rows with '100' in each.

How do I get 1 field with '100' in it?

Thanks

Malice

It's hard to see from your post exactly what your SQL Query is so double-check your syntax.

I tried the following on an Access database and it worked fine:

SELECT COUNT(userID) AS TotalUsers FROM Users

stevire

Duff Man Jr. wrote: »

This is probally a simple question but its driving me mad.

I have a table called applicants with ref as the primary key. I need a query to count the number of records.

Using {Count: (SELECT Count(ref) FROM Applicants)} There are 100 records, but the query returns 100 rows with '100' in each.

How do I get 1 field with '100' in it?

Thanks

Is it not just (SELECT COUNT(*) FROM Applicants). COUNT function only counts the records in the database... Are you looking for the query to return rows 1 - 100 sequentially?

If so, do something like: (SELECT ref FROM Applicants Order By ref)

Duff Man Jr.

Ok I was on the toilet there so I had a good long think about it. I am a access noob.

That is what I put in the Field row in the design view for a query didn't touch any sql.

Here it is better explained maybe. I need to make a report which says "There are ** applicants". What do I put in the control source for **.
** is the nummber of records in 'Query1'.

Thanks

Malice

stevire wrote: »

COUNT function only counts the records in the database...

Just in case there is confusion with this, the COUNT() function returns the number of records calculated by a query expression, not the number of records in the database. I assume that's what stevire meant .

Thinking about this again, I think the OP basically wants a query similar to what I posted which should work fine once the field and table names are changed.

Duff Man Jr wrote:

the query returns 100 rows with '100' in each.

I'd like to see the SQL query that you are using because an aggregate function like COUNT should just return a single value.

stevire

Create a query... Right click the tab in the main window and go to SQL view. Then you can enter the SQL definitions above. Go with the original one you had:

SELECT Count(ref) FROM Applicants

stevire

malice_ wrote: »

Just in case there is confusion with this, the COUNT() function returns the number of records calculated by a query expression, not the number of records in the database. I assume that's what stevire meant .

Oh of course was just referring to my query : SELECT COUNT(*).....

Malice

Duff Man Jr. wrote: »

Ok I was on the toilet there so I had a good long think about it. I am a access noob.

That is what I put in the Field row in the design view for a query didn't touch any sql.

Here it is better explained maybe. I need to make a report which says "There are ** applicants". What do I put in the control source for **.
** is the nummber of records in 'Query1'.

Thanks

So you were sitting and thinking while you were shitting and stinking?

I've never dealt with Access reports so I can't help you with there unfortunately.

Duff Man Jr.

Got it sorted, thanks a lot.

Malice

Duff Man Jr. wrote: »

Got it sorted, thanks a lot.

So what was the answer in the end?