Need help!

[registan]

Hi all!
I need some help about queries. If anyone can help, please tell me, so that i can send him/her my database.

Thank you!

 

[Kryst51]

You can get help here, but post a specific question, that way people have something to help you with. Or at the very least give us some clue as to what your problem with the query is. You can always post your db here with a description of what's wrong. Just zip it up first.

 

[registan]

Thank you Kryst51 for the answer.
You know, im not english, that's why i didn't post my question directly.
OK, ill try to explane.
I have a table named "DATA", and i want to pic up some data from it. The table DATA has 4 columns, name, age, nationality and sex. So, i want to create a query that pics up each column with different criterias. What should i do?

Waiting for the answer, thank you.

 

[Kryst51]

Do you know what the query builder is? in the queries section of the database, you can either create a query in design view, or use a wizard. Try creating a basic query first, pulling in your table, and adding the fields you want into the Grid.

Also check out the following sites from Microsoft.com concerning queries, starting with the basics, and building up from there. Once you get the basics of building a query, you can post back on this forum with specific question. Also, You can try doing a search on the internet for queries, I don't know if there are any sites that might be in your language, but you can always look (Being English, I have never had to search for nonEnglish sites)

Also, these tutorials are for Access 2003, the site also has tutorials for Access 2007.

Queries I: Get Answeres With Queries
Queries II: Limit your Results and Calculate Values
Queries III: Create parameter queries that prompt users for input
Queries IV: Working with complex criteria

 

[Kryst51]

Oh, yeah, welcome to the forum, and if my answer is too vague, take a look at the first part, then maybe ask a specific question from there, so I can try to explain more to what you don't understand.

 

[registan]

Thank you,

For example, I have data like this:

Name Age Nationality Sex
Dave 23 English Male
Kolya 20 Russian Male
Olga 23 Russian Female
Jack 20 English Male


I want to create a query that shows me how many English, how many Russian, how many male and how many female are there in the table. Like this:

English Russian Male Female
2 2 3 1

 

[Kryst51]

OK, I believe what you are looking for is a cross tab query. I have never done these, so I can't help you with it, but search this forum for crosstab queries, and I am sure that you will find help.

Also, someone else will likely read this, and might jump in and give you some direction. Also, I am sure the tutorials I linked to in Post #4 will get into those.

 

[Kryst51]

Hmmm, I've read up quickly on crosstab queries, and am not entirely sure that is what you need.

So, You can either create two queries, one that will count gender, one that will count nationality, then show them in a report or display them in a form, OR create one query If what you want is say:

There are 4 females, 2 are English, 1 is Russian, and 1 is French
and 3 males, 1 is English, 2 are Russian

Then the query would look like this:

Female, English, 2
Female, Russian, 1
Female, French, 1
Male, English, 1
Male, Russian, 2

 

[Atomic Shrimp]

You could create a single query that yields results like you want, by first creating calculated fields, using the Iif function, so you would include your table into the top section of the query designer, then in the top row of the grid, type expressions like this:

CountEnglish: Iif([nationality] like "English", 1,0)

Then right click the query grid and select totals

then set the countenglish column to 'sum'

Do the same in different columns for other values, i.e.:
CountMale: Iif([sex] like "Male", 1,0)
and so on

However, this approach relies on a lot of hardcoding in the query, and I don't really recommend it. You're better off running two separate queries - one to summarise the count of records by nationality, then another to summarise the count by gender.

 

[Kryst51]

You're better off running two separate queries - one to summarise the count of records by nationality, then another to summarise the count by gender.

There is a sample of this in the db I posted, so you can see it.