Multiple Forms

[Guest]

Ok, first of I'm a total newb.

Now that my disclaimer is out of the way.

I have 3 queries, each of them are related in some way.

First query is supposed to count the number of exams taken for a time frame.

SELECT Count([Exam History 1].Exam) AS CountOfExam
FROM [Exam History 1]
WHERE ((([Exam History 1].[Job code])="3") AND (([Exam History 1].Examdate)
Between [beginning date] And [ending date]) AND (([Exam History 1].[Test
type])=[forms]![info entry for reports]![exam type]));


the second query is supposed to count the number of exams passed in a
timeframe
(passed is 80 percent or better)

SELECT Count([Exam History 1].[Record Number]) AS [CountOfRecord Number]
FROM [Exam History 1] INNER JOIN Grade ON [Exam History 1].[Record Number] =
Grade.[Record Number]
WHERE ((([Exam History 1].[Job code])="3") AND (([Exam History 1].Examdate)
Between [forms]![Info entry for reports]![beginning date] And [forms]![Info
entry for reports]![ending date]) AND (([Exam History 1].[Test
type])=[forms]![Info entry for reports]![Test type]) AND (([Exam History
1].Grade)>=80));


Last and most important is a query that is supposed to give me a pass rate.

SELECT [Instr # passed/time frame]![CountOfRecord Number]/[INSTR # exams
taken/time frame]![CountOfExam] AS Expr1
FROM [Instr # passed/time frame], [INSTR # exams taken/time frame];

The first two queries work perfectly, the last....I seem to be having a
problem with. It asks for the beginning and end date twice and the type of
exam twice. It will output the correct data but I would like my database to
work efficiently. Can someone please provide some assistance?

Thanks,

 

[John W. Vinson]

The first two queries work perfectly, the last....I seem to be having a
problem with. It asks for the beginning and end date twice and the type of
exam twice. It will output the correct data but I would like my database to
work efficiently. Can someone please provide some assistance?

Put two more textboxes on the form [info entry for reports] - txtStartDate and
txtEndDate - and use them as the criteria, instead of prompts. It's not
obligatory but it can help to also define the Parameters:

PARAMETERS [forms]![info entry for reports]![txtStartDate] DateTime,
[forms]![info entry for reports]![txtStartDate] DateTime,[forms]![info entry
for reports]![exam type] Text;
SELECT Count([Exam History 1].Exam) AS CountOfExam
FROM [Exam History 1]
WHERE ((([Exam History 1].[Job code])="3") AND (([Exam History 1].Examdate)
Between [forms]![info entry for reports]![txtStartDate] And [forms]![info
entry for reports]![txtEndDate]) AND (([Exam History 1].[Test
type])=[forms]![info entry for reports]![exam type]));

John W. Vinson [MVP]

 

[Guest]

I'm not sure I understand what you mean by: "txtStartDate and

txtEndDate - and use them as the criteria, instead of prompts. It's not
obligatory but it can help to also define the Parameters:"

The first two queries work perfectly, the last....I seem to be having a
problem with. It asks for the beginning and end date twice and the type of
exam twice. It will output the correct data but I would like my database to
work efficiently. Can someone please provide some assistance?

Put two more textboxes on the form [info entry for reports] - txtStartDate and
txtEndDate - and use them as the criteria, instead of prompts. It's not
obligatory but it can help to also define the Parameters:

PARAMETERS [forms]![info entry for reports]![txtStartDate] DateTime,
[forms]![info entry for reports]![txtStartDate] DateTime,[forms]![info entry
for reports]![exam type] Text;
SELECT Count([Exam History 1].Exam) AS CountOfExam
FROM [Exam History 1]
WHERE ((([Exam History 1].[Job code])="3") AND (([Exam History 1].Examdate)
Between [forms]![info entry for reports]![txtStartDate] And [forms]![info
entry for reports]![txtEndDate]) AND (([Exam History 1].[Test
type])=[forms]![info entry for reports]![exam type]));

John W. Vinson [MVP]

 

[John W. Vinson]

I'm not sure I understand what you mean by: "txtStartDate and

I'm suggesting that you use a Parameter Query. If you don't know what a
Parameter Query is see some of the links at:

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


I have NO idea what your queries are. Could you please do me a favor? Open a
typical query in design view; select View... SQL on the menu; and copy and
paste the SQL text to a message here. That will help me advise you.

John W. Vinson [MVP]