I have a database with five tables of student test data (i.e., PSSA dates and scores, CAFAS dates and scores, etc.).

I have five queries built (one for each test) which projects the next upcoming test date (different "rules" for each type of test)

Is there a way to build a form/query which would prompt for a date range and would run through each of the five queries to see which students have upcoming tests (among all 5 types) between a certain time frame?

Not all students have all tests so there is no linking variable between all five tables/queries.

Thoughts?

JenB,

You could create a Union Query to combine the five Queries, if they are all alike like you said before. Then in a Sub Query of the Union Query, you create a Parameter that will ask you for a Start Date and an End Date for a certain time period. Does this sound like what you wanted?

RAZMaddaz

JenB,

Before we go any further, can you explain why you have Five different Tables and not just one with all the Tests?

RAZMaddaz

Basically, we did separate tables bc we're doing databases for about 6 schools - some schools have some tests; some schools don't. It was easier to just remove a table from a school database if it wasn't being used.

Would a union query work if there is no variable the same in each table? Just as some schools have some tests, some students have test dates/scores; others don't - so I'm unable to link by student ID.

A Union Query combines all the Fields in one Query with all the other Queries. You don't have to necessarily have to use all the Fields from each Query you can just retrieve the Fields you want. For Example:

Select Field1, Field2, Field5, Field10 from Query1
Union
Select Field20, Field30, Field8, Field30 from Query2
Union
Etc....


If the Fields were in the same order from each Query and were all alike then all you would have to do is the following:

Select * from Query1
Union
Select * from Query2
Union
Select * from Query3

Understand?

JenB,

View a Query in SQL and copy the above into the SQL. Change Query1, Query2 and Query3 names to the name of your Queries and see if that combines eveything correctly. This will give you a better idea of what I was trying to explain.

I need to go for the evening, but I'm sure someone else here could explain the rest if the first part works the way you want.

Good luck!

RAZMaddaz

Thank you for your help. I haven't had a chance to try this yet (as I got pushed into other more pressing projects) but I wanted to hop on to say I appreciated your help!!

Jen

Your welcome and good luck with your work!

RAZMaddaz

Thanks again for your help.

This worked perfectly (after some tweaking with the original queries to get them all to "line up" appropriately).

Any thoughts on how to go about adding a date prompt? I'd like to be able to have a prompt asking which time frame (between when and when) any of the tests will be due.

I currently have the following columns:

Test Name
First Name
Last Name
Due Date

I'm not opposed to creating a separate form where it asks for a begin date and an end date - I'm just unsure how to go about doing this.

Ideas?

Thanks in advance!

Glad you were able to get everything to work!!!

What you are now trying to do is called a Parameter. To do this simply enter the following in the Due Date Field of the Query:

Between [Enter the Start Date:] and [Enter the End Date:]

When you run the Query a window will appear asking you to "Enter the Start Date," in which you will enter any date you want and then click ok. then another window will appear asking you "Enter the End Date" and you will do the same thing. This will not work if the Due Date Field is a Date Type of Text. Understand?

RAZMaddaz

Thank you!

Before I returned for a solution here, a co-worker suggested a query of the union query and that has worked too!

I appreciate the help!

Jen

Awesome!!! Glad you got everything working and that I could help!!!

RAZMaddaz