Help me in this query

[Immaster]

Hi mates
I will explain the problem in easy way

I have a table query this table has schedule of classes during the week for example in Sunday i put sun1 sun2 sun3 sun4 upto sun7 sun means sunday and 1 means period 1 now i have a report to display who of the teachers arent have classes in sun1
Some teachers are working and some are free I want to know sho is free
I made this query from master table and puy a criteria is Null yo grt names of teachers that are free in sun1
Now if i want to know who is free in sun2 which means sunday period 2
Should i make another query and another report or there is a better way

If i make query for each day and period i will make 34 query and it is a waste of time please helo

 

[Mile-O]

Go back to your table design, by the sounds of it. When I see Sun1, Sun2, Sun3, etc then it's indicative of a design flaw in your tables. Ditto the idea of having 34 queries.

I have this horrible mental image of you having created some sort of matrix (days v classes) in an Excel style out of a database table. Is this so? If not, what is your table structure?

 

[Immaster]

I am using access 2007 and my table structure that is
Teachers have many classes during the day and week
I put tescher name the put sun1 sun3 etc for their classes
Now i want a query and report to display who are free in this day and this period
Hope this clear

 

[namliam]

Go back to your table design,

I second that idea.

 

[Immaster]

Please i am new to access and cant see images cause i have less than 10 posts

Please explain step by step

 

[Mile-O]

Er, there are no images.

See this example for what I mean about table structure.

 

[Immaster]

Thanks alot Mile-O for the example
I tried to see your example and explore it
it solves half of the story

the timetable doesn't depend on date . it repeated every week with the same classes .
I need a report depend on a query when i open the report it asks me first which day you want and which period ( No date all the year will be the same timetable) that has no classes for certain teachers . OR as simple who are free on this day and this period exactly

working days are Sunday Monday Tuesday Wednesday Thursday and each day 7 periods

 

[Immaster]

Please if someone can help me with example . Thanks in advance

 

[Mile-O]

While you've seen my example, I don't know how helpful it can truly be as you've not answered my first question about your table structure?

 

[Immaster]

OK Mile-O
your example was great but please modify it little to have all days and periods 5 days with 7 period each and an query to display the name of teachers that don't have class on this day with this period.

please i'm novice in access . Sorry if I don't understand your expressions.

 

[Mile-O]

With all respect, I'm advising, not doing your work for you. Advising is free. Doing work costs.

 

[Immaster]

It's ok if someone can do this for me.

 

[GinaWhipp]

Immaster,

I think you misunderstand the purpose of the Forum. We are here to help *you* do the work not for us to do the work. We are ALL unpaid volunteers donating our spare time. Now, that said, if you are unable to do the work you might want to consider hiring someone to do it for you.

 

[Immaster]

Immaster,

I think you misunderstand the purpose of the Forum. We are here to help *you* do the work not for us to do the work. We are ALL unpaid volunteers donating our spare time. Now, that said, if you are unable to do the work you might want to consider hiring someone to do it for you.

Thanks GinaWhipp for this , however, I'm new and just start using access. Then I need little help to develop the project.
My Question only how Do I do a forum that has a drop-down box which has the day and period of the class and display the teacher's name only as a result.

If someone can only tell me how to do it without making example for me.

 

[Immaster]

Please can you correct me if I am wrong
I'll design a table of teachers with primary key teacherID
then subject with PK subjectID
classes with ClassID
days with DayID
periods with PeriodID

Now Primary TeacherId with foreign Key subjectID , classID,DayID and PeriodID

Then Primary subject ID with foreign ClassiD ,DayId and PeriodID
then primary ClassID with foreign subjectID,DayId and PeriodID
last primary DayId with foreign Period ID

Is this make sense?

 

[GinaWhipp]

I think you are missing a few Junction tables AND I am not sure why you would need a table with Days or a separate one with Periods when it should be included in the Classes one. This might help you to better understand...

http://www.access-diva.com/dm7.html

Because you are new to Access I'm going to suggest you study the links below...

Jeff Conrad's resources page...
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page...
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials...
http://allenbrowne.com/links.html#Tutorials

UtterAccess Newcomer's Reading List
http://www.utteraccess.com/forum/Newcomer-s-Reading-List-t1998783.html
Help with Normalization
http://www.access-programmers.co.uk/forums/showpost.php?p=1146957&postcount=2

Sample data models...
http://www.databasedev.co.uk/table-of-contents.html
http://www.databaseanswers.org/data_models/

Naming Conventions…
http://www.access-diva.com/d1.html

Other helpful tips…

Setting up a Model Database
http://www.access-diva.com/d11.html

My Database Standards...
http://regina-whipp.com/blog/?p=102

All that said, we can't just tell you how to do something unless we understand exactly what you are trying to do. In the same fashion you can't go to the tailor with some fabric and say *make me a suit* and walk out the door. You didn't tell the tailor what kind of suit! That is why it is important to answer the questions asked..

 

[vbaInet]

In the same fashion you can't go to the tailor with some fabric and say *make me a suit* and walk out the door.

I tried that once and instead of a suit he ended up making me a dress!

 

[GinaWhipp]

@vbaInet

Lesson learned!

 

[Immaster]

I have found my way.Thanks for all that help me .