By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,905 Members | 2,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,905 IT Pros & Developers. It's quick & easy.

Create Filter Record on a Form?

yosiro
P: 34
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
Attached Files
File Type: zip example.zip (25.0 KB, 95 views)
Sep 18 '12 #1

✓ answered by 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.

Share this Question
Share on Google+
6 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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?
Sep 18 '12 #2

yosiro
P: 34
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
Sep 18 '12 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Sep 18 '12 #4

yosiro
P: 34
Sory for the attachment

I have two table like this




and form like this



the question is how to make query for that?
Sep 18 '12 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Attached Files
File Type: zip example (Edit).zip (39.4 KB, 104 views)
Sep 18 '12 #6

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Sep 18 '12 #7

Post your reply

Sign in to post your reply or Sign up for a free account.