Query to find Specific Dates Help

[RandRob]

Here is what i am hoping to do
in an excisting database
there is a table that is set up like this

id 01_grade 01_recieved 02_grade 02_recived 03_grade 03_Recieved
01 95 2/22/2009 80 2/22/2009 100 2/01/2009
02 85 2/10/2009 100 2/22/2009 100 1/01/2009

with this go on for 50 grades

what i need to do if possible is set up a query
that would search each recieved field and return for me
only the grades which have a specific recieved date

so in the above example if i wanted 2/22/2009
it should return to me
id grade date
01 95 2/22/2009
01 80 2/22/2009
02 100 2/22/2009

this is a little simplified it will also pull from a couse name field to include that and possibly some other info
but if someone can show me how to do this i can figure out the rest

Thanks for any help
Randall

 

[Dennisk]

This is not normalised. Each recurring pair of columns should be in a seperate table linked by the ID from the main table

ie

call this a grades table
GradesID - Primary Key
id - Foreign Key
Grade
GradeReceived

Then the select statement is simple

SELECT * FROM tblGrades WHERE GradeReceived = YourDate

doing it your way would require a query for each column pair then a union query to bring them all together.

 

[RandRob]

Im not sure how you are saying to set up the tables
initially i was going to have a tabel for each
course
each one with the grade date recieved and date sent

i needed to tie these all in to a form so data could be updated
this is when i was told to use one table

i now know how to link all the table to the form so that no longer is an issue.

Can you give me a starting point on the tables you are suggesting

Here is what i would be dealing with
60 courses each one i need to record the course name course grade
date the course was returned and date it was sent

Thanks for your help

This is not normalised. Each recurring pair of columns should be in a seperate table linked by the ID from the main table

ie

call this a grades table
GradesID - Primary Key
id - Foreign Key
Grade
GradeReceived

Then the select statement is simple

SELECT * FROM tblGrades WHERE GradeReceived = YourDate

doing it your way would require a query for each column pair then a union query to bring them all together.

 

[Rabbie]

See this link for a school grades example.

 

[RandRob]

See this link for a school grades example.

I did look at this page as suggested
and understand what is being done here

The issue with me using this would be there is a form that shows all
the data in a single view so it can be updated easily

here is a link so you can see a capture of the form

http://home.comcast.net/~randall.roberts/001.jpg

Currently it fills in the data from three separate tables
all the course info comes from one table pulling it from the column
so if i change this to the method you suggest how do i
then still get this form to fill in?

do you have any sugestion for how i can make this work?

Thank you again the help is appreciated