Getting what I need in a Report

[piovesan]

I'm not sure if this is a Table and Relationships question, or a Query and Report question but I'll give all the details in the hopes that someone out there can help me!!!

I have a staff member that collects information pertaining to waste collection (organics) She visits homes and records the following:
Date of visit; address of the home; whether or not their organics cart is at the curbside; whether or not there is anything in the cart that shouldn't be; and if there is contamination, what type it is (eg. plastics, cardboard, etc.)

These homes can be visited more than once and of course the data connected to that location will change everytime.

Therefore, I have set up the following tables:

A Location Table which is a lookup table containing the street and community name and a LocID which is an autonumber and is unique. It is also the primary key.

A Civic Table which contains the Civic# field, the LocID linked to the Location Table as a 1:N relationship, and a CivicID which is an autonumber and is unique. But the LocID and Civic# together make up the primary key.

and a Date Table which contains the Date, a DateID which is an autonumber and is unique, the CivicID which links to the CivicID in the Civic Table in a 1:N relationship, a Cart field (yes or no)which indicates whether the cart was at the curb or not, a Contaminants field (yes or no) which indicates if there was something in there that shouldn't be, and then the various types of contaminants as yes/no fields (eg. plastic (yes/no), cardboard (yes/no)). The Date and CivicID make up the primary key.

What I need to be able to get out of this, the number of homes visited on a certain date, the number that had the cart out, the number that had contamination and what percentage of the comtamination was plastic, cardboard, etc. Then, I also need to compare the % change when these same homes are revistied on another date.

Am I going about this setup the right way? And how do I create a report to get this info out? I think I have to create subqueries to COUNT the numbers of carts, contaminants, etc do I? Problem is, I've never used subqueries!

Thanks for any help!!

 

[Brambojr]

I believe that the way you currently have the tables configured will case problems immediately and down the road.

I am not sure what the Civic table is, but let me tell you how I think setting it up may work.

Your location table sounds good.

You may want to create table for employees, but since you indicated it was only one, we'll skip that until the operations grows.

You need a table with a list of contaminants. This will simplify things down the road. You can then hit that table for the drop down boxes. It will make it easy to add others later and you may want to add a grouping level to this later.

You do NOT want a Date table. You want a Visit table. It will have a place for the loc ID, and the date (default - Date()??) You can set up a key in the visit table one of two ways, by creating another autonum field or by selecting location and date (multiselect) and clicking the key at the top )doing this assumes that no location will ever be visited twice in one day).

There is no such thing in Access as subqueries. If you are referring to using one query to feed information into still another query, it's really rather simple and works the same as writing a normal query - only you add a query in design view and not a table (the pop up tabbed thing when you double click new query in design view).

After this you should be able to grab the information that you described fairly easily. Brambojr