# Database Discussions > Microsoft Access >  Query Issue

## algebroni

I have a Query that pulls rooms due for inspection, it has 7 fields:
Roomid  final inspection  1month schedule inspec  3months schedule inspec  6months schedule inspec  9months and 1year.
The 1,3,6,9 and 1 year field are calculated date fields based on final inspection date. The query works fine but my problem is when I try to pull rooms that due for inspection out of the 1,3,6,9and 1year data based on user input between start and end date nothing comes up. any help is really appreciated.

----------


## GolferGuy

We need to see the query that you are actually using.  You say it works fine, and I assume you mean it calculates those different dates for you.  But, when you try to pull rooms that are due for inspection nothing comes up.  What are you doing to "try to pull rooms ..."  We need to see what these actual actions are.

----------


## algebroni

Do you need to see the SQL.

----------


## GolferGuy

Actually, not just the SQL, but EVERYTHING that applies!  I asked what or how are you "trying to pull rooms" so that needs to be answered as well as the SQL involved.  There is NO way to help you without details of what is happening, what you want to happen, and what error messages (detail!) you get if any and when you get them.

----------


## algebroni

Thank you for your help, let me answer your question first I try to pull rooms that are due for inspection by putting 
Between [Enter Start Date] And [Enter End date] in criteria of 1,3,6,9 and 1 year field in the query, when I did that nothing came up with no errors.
Now let me give an idea about this database, I have 4 Tables: 
Basic Room Info
RoomID  Bldgname  Bldg# Floor  Sectionname

Room Inspection
RoomID  InspDate  InspReason  InspResult  Technician

Treatment Dates
RoomID  Positive Date  Pre-Treat  Treat Final Inspection

Adjoining Rooms
RoomID Left Right Above Below

So basically we inspect Rooms if it's positive we treat, we do final inspection and schedule room for follow up inspection 1,3,6,9 and 1 year. if neg we do nothing.

This is SQL for the query
 SELECT [Basic Room Info].[RoomID], [Basic Room Info].[Bldgname], [Treatment Dates].[Positive Date], [Treatment Dates].[Final Inspection], DateAdd("m",1,[Final Inspection]) AS [1 Month Scheduled], DateAdd("m",3,[Final Inspection]) AS [3 Month Scheduled], DateAdd("m",6,[Final Inspection]) AS [6 Month Scheduled], DateAdd("m",9,[Final Inspection]) AS [9 Month Scheduled], DateAdd("yyyy",1,[Final Inspection]) AS [1 Year Scheduled]
FROM [Basic Room Info] INNER JOIN [Treatment Dates] ON [Basic Room Info].[RoomID] = [Treatment Dates].[RoomID]
WHERE (((DateAdd("m",1,[Final Inspection])) Between [Enter Start Date] And [Enter End date])) OR (((DateAdd("m",3,[Final Inspection])) Between [Enter Start Date] And [Enter End date])) OR (((DateAdd("m",6,[Final Inspection])) Between [Enter Start Date] And [Enter End date])) OR (((DateAdd("m",9,[Final Inspection])) Between [Enter Start Date] And [Enter End date])) OR (((DateAdd("yyyy",1,[Final Inspection])) Between [Enter Start Date] And [Enter End date]))
ORDER BY [Treatment Dates].[Final Inspection];
 Hope this helps.   Thanks again

----------


## GolferGuy

First, you need to redesign your database.  Lookup Database Normalization on the web and do some reading.  The tables you laid out are not Normalized, that is why you need to read up on Normalization.  
This is a starting point on normalized tables as I see them:


```
BasicRoomInfo		AdjoiningRooms	RoomInspection	TreatmentDates
RoomID			RoomID		(OK as is)	RoomID
BldgName		RecordID			RecordID
BldgNumber		AdjoinID			TypeID
FloorNumber		AdjoinRoomID			TreatmentDate
SectionName

New Tables:
AdjoinDirectionTable	TreatmentTypesTable
AdjoinID		TypeID
AdjoinDirection   	TreatmentType
```

This new design of the database removes the buckets you defined for the adjoining rooms and puts all adjoining rooms in the same field, but with a descriptor (accessed by AdjoinID into table AdjoinDirectionTable) so when you need to know the direction of adjoin to the subject room, you can find out.  But during normal processing, especially in queries, the direction normally means nothing.  This new design also removes the treatment dates out of buckets and puts all dates into the same field, TreatmentDate and then adds the descriptor (accessed by TypeID into table TreatmentTypesTable).
When you read up on Normalization, you will understand what I mean by buckets above.

Now, about your query.  You have a bunch of DateAdds in your SELECT statement, and a bunch more in your WHERE statement.  Why?
I would suggest you try a query against only the TreatmentDates table and the FinalInspection field.  Then only look for FinalInspection plus one month.  See if you get any hits.  If you look at your data before running the query, you should be able to find a record that should be selected.  After identifying at least one record that should be selected, run the query to see if it is selected.  If not, post the query SQL and the details of the record you thought should be selected.  Then we might have enough info to be able to see what is working and what is not working.

----------


## algebroni

Thanks for quick response and your advice. In the treatment type table do you mean treatment type are 1,3,6,9 and 1 year follow up or something else. Regarding the Query I ran it without calculated field and it did pull the records for the 1 month and the 3 months.

----------


## GolferGuy

The treatment types would be what you had used for the separate fields before.  You had a table named TreatmentDates.  The fields for those dates were: Positive Date Pre-Treat Treat Final Inspection.  I really could not figure out what words went with other words to be the names of your fields.  That is, Positive, Date, Pre-Treat, Treat, Final, Inspection or Positive Date, Pre-Treat Treat, Final Inspection, or ????  But, from the name of the table, I had the idea that you had different types of treatments you do.  But if you only have one type of treatment, just different dates in regard to that treatment, and the Positive, based on your last response, sounds like it is the outcome of your testing, and if Positive is True, then you do a treatment.  From not knowing the business, it is very difficult to try to figure out what your table names and field names are and mean.

----------


## filo

So, it was solved this problem?

----------


## GolferGuy

> So, it was solved this problem?


Not in this thread.  The original poster was asking questions but not showing detail of how he was doing these things. It is impossible, at least for me, to help others without details of what is happening.

----------

