Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2003
    Location
    Oxford, England
    Posts
    38

    Unanswered: Adding a value to the result of a query

    Can anyone help?

    I have a date field containing dates from the year 2000 to 2004 from which i have queried a list of disinct years. What i need is a list of all the years in my date field and the current year if there is not a record conatining from anything in the current year

    i.e.

    at the moment i have records containg dates from every year from 2000 to 2004 which is fine, but as soon as we hit 2005 until there is a new record in my date field that contains 2005, it wont be listed in the query.

    Is there of adding the current year to my query results?

  2. #2
    Join Date
    Dec 2003
    Location
    Oxford, England
    Posts
    38

    Re: Adding a value to the result of a query

    Failing that is there any way i can have a query that just lists years whether they exist or not in my recordset (or even just spew out numbers) which i can query

    WHERE Years Between "2000" and datepart(Year, getdate())

    or similer?

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    You can use a Create Table Query to have all your records then make an Insert Qury which insterts a Record of the year 2005 (just dummy record) to the created table.

    Hope it helps

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's one way:

    select distinct year(datecol) from yourtable
    union
    select year(date()) from onerowtable

    you need a "onerowtable" in order to make this work

    oracle has one, called DUAL

    mysql and sql server don't need one, you can just go like this --

    select distinct year(datecol) from yourtable
    union
    select year(date())

    but in microsoft access you'll just have to create a dummy table with one row

    doesn't matter what columns it has in it, just as long as there's only one row

    then if the list of years from the first table does not include the current year, then the second query in the union will supply it

    and if the list of years from the first table does include the current year, then the second query of the union will add a duplicate, but the UNION will remove it

    neat, eh?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Re: Adding a value to the result of a query

    Originally posted by Lukelrc
    Failing that is there any way i can have a query that just lists years whether they exist or not in my recordset (or even just spew out numbers) which i can query

    WHERE Years Between "2000" and datepart(Year, getdate())

    or similer?
    SELECT Year([anyBirthDay) AS Year
    FROM Table;

  6. #6
    Join Date
    Dec 2003
    Location
    Oxford, England
    Posts
    38
    Cool, i'll give it a whirl.

  7. #7
    Join Date
    Dec 2003
    Location
    Oxford, England
    Posts
    38

    Re: Adding a value to the result of a query

    Originally posted by hammbakka
    SELECT Year([anyBirthDay) AS Year
    FROM Table;
    Sorry Hammbakka, would this generate a list of years which i can filter? 'anyBirthDay' is a field name right?

  8. #8
    Join Date
    Dec 2003
    Location
    Oxford, England
    Posts
    38
    Ah, it works:

    SELECT DISTINCT DatePart(yyyy,[PressreleaseDate]) as Yearlist
    FROM dbo.ViewPressReleases UNION SELECT datepart(yyyy, getdate())
    ORDER BY DatePart(yyyy,[PressreleaseDate])

    Thanks all.

  9. #9
    Join Date
    Dec 2003
    Location
    Oxford, England
    Posts
    38
    Me again

    Now i've run into another small snag. I need to do a similar thing with the month but i want to ad a WHERE clause anlong the lines of

    SELECT DISTINCT DatePart(m,[PressreleaseDate]) as Monthlist
    FROM dbo.ViewPressReleases UNION SELECT datepart(m, getdate())
    ORDER BY DatePart(m,[PressreleaseDate])
    WHERE Monthlist LIKE stgetstyear

    I know i cant use the Monthlist colunm name in the WHERE clause as i need to do somthing like

    WHERE DatePart(m,[PressreleaseDate]) UNION datepart(m, getdate()) LIKE stgetstyear

    but i am at a loss as how to corrctly write it??

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    neat, i'm glad you got it to work

    although i should point out that you went for a sql server solution in the microsoft access forum, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    monthlist? a list of months in a column?

    LIKE a stored proc?


    huh?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Dec 2003
    Location
    Oxford, England
    Posts
    38
    Oh, forget me!!! What am i on about there are always Jan-dec in every year. Its only lunchtime and its been long day already!

  13. #13
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Re: Adding a value to the result of a query

    Originally posted by Lukelrc
    Sorry Hammbakka, would this generate a list of years which i can filter? 'anyBirthDay' is a field name right?
    Yes this would generate a list of years, and anyBirthday is a Field

    SELECT Year([anyBirthDay]) AS Year
    FROM Table
    WHERE Year=1990

    This will show only the records with the year 1990

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT Year([anyBirthDay]) AS Year
    FROM Table
    WHERE Year=1990

    This will show only the records with the year 1990
    actually, that's not true

    it will show only the years where the year is 1990

    for example, it will show

    1990
    1990
    1990
    1990
    1990
    1990
    1990
    1990
    1990
    1990
    1990
    1990


    oops, forgot a couple --

    1990
    1990


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Never mind, just use the TOP 1 Perdicate

Posting Permissions

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