Need Help with Two Queries

[thejabronisayz]

Hi everyone, I'm new to access and I'm having trouble with two queries that I hope you can help me with.

The first one seems very simple to me, but for whatever reason I'm getting an error message. One of the tables I have basically has a list of a lot of people and their addresses, and I need to return the number of people who live in a certain state. So when I make the query, I pick that table, click "State", then go to totals and select "Count," and then enter in the state in the criteria box. When I do this, Access gives me an error message saying its a "Data Type Mismatch." I checked, and the field "State" is a Text data type, so I don't know what the issue is.

My other problem is more complex. I need to basically make a query in which I build my own formula to subtract two numbers from each other. For instance, I have one table with customers X, Y, and Z. Each customer made for instance $5,000 dollars, but spent $2,000 dollars. I need to make a query that will return each customer, X Y and Z, with the difference between how much they made and how much they spent. I know how to build a function and I've used the sum function before. I have tried just taking How much money they made, and subtracting how much money they spent, but I need to put an expression (such as Sum) before the subtraction, and I don't know what to put.

Any help would be great, thanks.

 

[statsman]

In your first question:

I assume you are using the Query wizard. If so, open the query in design view. Click the sums button on the top row (looks like the Greek letter sigma). In your field for State enter the following in the criteria line:
[Enter State] square brackets required.
In the Total line select "Count"

For your second question:
Again, I am assuming you are using the Query wizard.
Open the query in design view
Go to the first blank field to the right and enter the following:
Net:Sum([amount made]-[expences])

 

[thejabronisayz]

Hello,

I really appreciate the swift reply; however, all of those steps were indeed my initial thought processes and they are not working.

When I enter in [State] into the criteria box, in then prompts me to "Enter Parameter Value." I have no idea what to enter into the box. If i enter in the state again, or leave the box blank, it still gives me the same answer, which is the wrong answer.

For the second problem, that is precisely what I'm doing and it's giving me garbage. I can't even for the life of me figure out how it's getting the numbers it is.

If you don't know what's going on then that's fine, I appreciate the help anyway!

 

[CameronM]

Going back to your original post, the reason you are getting and datatype mismatch is because Count(State) returns a number, but in your criteria you are adding the text value of the State's name. I suspect what you really want is not the count of how many States are in your table, but rather the count of how many people come from that state. You get this by counting the rows using something like PersonID, or RowID - if you have something like that in your table.

You call still have a criteria [Enter State] on the state field, but instead of Count, the State field should have GroupBy in the 'Total' row.