Create Filter Record on a Form?

[yosiro]

In my attachment (file access 2010)
I want to show some information from a form but i don't know how to write code on query or on a form load.

Described here:

I want to make report that run from a Form that just show only a specific year
Example I just choose from combo box a year that I want to searh, e.g. just 2011

So I want to make a form that show me some information

1. City of student (i can choose all or specific city)
2. Year (i can choose all or specific year and just year format)
3. Start Date
4. End Date
5. Score review

the information must show:
Total of student : ?
Total of student that has score above score review : ?
Average

and the information can you see on my attachment.
Thanks

[TheSmileyCoder]

You mix back and forth between using the terms form and report, so it gets a bit confusing. Do you want your report to ONLY show the summary information?

[yosiro]

there are 2 output that i want to show.

1st when i click submit button then i will get some value on the form,

2nd when i click print report then i will get all information on report that i haven't create on the example.

Please see my attachment

[TheSmileyCoder]

Sorry, I don't download attachments from people I don't know. If you can't explain it with a screenshot and text there is also a fairly good chance that I wont be able to make sense of your database either, that just experience talking here.

If you can explain it with screenshots and text I will be happy to try and help, or you can wait around and see if there is someone who is willing to download and open the attachment.

[yosiro]

Sory for the attachment

I have two table like this

and form like this

the question is how to make query for that?

[TheSmileyCoder]

Okay, now we are getting somewhere.

As for the the places marked "?" in your form you can get those values by using a domain function such as dcount.

I have uploaded an example of how to do it, using a query that draws part of its criteria from the form.

Its not perfect, for instance it only reacts to begin and end dates, and not the year textbox for instance.

But you can take a look at it, work on it some yourself (its your assignment after all) and then come back when you run into a new problem.

[TheSmileyCoder]

On a few general notes to your database.
The cities should not be stored as text. You need to create a separate lookup table to contain the cities.

The score should not be kept as text. If its a number keep it as a number, otherwise you will get all sorts of weird results when doing comparisons.

Your design nor question mentions not what to do when a student has more then 1 score.

You have a table Score with a field score. Thats a bad idea, as access gets confused as to whether you are referring to the field or the table. Its a good idea to use a certain notation when naming fields and objects (look up Hungarian notation) as it helps document the database and what the objects are for. I for example prefix all table names with a tbl such as tbl_Student and tbl_Scores.

Last comment, in your scores table you have a field Customer_ID which I can only assume should have been named Student_ID.