Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Advice required on Database (2000)

    Hi,
    I was hoping that someone could offer me some advice regarding yet another database that I'm working on. Attached is a database that I will need to pull some statistics off of.
    The information that is entered in FrmMain and thus replicated in tblMain has certain fields that I need to concentrate on. I need to collate information from: OffCat,Days,Counsel,IDTarget,TargetMet,BriefOut. I've a vague idea how to do this but I have a few questions.
    1) I need to fill 14Met or 21Met (tblMain) with either Yes or No dependant on the information in ComTarg on frmMain and dependant upon whether IDTarget is 14 or 21. So ComTarg is "Yes" and IDTarget is 14, 14Met = "Yes". Is the best way to do this in a query? Frankly I don't know!
    2) I then need to collate how many cases fall within BriefOut equalling the previous month.I'm pretty sure I know how to do this as I've had a similar query previously.
    3) What I then need to do is create a query that relates to the cases within these dates, and find the following information:
    How many cases have BriefOut within previous month,Target 14 and have had 14Met = "Yes"
    How many cases have BriefOut within previous month,Target 14 and have had 14Met = "No"
    How many cases have BriefOut within previous month,Target 21 and have had 21Met = "Yes"
    How many cases have BriefOut within previous month,Target 21 and have had 21Met = "No"

    The rest relates to relating TblMain with TblCounsel.

    I need to split the above queries into OffenceCat,Ethnicity,Gender.
    then Days, Ethnicity, Gender,
    then HearingType, Ethnicity, Gender.
    I haven't created any relationships yet but as I see it I only need a relationship between TblMain and TblCounsel. I really wanted some advice as to the best way to have a crack at the queries before I start as there are quite a few. Would it be best to do a main query between TblMain and TblCounsel with a calculated field similar to FirstOfMonthateSerial(Year([BriefOut])),Month([BriefOut]),1) with the criteria DateSerial(Year(Date()),month(Date())-1,1) and then produce a report with DLookUp fields within textboxes?

    regards,
    Nigel
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Advice required on Database (2000)

    1) This resembles your earlier question about DateFull, Bail and Custody in the thread starting at <post#=343115>post 343115</post#>. Write code in the After Update event of ComTarg, and use the code you got in the other thread as model.
    2) Yes.
    3) Use DCounts or DLookups with various where-conditions for this, as in the earlier thread.

    Please reread that thread, for a lot of the general remarks there apply here too.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advice required on Database (2000)

    Hi Hans,
    Yet again thanks for your assistance, I just really wanted confirmation that I was going about it the correct way as I thought that this database was very similar to the previous one. If I get stuck I'll do another post but I'm kinda hoping I'll be able to do this one by my lonesome.
    Thanks,
    Nigel

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advice required on Database (2000)

    Hi Hans,
    Sorry about this, I've had a look at the coding and I understand what I need to do but I can't quite get there. I need to work out whether the target has been met in the following way:
    NextDate = start date
    Plus either 14 or 21 days dependant upon what has been entered in IDTarget
    Then autofill TxtTargetMet (which I've replaced comTarg with).
    Private Sub IDTarget_AfterUpdate()
    If IsNull(Me.IDTarget) Then
    Me.ID14 = Null
    Me.ID21 = Null
    Elself Me.IDTarget = False ' name is not correct on form
    Me.ID14 = Me.NextDate + 14
    Else
    Me.ID21 = Me.Nextdate + 21
    End If
    End Sub.

    What I'm curious about is whether I need an ID14 and ID21 in TblMain or whether I can just use a general IDTarget field which includes both 14 and 21.
    Then presumably to fill TxtTargetMet I would create a query based on TblMain, adding calculated fields.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Advice required on Database (2000)

    >> whether I need an ID14 and ID21 in TblMain or whether I can just use a general IDTarget field

    That depends on whether you will ever need both dates (NextDate+14 and NextDate+21) for some reason. If it is always going to be either the one or the other, it is more efficient to use one Target date field, and set it to either NextDate+14 or to NextDate+21, as needed.

    For TargetMet you would use a query, as for TargetMetB and TargetMetC in the other database.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advice required on Database (2000)

    Hi Hans,
    I have replaces ID14 and ID21 with TargetMet so that this will be a central figure.

    I have used the code:

    Private Sub Com14_21_AfterUpdate()
    If IsNull(Me.Com14_21) Then
    Me.[TargetMet] = Null
    Elself Me.Com14_21 = False ' name is not correct on form
    Me.[TargetMet] = NextDate + 14
    Else
    Me.[TargetMet] = NextDate + 21
    End If
    End Sub

    But it picks up an error in
    Elself Me.Com14_21 = False ' name is not correct on form

    Com14_21 is a combo box in FrmMain that is lookups up the values in TblTimeEstimate. Either 14 or 21.
    TargetMet is in TblMain & FrmMain.
    Is the coding incorrect?
    Thanks,
    Nigel
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Advice required on Database (2000)

    TargetMet is a Yes/No field, you can't set it to a date. I thought that you would create a TargetDate field (or something similar) of type Date/Time to hold NextDate+14 or +21, and that TargetMet would be Yes or No depending on whether another date was earlier or later than this TargetDate.

    Com14_21 is a combo box that can have the values 14 or 21, so it can't be False.

    Before continuing, try to make a list of the data you need, with their data type (text, number, date, yes/no). Do this on paper, not on the computer.
    Next, try to list the relationships and dependencies between the data. TargetMet, for instance, is not something the user will enter, it is derived data. Therefore, TargetMet should not be a field in a table, but it should be calculated in a query.

    This will assist you in designing a logical data structure.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advice required on Database (2000)

    Hi Hans
    Still working on it. I took your advice and designed the database on paper which was a lot easier to note the data types and what I would need.
    I have started over (unsuprisingly) but I still would like to run a few things past you if that is okay.
    I have:
    CaseID which is either 14 or 21. Number format
    DatePapers which is the date the file is in. Date format
    TargetDate, date format.

    Next I would need to do a code:
    Private Sub DatePapers_AfterUpdate()
    If IsNull(Me.DatePapers) Then
    ' If DatePapers has been cleared, clear TargeDate
    Me.TargetDate = Null
    Elself Me.CaseID = ...............................

    And this is where I get lost as the data is reliant on CaseID being either 14 or 21 as opposed to false or true. Therefore TargetDate is a singular field as opposed to Target14 or Target21 fields.

    As with the original table, TargetMet14 and TargetMet21 do not exist and would only exist in a calculated query taken from TblMain. This would be created after the coding presumably.

    The rest I can do and have a pretty good idea of the direction to take. If you can find the time to quickly help it would be appreciated.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Advice required on Database (2000)

    Hi Nigel,

    If I understand the current setup, you want to add 14 or 21 days to DatePapers, depending on whether CaseID is 14 or 21. In other words, you want to add CaseID days to DatePapers. Try this:

    Private Sub DatePapers_AfterUpdate()
    If IsNull(Me.DatePapers) Then
    ' If DatePapers has been cleared, clear TargetDate
    Me.TargetDate = Null
    Else
    ' Add CaseID days to DatePapers and assign to TargetDate
    Me.TargetDate = Me.DatePapers + Me.CaseID
    End If
    End Sub

  10. #10
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advice required on Database (2000)

    Hi Hans,
    Thanks for that, works a treat. I would have been there a very long time indeed.
    Is it possible to get the TargetDate to change if the CaseID is changed during the process?
    Looking at the previous coding for the other database I guess:

    Private Sub DatePapers_AfterUpdate()
    If IsNull(Me.DatePaperss) Then
    'If DatePapers has been cleared, clear TargetDate
    Me.TargetDate = Null
    Elself Me.CaseID = 14
    'If not CaseID, set TargetDate to 21
    Me.TargetDate = Me.DatePapers + 21
    Else
    'Otherwise, set TargetDate to 14 days after full date
    Me.TargetDate = Me.TargetDate + 14
    End If
    End Sub

    I'm probably wildly wrong but i thought i'd have a stab. I've included the zipped database just in case you need to refer to it.
    Thanks for all your help.
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Advice required on Database (2000)

    If you want the TargetDate to change if CaseID changes, you must put code in the After Update event of CaseID. Since the action is the same as when DatePapers changes, you can refer to the After Update event for DatePapers:

    Private Sub CaseID_AfterUpdate()
    DatePapers_AfterUpdate
    End Sub

  12. #12
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advice required on Database (2000)

    Thanks Hans, simple when you know how!

  13. #13
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advice required on Database (2000)

    Hi,
    I seem to be having problems with the queryMonth. I have based it on TblMain with calculated fields:
    FirstOfMonth: DateSerial(Year([DateOut]), Month([DateOut]), 1)
    Criteria - DateSerial(Year(Date()),Month(Date())-1,1)
    It comes up with Data Type mismatch in expression.............can't work out where I'm going wrong..................help
    Attached Files Attached Files

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Advice required on Database (2000)

    The attached database does not contain a query named queryMonth, and tblMain does not contain a field named DateOut. There is a query QEthnicity with similar criteria, it opens without problem on my PC.

  15. #15
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advice required on Database (2000)

    Sorry, wrong database attached!!!!!!!!!!! Heres the correct one.............
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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