Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2016
    Posts
    3

    Unanswered: Help - Searching Multiple Fields in Access

    So here is the issue in a nut shell.

    A 2 Star General would like to know how often people are deploying compared to how often they are at home. They call this a dwell ratio. What I have constructed is a database in which each member, every time they deploy, they imput the date they leave and the date they return. The data base then calculates how many days they were gone and what their ratio is. That is the simple part. The issue I am having is the query part. I would like to be able to input 1 Date for a search and pull up everyone who deployed that day. The problem is...your new guy may be on his first deployment and leave that day or your older guy may be on his 5th deployment. So how do I get it to search multiple fields looking for that one date selected?

    I basically have a field Labeled Dep1, Dep2, Dep3....all the way up to Dep10 which allows up to 10 Departure Dates to be entered. I need to search each of those fields for one date. Once I figure that out...I should be good to go. Thanks for any help you can offer.

    Major Lowther

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59

    Cool

    Its a bad design. Virtually anytime you see a series of columns prefixed or suffixed with the same name its nearly always suspect.
    Redesign the the schema so the deployments becomes a table in its own right.
    That table has soldiers ID in it and the date of deployments, and anything else unique and relevant to that deployment. Probably the primary key should be soldierID AND deployment date,but it could be something else. By unique and relevant that means anything specific to the recording if the deployment (eg who ordered it, datetime of order, but not repeating groups of stuff such as skills used on deployment, lists of engagements etc... thats another sub table. Repeating data deserves its own sub tables.....

    When storing datetime values in Access use a date column, if using a default value for this use date () NOT now (). Im guessing for your puposes you dont need the time component on a deployment just the dates. If you store the time you make it harder for yourself as the filter becomes more complex.

    To calculate the dwell ratio its the current date less the enlistment date (or whatever your start point is, could be in soldier table, could be a calendar date) divided into the sum of the deployment dates (again the difference between return and departure dates). Strictly speaking you should never do direct maths on temporal data, you should use the datetime functions intrinsic in Access. But for this requirement id be tempted to ignore that database commandment.

    This way round a soldier could have 0, 1 or many deployments. There is no arbitary limit of 10. There is no need to set up some complex query to try and identify which of your n dates is in or out if range.

    You still need to devise a stratgey to validate data stop duplicate deployment rows. You can partially do this by selecting the soldier ID and deployment date and return date as the PK ( OR enforce a unique index on those columns. Thus may be a better bet if the return date is entered after the main data in the row is captured) That stops duplication. But you also need to validate so that you dont have overlapping dates where due to error you have two rows covering overlapping periods for the same soldier.

    Read up on normalisation
    Last edited by healdem; 02-20-16 at 04:40.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2016
    Posts
    3
    All I really need is the Date Departed, Return Date, Number of Days Deployed, and then the ratio from the return date of the last deployment to the departure of this said deployment. And then wash rinse and repeat however many times this person deploys. I tried separating the Deployment Dates into a separate table or into multiple tables for each deployment, but it still boils down to me trying to enter one piece of data (Departure Date) and sorting through every departure date the person has to find out if he/she left on that particular deployment. Something still isnt clicking in my mind...I dreamed off Access last night and I know its bad.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    All you really need is....
    ....is to normalise your design. The reaon why you are struggling designing a query is precisely because its not a normalised design.

    Fix the design first.

    As an aside storing derived data is another database design no no. Store departure date and either return date OR duration but not both
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2016
    Posts
    3
    I guess it kinda means that an Access Database does not work for the issue at hand. I need to have both a departure date and a return date and an ability to sort out everyone who left on a particular date through multiple deployments listed per individual.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Far from it, any SQL, or even non SQL database will do what you want, assuming its properly designed. The reality is that virtually without exception you can develop a solution to virtually any problem. But its a tool, and you have to approach the problem the right way, the way it expects so tbe toolcan be used. There is feck all point blaming the tools because of poor design.

    Your current design isnt normalised, its a nightmare to write a query against as you have already discovered.

    Read up on normalisation
    Fix the design
    Write the query

    Trying to write a query against your current design would be tricky, error prone and a pig to modify or maintain over time. Even worse for your successor.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    assumiung that you were interested in the dwell ratio for the calendar year 2015...
    a naive approach to this problem could be:-
    Code:
    SELECT Deployments.Soldier_ID, 
    Sum(returndate-departuredate) AS DaysDeployed
    FROM Deployments
    where departuredate <= #12/31/15#
    and returndate > #01/01/15#
    GROUP BY Deployments.Soldier_ID;
    note the way the date banding is formed
    the return date must be on or after the START of the year
    the departure date must be on or before the the end of the year

    ..however this query has a few problems
    1) the cut off points for the dates are hard coded... you can get round that by using parameters. probably best implemented as an ADO query object
    2) although the query calculates the time on deployment, it doesn't handle the problem oif where your data band may straddle a deployment. say you are looking at calculating the annual dwell rota, but a soldier may be deployed before the start of the year and or may return after the end of the year
    ..so thats a whole new level of complexity, you need correct the days deployed to cater for that. (remember why it was suggested earlier that you don't store derived data? )

    so to cater for on tour dates that are outside the date band
    Code:
    SELECT Deployments.Soldier_ID, 
    sum
    ( iif(departuredate< #01/01/15# AND returndate >#12/31/15#, #12/31/15# - #01/01/15#, 
        iif(departuredate< #01/01/15#, returndate-#01/01/15#, 
          iif(returndate > #12/31/15#,#12/31/15#-returndate,returndate-departuredate
          )
        )
      )
    ) as DaysDeployed
    FROM Deployments
    where departuredate <= #12/31/15#
    and returndate > #01/01/15#
    GROUP BY Deployments.Soldier_ID;
    The iif is an access specific way of handling complexity, it takes the form iif (boolean exoression, if true, if false. In this case we use a compound IIF, the first iif says if the deaprture date is before 01 jan 2015 and the return date id after 31 dec 2015 then for oyr purpises use the number of days in the year. The next iif says if the deaprture date is before 01 jan use o1 jan in place of the departuredate. The last term us similar except if the return date is after 31 dec use 31st dec otherwise use returndate -departuredate to calculate the duration of the deployment.
    Compound IIFS are a powerfull tool but can be a pain to setup, test and maintain.

    now imagine trying to expand that query 10 fold with departuredate1...n returndate1...n
    what happens if your general then asks why can't you handle say 11 deployments
    ...or if you find you need to support say a one or two day a week deployment for 52 weeks....


    to calculate the dwell ratio
    Code:
    SELECT Deployments.Soldier_ID, 
    cdbl
    ( sum (
      iif(departuredate< #01/01/15# AND returndate >#12/31/15#, #12/31/15# - #01/01/15#,
        iif (departuredate< #01/01/15#, returndate-#01/01/15#,
          iif(returndate > #12/31/15#,#12/31/15#-returndate,returndate-departuredate
             )
          )
        )
      )  / (#12/31/15# - #01/01/15# )
    ) as dwellratio
    FROM Deployments
    where departuredate <= #12/31/15#
    and returndate > #01/01/15#
    GROUP BY Deployments.Soldier_ID;
    ..in this case note the express conversion of the datatype of the dwell ration as a double value number

    there are other ways of approaching this
    one is to run a first query which calculates the days deployed and corrects for deployment or return date being outside the time limits, then use the output of that query as the feedstock into you summarising query.

    if you need to sort on the dwellratio then you need to do some more spadework, but that can be left for later (or merely implemented in a form or report sort order (which Im pretty certain would be the smart solution

    read up on normalisation. understand why its a 'good idea' when designing relational databases. apply those design ideas to your db.

    bear in mind not everything 'has' to be done in a query, some things you can (and probably should) do in the presentation layer.
    Last edited by healdem; 02-21-16 at 14:52.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    to then tidy up the query, to add back the soldiers names for presentation purpsoses
    Code:
    SELECT
      Deployments.Soldier_ID,
      Soldiers.Forename,
      Soldiers.Surname, 
      CDbl 
      ( Sum
        ( IIf(departuredate<#1/1/2015# And returndate>#12/31/2015#,#12/31/2015#-#1/1/2015#,
            IIf(departuredate<#1/1/2015#,returndate-#1/1/2015#,
              IIf(returndate>#12/31/2015#,#12/31/2015#-returndate,returndate-departuredate
              )
            )
          )
        )
      / (#12/31/2015#-#1/1/2015#)
      ) AS dwellratio
    FROM Soldiers INNER JOIN Deployments ON Soldiers.ID = Deployments.Soldier_ID
    WHERE (((Deployments.[departuredate])<=#12/31/2015#) AND ((Deployments.[returndate])>#1/1/2015#))
    GROUP BY Deployments.Soldier_ID, Soldiers.Forename, Soldiers.Surname;
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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