Query Help Needed!

[Golfinray]

I know I should be able to do this but my mind is blank. I have 2 tables. One
for every school district in the state. One for every school district in the
state that has turned in their paperwork. (I work for the state education
department) There are 255 districts, 183 have turned in their paperwork. I
want a query to give me a list of those who have turned in their paperwork
for '08 and a list of those who have not. I tried using IFF statements but I
never could get the syntax right. Thanks so much!!!

 

[Jeff Boyce]

I'm not entirely clear on your starting position...

Are you saying your two tables contain "all school districts" and "all
school districts that have turned in paperwork"?

That sounds like how you might set up a spreadsheet, not a relational
database.

Couldn't you simply add a "turned in paperwork on date =" field in a single
table, then update that field for each school district as the paperwork
comes in?

And I may be reading too much between the lines here, but it seems possible
that your school districts might have to turn in paperwork more than once
(e.g., once each year). If so, you'll need to rethink your table structure
even further -- making a new database each year to handle that year's
activity is NOT making the best use of the relationally-oriented features
and functions that Access provides.

.... or I may be reading too much into this...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Golfinray]

Thanks! They do turn in facilities projects once per year. The date that they
turn them in is unimportant. I just add them to my completed table (add new
record) when they turn them in. Since it is near the end of the year, I would
just like to know which have and which haven't so far. If one table would be
better, I could probably do that with some trouble. Isn't there a way i could
just query to see how many match? Thanks!!!!

 

[Dale Fye]

I'm with Jeff on this one, sounds like your table structure needs to be
revised. Maybe instead of DateReceived, you might want to consider having a
LatestFacRequest field that contains the year that the latest request is for.
This way, when you get a request in, you simply change the year value in
this field, making it easy to query a single table. However, if you need to
maintain data for each years request (Amount, submitter, ...), then a second
table with this information might be necessary, in which case storing the
year the request is for would be critical.

However, in the mean time, ...

In your second table, where you just add the record when they hand it in,
will they have records for 06, 07, and 08? If so, how do you intend to
distinguish between which records belong to 08 as opposed to 07?

If not (you clean this second table out each year), then you can do
something like:

1. To get the list of those that have submitted, you simply query table #2
for a list of the schools. Or, if there is data in table 1 that you need,
then it would be something like:

SELECT Table1.*
FROM table1 INNER JOIN Table2
ON table1.DistrictNum = table2.DistrictNum

2. To get the list of those that have not submitted you simply modify this
query to:

SELECT Table1.*
FROM table1 LEFT JOIN Table2
ON table1.DistrictNum = table2.DistrictNum
WHERE Table2.DistrictNum IS NULL

HTH
Dale


--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.