Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    May 2003
    Location
    Northern Illinois
    Posts
    10

    Question Difficulty with a date query

    Hope someone can help.

    I am working on the database here at the dairy and all has gone well until now.

    What I need to happen in a query is to provide inventory data for one date (say 5-2-03) from the inventory table and also provide the same type of data (tanks, volumes, etc) for the previous day (5-1-03) and then be able to do calculations off of the resulting data.

    It is to provide us with a gain/loss report that could span a few days, a week or even a month. I envision a line of data that would list the current day, the current inventory level, the previous inventory level and then be able to calculate the difference.

    I have to this point be unable to get a query to retrieve data from one date and then from a preceeding date.

    Again, I hope that someone can help me with this.
    Terry Fairfield
    aka "The fishdoctor"

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you need a left outer self-join

    select today.product
    , today.tanks - coalesce(otherday.tanks,0) as changeintanks
    , today.volumes - coalesce(otherday.volumes,0) as changeinvolumes
    from product today
    left outer
    join product otherday
    on today.product = otherday.product
    where today.inventorydate = now()
    and otherday.inventorydate = now() - 7


    rudy

  3. #3
    Join Date
    May 2003
    Location
    Northern Illinois
    Posts
    10
    Rudy,

    This what I have done with what you replied with. When I try to run the query, it gives me an error that states this:
    join expression not supported.
    I am using MS ACCESS 97 if that makes a difference.
    Am I just not seeing something that is causing this. At this point of the week, my brain is frying on this subject.

    By the way, thanks for the help so far.


    select EVENINGDATE, PRODUCTID, TANKID, FATTEST, EVENINGRAWINVENTORY, BRILBSBF, RAWREC, RECLBSBF, SKIMTOSEPARATION, SEPLBSBF, PRODUCTTOCONDENSED, CONDLBSBF
    FROM qryWEEKLYGAINLOSS
    left outer
    join product otherday
    on EVENINGDATE, PRODUCTID, TANKID, FATTEST, EVENINGRAWINVENTORY, BRILBSBF, RAWREC, RECLBSBF, SKIMTOSEPARATION, SEPLBSBF, PRODUCTTOCONDENSED, CONDLBSBF
    FROM qryWEEKLYGAINLOSS = EVENINGDATE, PRODUCTID, TANKID, FATTEST, EVENINGRAWINVENTORY, BRILBSBF, RAWREC, RECLBSBF, SKIMTOSEPARATION, SEPLBSBF, PRODUCTTOCONDENSED, CONDLBSBF
    FROM qryWEEKLYGAINLOSS
    where today.EVENINGDATE = now()
    and otherday.EVENINGDATE = now() - 7
    Terry Fairfield
    aka "The fishdoctor"

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in your ON condition, you have a list of columns, whereas what you need is a join condition

    all you need in the ON condition is to join the primary keys

    you do have a primary key in this table, right?

  5. #5
    Join Date
    May 2003
    Location
    Northern Illinois
    Posts
    10
    Rudy,

    When I looked at the table, EVENINGDATE should be the primary key, but it was not. That was probably the problem all along. Every other table in the database has a primary key. Wonder how I could have missed this one. So I have inserted EVENINGDATE as the primary key and in the join, I should only use EVENINGDATE and nothing else, correct.
    Terry Fairfield
    aka "The fishdoctor"

  6. #6
    Join Date
    May 2003
    Location
    Northern Illinois
    Posts
    10
    Rudy,

    When trying to change the eveningdate to the primary key, it creates duplicates in that field. Since there are numerous tanks being recorded for a particular day, how do I allow for duplicate dates in this field? Or is there another way, such as creating a table that has dates and evening dates and have this tied to the inventory tables in a one to many relationship.
    Terry Fairfield
    aka "The fishdoctor"

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a primary key is a column, or a combination of columns, that is unique

    this allows you to distinguish "this" row from "that" row

    i did not think about this too much at first, and just used "product" as the join condition, assuming that your inventory table has a product code, and that no two products would share the same code

    but an inventory table has to have a compound primary key, product code and date

    or, if product quantities can exist for the same date in different warehouses, for example, then the primary key is product code, location, date

    so if you have a compound primary key in your inventory table, you need to join on all columns of that key

    note that you do not actually have to declare a primary key in order to write a query that uses the same columns as though they were the primary key...

    ... if you know what i mean


  8. #8
    Join Date
    May 2003
    Location
    Northern Illinois
    Posts
    10
    In one given day, there may be numberous tanks that would have raw milk stored in them with the productid of RAW, so therefore I cannot use that as a primary key. Likewise, every day some of the same tanks are used, some are not used every day, so that is not a constant between the days. The idea of a compound key might work, but I have never created one and do not know how to.

    What do you mean by " that you do not actually have to declare a primary key in order to write a query that uses the same columns as though they were the primary key...".

    Does this mean that I do not have to have date and some other one as a primary key or compound key? I tried to make this work and I get an errror.

    select EVENINGDATE, TANKID
    left outer
    join product otherday
    on EVENINGDATE, TANKID
    FROM qryWEEKLYGAINLOSS
    where today.EVENINGDATE = now()
    and otherday.EVENINGDATE = now() - 7

    The error is : Syntax error (missing operator) in query expression 'tankid left outer join product other day on eveningdate.
    Terry Fairfield
    aka "The fishdoctor"

  9. #9
    Join Date
    May 2003
    Location
    Northern Illinois
    Posts
    10

    Thumbs up

    Rudy,

    I am getting close (I think), because I have created this, but it does not return any data.

    SELECT qryWEEKLYGAINLOSS.EVENINGDATE, qryWEEKLYGAINLOSS_1.EVENINGDATE, qryWEEKLYGAINLOSS.EVENINGDATE AS TODAY, qryWEEKLYGAINLOSS.EVENINGDATE AS OTHERDAY
    FROM qryWEEKLYGAINLOSS LEFT JOIN qryWEEKLYGAINLOSS AS qryWEEKLYGAINLOSS_1 ON qryWEEKLYGAINLOSS.EVENINGDATE = qryWEEKLYGAINLOSS_1.EVENINGDATE
    WHERE (((qryWEEKLYGAINLOSS.EVENINGDATE)=Now()) AND ((qryWEEKLYGAINLOSS.EVENINGDATE)=Now()-7));

    There is some final piece that I must be missing.
    Terry Fairfield
    aka "The fishdoctor"

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my suggestion was to use "today" and "otherday" as table aliases, it makes writing the query a lot easier

    the reason your query is returning nothing is because your WHERE clause is looking at only the "today" date and expecting it to be two different values at the same time --

    WHERE
    ((qryWEEKLYGAINLOSS.EVENINGDATE)=Now())
    AND
    ((qryWEEKLYGAINLOSS.EVENINGDATE)=Now()-7)
    );

    the query is easier to understand using table aliases:

    SELECT today.EVENINGDATE as datetoday
    , otherday.EVENINGDATE as dateotherday
    FROM qryWEEKLYGAINLOSS AS today
    LEFT
    JOIN qryWEEKLYGAINLOSS AS otherday
    ON today.EVENINGDATE
    = otherday.EVENINGDATE
    WHERE today.EVENINGDATE=Now())
    AND otherday.EVENINGDATE=Now()-7

    in a self-join, you also usually need column aliases, in this case i am using "datetoday" and "dateotherday" for those

    nevertheless, you may still not get satisfactory results, because there is no product code involved in the above, so it will only work (in this case, work means "get the right answer" rather than just "have no syntax errors") if there's only one product in the table

  11. #11
    Join Date
    May 2003
    Location
    Northern Illinois
    Posts
    10
    Rudy,

    When I incorporated what you sent, your right that it does not create an error, but it returns nothing. When I do incorporate the productid into the SQL statement, it still returns nothing. Is this because there are more that one product listed within productid?
    Terry Fairfield
    aka "The fishdoctor"

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no idea, but that's because i do not understand your table

    pick a product, pull some of its rows out of the table, show me what's in all the columns that figure into this, and tell me what you expect in the final result

    just a few rows should do it

  13. #13
    Join Date
    May 2003
    Location
    Northern Illinois
    Posts
    10
    Rudy,

    Here is a small part of the database. It includeds the table, the query that the current query is based off of (qryLOST/GAIN EQUATION) and even an example of the report I am trying to create if I can get it to reference previous days information.

    A select part of the base table is included in qry1.

    Thanks for all of your help.

    I will be gone for a bit getting lunch. I will check for your reply when I get back.

    Again, Thanks.
    Attached Files Attached Files
    Terry Fairfield
    aka "The fishdoctor"

  14. #14
    Join Date
    May 2003
    Location
    Northern Illinois
    Posts
    10

    Unhappy

    Rudy,

    I am back. I have tried doing the same approach of using a left outer join with the original table and have the same problem.
    I have also tried creating a query where it will return only the raw information, another one that returns just the cream information and then tried to create a left outer join off of these. Every time I get blank results.
    Terry Fairfield
    aka "The fishdoctor"

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your data scares me

    you will need to use GROUP BY on date and productid, since you have all sorts of transactions in there with the same date/productid values

    i forgot the original question, but the answer will be more complex than i have time for

    i think you may need consulting help -- consider bringing an access expert in to help you

    sorry

    rudy

Posting Permissions

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