Query Issue
Results 1 to 10 of 10

Thread: Query Issue

  1. #1
    Join Date
    Jul 2010
    Posts
    6

    Query Issue

    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.

  2. #2
    Join Date
    May 2006
    Posts
    405
    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.

  3. #3
    Join Date
    Jul 2010
    Posts
    6

    Query Issue

    Do you need to see the SQL.

  4. #4
    Join Date
    May 2006
    Posts
    405
    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.

  5. #5
    Join Date
    Jul 2010
    Posts
    6

    Query Issue

    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

  6. #6
    Join Date
    May 2006
    Posts
    405
    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:
    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
    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.

  7. #7
    Join Date
    Jul 2010
    Posts
    6

    Query Issue

    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.

  8. #8
    Join Date
    May 2006
    Posts
    405
    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.

  9. #9
    Join Date
    Nov 2013
    Posts
    5
    So, it was solved this problem?

  10. #10
    Join Date
    May 2006
    Posts
    405
    Quote Originally Posted by filo View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •