#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Location
    Canada
    Posts
    5
    Rep Power
    0

    Populating empty fields to get a daily snapshot of data


    Alright, seems like Iím running out of wisdom once again. Iím working on a database that monitors the fuel inventory of multiple tanks. Main entries are the dips, entered only when liquid is added/taken out (not daily), which are then converted to Volume with a formula (calcvol below). My goal with this particular query is to return a daily snapshot of the total Inventory as of that date. With the help of June7 and Allen Browne, I was able to make a statement that returns the latest dip value for every tank without the need of cascading tons of queries and making silly relationships. It looks like this.

    SELECT tblFuelDip2.fdDateTime, tblFuelDip2.fdTankNo, tblFuelDip2.fdReading, calcvol([fdReading],[stTankType]) AS Volume
    FROM tblStorageTanks INNER JOIN tblFuelDip2 ON tblStorageTanks.stID = tblFuelDip2.fdTankNo
    WHERE (((tblFuelDip2.[fdDateTime])=
    (SELECT max([fdDateTime]) FROM tblFuelDip2 as x WHERE tblFuelDip2.fdTankNo = x.fdTankNo)));

    This works great to know my total inventory at the current moment. Now the part where my brain stops to function is when trying to get this value AS OF each calendar date. So in other words, to run this for every fdDate while excluding any records where the fdDate is greater than the one weíre trying to get a result for.
    If I simply do a where fdDateTime <= [insert random date], in the query above, itíll exclude whatever tanks have been filled up after that date, but I wonít get a total snapshot of the volume as of that date, simply the ones that havenít changed.

    Example Current Result displayed as a crosstab (appologies for the dots...)
    ................2012-11-07...2012-11-08...2012-11-09...2012-11-010...2012-11-11
    Tank 1................................................. .................................................. .....20000
    Tank 2................................15000
    Total................0.................15000...... ..........0........................0.............. ..20000

    Example Desired Result displayed as a crosstab
    ................2012-11-07...2012-11-08...2012-11-09...2012-11-010...2012-11-11
    Tank 1........50000.............50000.............40000 ..............30000...........20000
    Tank 2........30000.............15000.............15000 ..............15000...........15000
    Total...........80000.............65000........... ..55000 ..............45000...........35000

    My backup plan for this is simply to have a single entry per tank everyday and sum up the volume at that date. Although that is very ďexcelĒ and will create tons of duplicate data, so Iím sure thereís a way to avoid that. I could also just convert all dip values to Volumes and deal with the rest in excel in a pivot table, which is what friends are recommending. Or perhaps i'm thinking about this a little bit too linear?
  2. #2
  3. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    59
    You can get the value of any day by modifing the query you have already. Two options. If you know the specific date you a looking for has a reading, but it in a text box on your form. Change
    Code:
    (SELECT max([fdDateTime]) FROM tblFuelDip2 as x WHERE tblFuelDip2.fdTankNo = x.fdTankNo)));
    to something along the lines of
    (SELECT [fdDateTime] = Forms!YourForm!DateTextField FROM tblFuelDip2 as x WHERE tblFuelDip2.fdTankNo = x.fdTankNo))); (my sql syntax needs work I know, but hopefully you get the idea)
    If you don't know if the date in question has a fuel reading, use fdDateTime <= Forms!YourForm!DateTextField. Set the dates in descending order and set the query to return only one result. (Give the max date value less than or equal to your entered date.)
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Location
    Canada
    Posts
    5
    Rep Power
    0
    Thanks again for your help meratigoerr. As I'm sure you figured it out, I'm going to use this query in order to use the other formula you've helped me out with. Decided to use subqueries to simplify the daily entry of dips. But it's only complicating the daily volume output O_o

    I see the logic behind what you're proposing. The issue I'm having when trying to implement this is that my only date field in the dip-related tables is this [fdDateTime]. I cannot compare it to itself, access is going to go in an endless loop (unless i can??). I've tried applying this by making a table called 'tblDates' where the only entries are the dates of the year, and then using these for the comparison. Whichever way i do the join, it doesn't seem to like what i'm doing, as it simply returns empty fields under the tblDates.DatesField column.
  6. #4
  7. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    59
    Originally Posted by MaxAndMin
    I see the logic behind what you're proposing. The issue I'm having when trying to implement this is that my only date field in the dip-related tables is this [fdDateTime]. I cannot compare it to itself, access is going to go in an endless loop (unless i can??). I've tried applying this by making a table called 'tblDates' where the only entries are the dates of the year, and then using these for the comparison. Whichever way i do the join, it doesn't seem to like what i'm doing, as it simply returns empty fields under the tblDates.DatesField column.
    I'm lost, where are you trying to compare the date to itself? In the example I gave you, the date you're comparing it to is a user entered value that they're searching for. The confusion may be coming from my lack of sql knowledge. Basically you're using criteria to limit the search results to either the day the user wants, or the first entry before the date the user enters. Either way you're comparing [fdDateTime] to a user entered value, not itself
  8. #5
  9. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Location
    Canada
    Posts
    5
    Rep Power
    0
    I'm gonna dumb down my DB a bit and apply your concepts. The way it's set up right now is a bit over my head & knowledge. Thanks for all the help. I'll get back you once i get it.

Similar Threads

  1. Msg box to appear if certain fields on form are empty
    By wrightie in forum Microsoft Access Help
    Replies: 4
    Last Post: July 20th, 2009, 09:46 AM
  2. Empty fields
    By bigbadbess24 in forum Microsoft Access Help
    Replies: 2
    Last Post: July 8th, 2006, 11:49 PM
  3. Replies: 2
    Last Post: November 30th, 2005, 07:06 AM
  4. Populating fields in one form/table with data from another
    By peeld in forum Microsoft Access Help
    Replies: 8
    Last Post: April 23rd, 2004, 02:01 PM
  5. Populating Fields -
    By Vision in forum ASP Development
    Replies: 9
    Last Post: March 3rd, 2004, 02:14 AM

IMN logo majestic logo threadwatch logo seochat tools logo