Hi guys,

i have database issues again..

i want to do a count.

i have a list for example


ID countofID
1 20
2 5
3 9
4 7

id is a field in a table
count of id is how many people have that number as their id.

I'm trying to run a report based on this query but i want the report to do a count of countofID. so in this example the total should be 41.

Everytime i try count(countofID) on the report it shows up either nothin or error.

the other problem i am having is i am basing a query on a form. when the user enters a number i want the query to change the where claus.
So far when the user presses a button, the button assignes the text values of 3 text boxes to "<20", ">20 & <50" ">50". then in using the expression builder i select the form, the correct text box, and teh text value and it enters it in for me.

This shows up a blank query. but if i type "<20" into the design of the query rather than using the form. the query shows results.

Any thought?

select sum(countofid)
from table

I don't know what the expression builder is.

The word you're looking for is SUM not COUNT.

Not being too clear.

count of id is how many people have that number as their id.

select count(countofID)
from table

I'm trying to run a report based on this query but i want the report to do a count of countofID. so in this example the total should be 41.

select sum(countofid)
from table

If in your example countofid is a computed column, which it looks like it is,
then you might want to do.

Select Sum(Count(ID)) AS TotalIdCount
From Table


last edited by mooby at 21:22:24 24/Jul/08

I don't know what the expression builder is.

expression builder is a GUI to query.

"<20"

dont use the quotes. quotes are for value types of varchars (strings).

Eg

Select Member
From QGL
Where Member = "mooby"

For numbers use

Select Member
From QGL
Where PostCount < 100

Changing the WHERE clause of a form (Access, right?)...

To change the query the form is based on you could change the RecordSource property to a different query, or define a SELECT statement.
e.g. add a button, cancel the wizard, go to the button's properties, go to the event tab, go into the "on click" event, click the "..." button on the side. From there you could probably use expression builder to do what you want, or you could run some VB instead (e.g. Me.RecordSource = "SELECT * FROM table") Naturally you'll want it to bring up the same field names as what the form is using.

Alternatively if the form is based on a broad query you could add filters. A filter is essentially a WHERE clause that gets slapped onto the form's RecordSource query (without the word WHERE - e.g. "[ID] = 1"). Have an unlinked text box and a button that runs some VB like:

Me.Filter = "[ID] > " & Me.TextBox1
Me.FilterOn = True

last edited by Chancre at 22:03:56 24/Jul/08