Needs your help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In my database i have fields called From (The start date of training e.g 13-Feb-2004) & To (The end date for training e.g. 13-Mar-2004

I want to create report by month and show trainees details who will be for instance on February 2004

An exampl
If Jone has training From 13-Feb-2004 To 13-April-200
When i click on February 2004 it opens the report(Trainees on february 2004) and Jones name is ther
The other report (Trainees on March 2004) I should also find Jones nam
The same for the report (Trainees on April 2004) it means that Jones training three month

This idea will make the size of the database very big..

Can you give me your idea and what should i do step by step

I look foward to hearing from yo

regards
Sonia
 
Sonia

I'm not clear why you think your design will "make the size of the database
very big"?

I'm imaging that you have a single row that holds something like:

TrainingID SubjectID PersonID StartDate EndDate

and has data as you suggested:

99999 1234 567 13-Feb-2004 13-Mar-2004

(where 1234 is the row ID from your TrainingSubject table and 567 is the row
ID of Jones from your Person table).

I don't see how running a report (or a query) against this table will change
the size of your database?

More info, please...

Jeff Boyce
<Access MVP>
 
I think what you should do is create a single report, "Training", and
base it on a query that will select trainees from the data table based
on a query. This query should have a parameter in it, which will be a
text box on your form. In the text box you will enter/select the month
you want to report, and the query will get filtered to include only
trainees who have training scheduled for that month.

Your query would look like this:

SELECT * FROM MyTable WHERE Month([From]) =
Forms!MyForm.txtMonthToReport OR Month([To]) = Forms!MyForm.txtMonthToReport

where txtMonthToReport is the name of the text box on the form called MyForm.
I don't think you need to worry about your database bloating because you
will reuse the same report.
Good luck,
Pavel
 
SELECT * FROM MyTable WHERE Month([From]) =
Forms!MyForm.txtMonthToReport OR Month([To]) = Forms!MyForm.txtMonthToReport

Thanks Pavel Romashkin for your idea.. i like i
but can you clarify more?? or tell me step by step what should i do

I have table called student
and a form called workplacemen
The text box of (From) called Text126 & the text box of (To) called Text12

I want in the report the following field

Student ID Student Name Univ/College Directorate Supervisor's Nam

****Where should i put the parameter in the query in a specific creteria or in a new field as Expr
****and how the user will select a month to see the report

**** Note: I tried this parameter Expr1: (SELECT* FROM students WHERE Month([From]) =
Forms!workplacement.Text126 OR Month([To]) = Forms!workplacement.Text128
but when i tested the query the following messege appears "" You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the Sub query to request only one field"" ****

Looking forward to hearing from yo

Best regards
Soni
 
Jeff Boys

Sorry for not clarifying my question

I have a table called students. It includes the following fields: Student ID, Student Name, Gender, Title, Univ/College, Major, Experience, City, P O Box, Mobile, Email, Picture, Year, Directorate, Section, From(Start Date for the trainig e.g.13-Feb-2004), To (End Date for the training e.g. 14-Mar-2004), et

I have the form workplacement that includes all fields from the table students.

My supervisor want to search the trainees detials who will be for example on february 200

for instance if Jone has training From 14-feb-2004 To 15-April-2004 when the supervisor search for trainees details on february 2004 he should find Jones name in the report the same if he search for the trainees details on March 2004 he should also find Jones name with others.

What should i do
I dont want 100 reports for every month and 100 querie

I want one query and one report for all months per year. When searching by month and year, the report should display with required month and year title e.g. Number of students on February(should be changed with every search) 2004 (should be changed with every search

and display all trainees detial on that month and yea

Note: The searching should be in a new form

regards
Sonia
 
You didn't do it exactly right.
A parameter query will use a paramater, not one of the existing fields.
This is the whole idea. What you want is a query that will accept a
parameter from you (either it will pop up a dialog box - like if you do
SELECT * FROM students WHERE [Student Name] = [Please enter name:], or
it can use an unbound text box on the form - this is what I suggest).
You will not use Text126 and Text128 in this query.
Try the following.
On your form, create an additional control, a text box. Call it
txtMonthToReport. Do not select anything as its data source.
Create a query that and paste this SQL into it:
SELECT* FROM students WHERE Month([From]) =
Month(Forms!workplacement.txtMonthToReport) OR Month([To]) = Month(Forms!workplacement.txtMonthToReport)
Now, open your form and type in a date, like 1/7/2004.
Now, open the query you created. It will use the date entered into the
text box to filter the dataset.
Now, you can use this query to base your report on.
Next thing I would do is change the text box into a combo box, and make
its row source return just the month. You will be able to select a month
you want to report from a drop list.
Good luck,
Pavel
 
Back
Top