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

    Simple database help needed. (Access 2000)

    Here attached is a rather simple database that I could do with some assistance on as I need to generate more complicated stats than I know how to do. The current database used is in excel format, which I'm not sure will be the best way to go, but I know that Access can create reports etc.
    What I need to be able to do where the user enters a date in the Full file received, I need to enter a non adjustable date into Target Date for Bail or Custody. This will be 10 days from the date Full file received for Remanded in Custody equalling "Yes" and 14 Days (Bail) for "No".
    I then need to calculate whether the Date Package sent is more that this date once filled, and display Target Met "Yes" or "No".
    Thats the easy bit.
    What I then need to do is print a report to send of to the relevant agencies based on the beginning of the URN and the Date Package Received (Not full file). All URN's begin with 36G,36K,36N, followed by numbers. The G,K & N relate to different areas and reports will need to be printed off for all of these where there is no date entered in the Date Package Received. This list would also need to be by Court Date Order.
    It gets harder........
    On a monthly basis I will need to do a statistical report for all cases that have had a date entered in Date Package Sent corresponding to that month. These will all need to be listed, and statistics drawn off of them. The stats would be:
    Amount of files, amount of defendants, average defendants per file, (all numerical):
    Amount of Bail cases within target date, amount of Bail cases outside target date, amount of custody cases in target date, amount of custody cases outside target date. These four will also need to be numerical as well as statistical (i.e. percentages).
    Can anyone help............or should I stick to my excel spreadsheet??? Although i would quite like to do it in Access.
    Anyone up to the challenge, help would be appreciated.
    Thanks,
    Armitage
    Attached Files Attached Files

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

    Re: Simple database help needed. (Access 2000)

    Before even trying to tackle your questions, you should get the data structure right. Here are some comments:
    <UL><LI>Turn off "Track name AutoCorrect info" in the General tab of Tools | Options...
    <LI>Set "Subdatasheet Name" to [none] in the Properties window for each table.
    <LI>Create lookup tables for caseworkers and lawyers and use those as row source for the corresponding fields in tblMain. The typed in value lists you have now are a nightmare to maintain.
    <LI>I don't know how far along you are with the design of this database, but if it's still feasible, replace field names such as "Is defendant RIC?" by names without spaces or special characters such as question marks, parentheses etc. Underscores are OK. This will make your life a lot easier later on.
    <LI>The second field in tblBuilder is named Field1. Shouldn't that be Code? That is what the lookup for Casebuilder ID in tblMain expects.
    <LI>I don't see the purpose of having an ID and a Field1/Code field in tblBuilder. I would scrap ID and make Field1/Code the Primary key. Or if you want to keep ID, relate the Casebuilder ID field in tblMain to ID instead of Field1/Code. (You would have to modify the data for this.)
    <LI>You have a one-to-one relationship between tblMain and tblBuilders on ID. I don't think that is correct. As far as I can see, there should be a one-to-many relationship between either ID or Code on the one side, and Casebuilder ID on the many side.[/list]You'll probably have questions about the above, don't hesitate to ask them. I think it's best not to start working on the reports now.

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

    Re: Simple database help needed. (Access 2000)

    Hi Hans,
    I have done all of the above okay. The only query I would have with what you have sent me is the type of relationship and whether or not this is correct. I have reattached the amended database.
    Now for the reports......where to start........
    Thanks
    Attached Files Attached Files

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

    Re: Simple database help needed. (Access 2000)

    That was fast. There are still a few loose ends, but I'm sure you can tie those yourself. (For example, you haven't changed the CaseWorker and Lawyer fields from text to number yet, and changed their row source type to Table/Query. You also need to update some of the names and control sources of the controls on the form.)

    Let's take it one thing at a time. To fill in the target date, we will use the After Update event of the text box bound to DateFull.

    Private Sub DateFull_AfterUpdate()
    If IsNull(Me.DateFull) Then
    Me.TargetB = Null
    Me.TargetC = Null
    ElseIf Me.Custody = False ' name is not correct on form
    Me.TargetB = Me.FullDate + 14
    Else
    Me.TargetC = Me.FullDate + 10
    End If
    End Sub

    To prevent the user from editing these dates, set the Locked property of the text boxes for TargetB and TargetC to Yes.

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

    Re: Simple database help needed. (Access 2000)

    Hi Hans,
    When I try and change the Caseworker & Lawyer fields to number it deletes the records. This happens in the tables. Out of interest, why would data that contains text need to be altered to number...or am I missing the point?
    Presumably this is not done in the FrmMain as they already reflect Row Source table/query, and do not include this option. I think I should be able to cope with the remainder of altering the names & control sources as I know where I've gone wrong.

    With regards to the VB, there seems to be an error as it won't allow me to go past "ElseIf Me.Custody = False ' name is not correct on form" as it keeps highlighting the '. This could be my fault though as I have no knowledge whatsoever of VB.

    Thanks

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

    Re: Simple database help needed. (Access 2000)

    In this case, you could leave the Caseworker and Lawyer fields as text, since they are only 2 or 3 bytes long anyway. If you decide to do so, you might as well do away with the AutoNumber field in tblCaseworker and tblLawyer, since you don't use it anyway, and make the text field the primary key.

    In general, it is more efficient to store a numeric code in a table than a text. The tblBuilder table is a good example. You are now storing the numeric code for a builder in tblMain; that is only 4 bytes per record; storing the CaseBuilder name in tblMain would have taken up up to 15 bytes per record.

    Try removing the comment (the part of the line beginning with the apostrophe ') and see if that removes the code. Otherwise, make the line blank and type

    ElseIf Me.

    At that point, IntelliSense should display a list of available items. Is Custody among them?

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

    Re: Simple database help needed. (Access 2000)

    Hi Hans,
    I've changed all the tblCaseworker & tblLawyer to text fields, making these Primary.
    The code is still expecting me to fill out further details after the False. I've tried entering the Elfself Me, but this does not supply me with the option of choosing Custody from the drop list. Any ideas?
    Also with regards to this coding, on the database I have two fields where a target date is displayed depending on the status of the Custody. I would like to keep this with both fields as it is then clear to the user as to what target date they will be aiming for, i.e. if In Custody, target date is displayed in TargetC, if not in Custody, target date is displayed in TargetB. Will this coding work with both fields and is it just a case of duplicating the coding for both fields?
    Thanks.

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

    Re: Simple database help needed. (Access 2000)

    Open the form in design view.
    Select the combo box whose label says "In Custody:"
    Change its Control Source and its name to Custody. (In the version you posted last week, you had renamed the field in the table to Custody, but the combo box on the form still referred to the old, no longer existing name "Is defendant RIC?"

    The code I posted last week already assumes that you have two separate text boxes TargetB and TargetC; there is no need to duplicate the code.

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

    Re: Simple database help needed. (Access 2000)

    Hi Hans,
    Thanks for your ongoing help.
    The Custody is now displayed in the drop field and I've changed all the info to the correct format.
    With regards to the coding, after Elself Me.Custody = False, it is expecting Then. I could guess at the meaning of this but I wouldn't be confident.
    Is this the point you then enter whatever text you wish to display.....or am I wide of the mark?
    Thanks,
    nigel

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

    Re: Simple database help needed. (Access 2000)

    Indeed, I forgot the Then after ElseIf Me.Custody = False, sorry about that. A line with If or ElseIf must always contain Then.

    Private Sub DateFull_AfterUpdate()
    If IsNull(Me.DateFull) Then
    ' If DateFull has been cleared, clear TargetB and TargetC too
    Me.TargetB = Null
    Me.TargetC = Null
    ElseIf Me.Custody = False Then
    ' If not Custody, set TargetB to 14 days after FullDate
    Me.TargetB = Me.FullDate + 14
    Else
    ' Otherwise, set TargetC to 10 days after FullDate
    Me.TargetC = Me.FullDate + 10
    End If
    End Sub

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

    Re: Simple database help needed. (Access 2000)

    Thats great, works a treat, presumably the next step would be to do a code for the TargetMet C and TargetMetB fields, taking the date from DatePackageOut to display 'Yes' or 'No' depending on whether the date is above or below this data. Unfortunately I haven't a clue how to do this as no doubt you guessed!
    After this step I presume that reports will be able to be created to display the data that need to be extracted......or am I jumping the gun?
    Thanks for your continued patience, I've learnt more about Access in the couple of weeks than I have for months.
    Cheers,
    Nigel

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

    Re: Simple database help needed. (Access 2000)

    Where should the Yes/No be displayed? Your statement
    <hr>taking the date from DatePackageOut to display 'Yes' or 'No'<hr>
    doesn't make sense - a date displays a date, not Yes/No.

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

    Re: Simple database help needed. (Access 2000)

    Sorry Hans, probably didn't explain myself correctly, the 'Yes' or 'No' would be displayed in the TargetMetB & TargetMetC, this would be correspond to whether or not the dates in TargetB or TargetC and are greater than the date in DatePackageOut. Currently I have the Data Type set as Date/Time whereas it should be Text presumably, as opposed to Yes/No.
    What I will need to do at a later stage is collate how many packages have been sent within the 10 or 14 day timelines within a certain month and whether they have met their target dates, hence the Yes or No. Plus the caseworker has to give a reason as to why the package was not served within time and therefore by displaying Yes or No, this will indicate to them clearly that they will need to enter this information.
    Thanks,
    Nigel

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

    Re: Simple database help needed. (Access 2000)

    In fact, you don't need TargetMetB and TargetMetC fields at all in the table, since they contain derived information.
    - Create a query based on tblMain.
    - Drag * from the field list to the query design grid (or double click *)
    - Add calculated fields:

    TargetMetB: [TargetB]>[DatePackageOut]

    and

    TargetMetC: [TargetC]>[DatePackageOut]

    - Save the query as (say) qryMain, and set the Record Source property of frmMain to qryMain.
    - Change the Format property of the text boxes on the form bound toTargetMetB and TargetMetC to Yes/No.
    - If you prefer, you can replace the text boxes by check boxes.

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

    Re: Simple database help needed. (Access 2000)

    Hi Hans,
    Sorry I'm lost on this one, i haven't really delved into the world or Reports and queries. Hang my head in shame.
    I understand how to create a query by dragging the fields from tblmain into the Query fields, then checking the boxes to activate the query.
    Would I be right in thinking that I would need to Add the fields TargetB, TargetC & DatePackageOut into the query from tblMain, then in the criteria enter TargetMetB:[TargetB]>[DatePackageOut} thus adding calculated fields? Or not.....
    Presumably then I would enter the FrmMain in Design view, select record source as QueryMain. With regards to the query, will the entire data of the tblmain need to be displayed in QueryMain to reflect accurate data in FrmMain.......
    Thanks,
    Nigel

Page 1 of 4 123 ... 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
  •