Printable View
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.
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.
Do you need to see the SQL.
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.
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
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:
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).Code: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
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.
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.
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.
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.Quote:
Originally Posted by filo